DECLARE @DIRLIST TABLE([OUTPUT] VARCHAR(1000)) --삭제할백업디렉토리리스트테이블변수
INSERT INTO @DIRLIST
EXEC XP_CMDSHELL 'DIR T:\DB_BACKUP\FULL_BACKUP\ /AD /B' --백업디렉토리경로하위에날짜별백업폴더리스트조회하여테이블변수에INSERT
DECLARE @CMD VARCHAR(4000)
SET @CMD =''
DECLARE @DIRNAME VARCHAR(100)
WHILE EXISTS
( SELECT * FROM @DIRLIST
WHERE LEN(OUTPUT)=8 AND OUTPUT LIKE '20%' AND OUTPUT<=CONVERT(NVARCHAR,DATEADD(DD, -7, GETDATE()),112))
BEGIN
-- 조건에맞는폴더명을조회
SELECT
TOP 1 @DIRNAME =[OUTPUT]
from @DIRLIST
WHERE
LEN(OUTPUT)=8 AND OUTPUT LIKE '20%' AND OUTPUT<=CONVERT(NVARCHAR,DATEADD(DD, -7, GETDATE()),112)
SET @CMD='RMDIR T:\DB_BACKUP\FULL_BACKUP\'+@DIRNAME+' /S /Q'
EXEC SYS.XP_CMDSHELL @CMD; --CMDSHELL 을이용하여대상폴더삭제
DELETE FROM @DIRLIST WHERE [OUTPUT] = @DIRNAME -- 삭제된폴더를테이블변수에서삭제
END
GO
DECLARE @CMD2 VARCHAR(4000)
SET @CMD2 ='MKDIR T:\DB_BACKUP\FULL_BACKUP\'+CONVERT(NVARCHAR,GETDATE(),112)+''; -- 오늘날짜백업디렉토리생성
EXEC XP_CMDSHELL @CMD2, NO_OUTPUT;
DECLARE @QUERY NVARCHAR(MAX)
SET @QUERY =N''
--전체데이터베이스리스트를조회하여백업쿼리문생성
SELECT
@QUERY = @QUERY +N'BACKUP DATABASE '+NAME+' TO DISK=''T:\DB_BACKUP\FULL_BACKUP\'+CONVERT(NVARCHAR,GETDATE(),112)+'\'+NAME+'_DATA.BAK'' WITH INIT
BACKUP LOG '+NAME+' TO DISK = ''T:\DB_BACKUP\FULL_BACKUP\'+CONVERT(NVARCHAR,GETDATE(),112)+'\'+NAME+'_LOG.BAK''
'
FROM SYSDATABASES
WHERE NAME NOT IN ('MASTER', 'MODEL', 'MSDB','TEMPDB', 'TEST_DB')
ORDER BY NAME ASC
--PRINT @QUERY ;
EXEC SYS.SP_EXECUTESQL @QUERY ; --백업실행