SQL BACKUP SCRIPT (FULL LOG DIFFERENTIAL, WITH FILE AUTO DELETE)

SQL Server 가 100 대 넘어가면 유지관리계획으로 관리하기 힘들다.
아래와 같은 스크립트가 도움이 될 것이다. 해당 스크립트는 변수만 셋팅하면 나머지 과정은 자동으로 처리한다.
시간이 지난 파일은 자동으로 지워준다.

오늘 올라온 질문중 백업 파일이 계속 커진다는 질문이 있었다.
이런 사람은 더 열심히 공부해야 한다.
개념을 이해하기 위해 http://www.sqlbackuprestore.com/introduction.htm 사이트를 참고해 열심히 공부한다.

 

 

USE MASTER
GO

IF OBJECT_ID('AP_DB_BACKUP') IS NULL
 EXEC ('CREATE PROC DBO.AP_DB_BACKUP AS SELECT 1 ')
GO

ALTER PROC [DBO].AP_DB_BACKUP
  @DBNAME VARCHAR(20) = 'MASTER' -- BACKUP TARGET DATABASE
, @PATH VARCHAR(100) = 'C:\BACKUP\' -- OS BACKUP PATH
, @BACKUP_TYPE VARCHAR(1) = 'F' -- F FULLBACKUP L LOGBACKUP D DIFFERENTIAL
, @BEFORE_TARGET_TIME_HH INT = 2 -- DELETE FILE BEFORE TIME HH
, @ENABLP_XP_CMDSHELL_FORCE INT = 1
, @CREATE_TARGET_FOLDER_FORCE INT = 1
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- SCRIPT BY MINSOUK KIM
-- MSSQL MVP, 2006~2009
-- VERSION 1.20111018
-- CAFE, WWW.SQLTAG.ORG, CAFE.NAVER.COM/SQLMVP
-- BLOG, SQLSQL.TISTORY.COM
-- TEL, 01099670955
-- MAIL, MINSOUK@HOTMAIL.COM

IF @BACKUP_TYPE = 'L'
WAITFOR DELAY '00:00:10.000'
IF @BACKUP_TYPE = 'D'
WAITFOR DELAY '00:00:05.000'

DECLARE @CAL_BEFORE_TARGET_TIME DATETIME
   , @DEL_TARGET_DEV_NAME VARCHAR(500)
   , @CUR_TARGET_DEV_NAME VARCHAR(500)
   , @DATE VARCHAR(500)
   , @TIME VARCHAR(500)
   , @PHYSICAL_NAME VARCHAR(500)
   , @TODAY VARCHAR(8)
   , @INSTANCE_NAME VARCHAR(100)
   , @XP_CMDSHELL_STATUS INT
   , @XP_CMDSHELL_CHANGE_STATUS INT
   , @CMD_STRING VARCHAR(8000)
  
SELECT @INSTANCE_NAME = REPLACE(@@SERVERNAME , '\','_')
SELECT @XP_CMDSHELL_STATUS = CAST(VALUE_IN_USE AS INT) FROM SYS.CONFIGURATIONS WHERE NAME ='XP_CMDSHELL'

IF @XP_CMDSHELL_STATUS = 0 AND @ENABLP_XP_CMDSHELL_FORCE = 0 BEGIN
 SELECT 'CHANGE @ENABLP_XP_CMDSHELL_FORCE PARAMETER TO 1'
 RETURN 0
END

IF @ENABLP_XP_CMDSHELL_FORCE = 1 AND @XP_CMDSHELL_STATUS = 0 BEGIN
 EXEC SP_CONFIGURE 'XP_CMDSHELL', 1
 RECONFIGURE WITH OVERRIDE
 SET @XP_CMDSHELL_CHANGE_STATUS = 1
END

IF @CREATE_TARGET_FOLDER_FORCE = 1 BEGIN
 SET @CMD_STRING ='MKDIR '+UPPER(@PATH+@INSTANCE_NAME)
 EXEC MASTER.DBO.XP_CMDSHELL @CMD_STRING, NO_OUTPUT
END

SELECT @DATE = CONVERT(VARCHAR(100), GETDATE(), 112)
  , @TIME = REPLACE(CONVERT(VARCHAR(100), GETDATE(), 108),':','')

SELECT @CAL_BEFORE_TARGET_TIME = DATEADD(HH,-1*ABS(@BEFORE_TARGET_TIME_HH),GETDATE())

DECLARE @LOG_BACKUP_DELETE_TARGET_ISDATE_TRUE TABLE
(IDX INT IDENTITY(1,1)
,NAME VARCHAR(500)
)

INSERT INTO @LOG_BACKUP_DELETE_TARGET_ISDATE_TRUE (NAME)
SELECT NAME
  FROM (SELECT TOP 100000000000 NAME -- (TOP N NO_MERGE, NO_PUSH_PRED HINT, DO NOT DELETE TOP STATEMENT!)
    FROM MASTER.DBO.SYSDEVICES
   WHERE NAME LIKE 'SQL'+@BACKUP_TYPE+'B_' +REPLACE(@@SERVERNAME,'\','_') +'_'+@DBNAME+'%'
     AND ISDATE(LEFT(RIGHT(NAME, 15),8)) = 1 ) A 
 WHERE CAST(REPLACE(STUFF(STUFF(RIGHT(NAME, 15),12,0,':'),15,0,':'),'_',' ') AS DATETIME) < @CAL_BEFORE_TARGET_TIME
 ORDER BY 1

DECLARE @MAX_IDX INT
SELECT TOP 1 @MAX_IDX = IDX
  FROM @LOG_BACKUP_DELETE_TARGET_ISDATE_TRUE
 ORDER BY IDX DESC

WHILE (@MAX_IDX > 0) BEGIN
 SELECT @DEL_TARGET_DEV_NAME = NAME
   FROM @LOG_BACKUP_DELETE_TARGET_ISDATE_TRUE
  WHERE IDX = @MAX_IDX
 IF @@ROWCOUNT = 0 BREAK;
 BEGIN TRY
  EXEC SP_DROPDEVICE @DEL_TARGET_DEV_NAME,'DELFILE' 
 END TRY BEGIN CATCH
  SELECT 'ERROR !'
 END CATCH
 SET @MAX_IDX = @MAX_IDX - 1
END

SELECT @CUR_TARGET_DEV_NAME = UPPER(
  'SQL'+@BACKUP_TYPE+'B_'
  + REPLACE(@@SERVERNAME,'\','_') + '_'
  + @DBNAME + '_'
  + @DATE+'_'
  + @TIME
  )
 
  , @PHYSICAL_NAME = UPPER(
    RTRIM(@PATH+@INSTANCE_NAME)
  + '\'
  + REPLACE(@@SERVERNAME,'\','_') + '_'
  + 'SQL'+@BACKUP_TYPE + 'B_'
  + LEFT(@DBNAME+ '_' + REPLICATE (@BACKUP_TYPE, 20),20) + '_'
  + @DATE + '_'
  + @TIME
  + '.'
  + @BACKUP_TYPE
  + 'BAK'
  )
 
SELECT @CUR_TARGET_DEV_NAME DEV_NAME
  , @PHYSICAL_NAME PATH_NAME

EXEC SP_ADDUMPDEVICE 'DISK' , @CUR_TARGET_DEV_NAME , @PHYSICAL_NAME

IF @BACKUP_TYPE = 'F'
 BACKUP DATABASE @DBNAME TO @CUR_TARGET_DEV_NAME WITH INIT
  , NAME = @CUR_TARGET_DEV_NAME
  , NOSKIP
  , NOFORMAT
  , PASSWORD = 'P@SSW0RD'
IF @BACKUP_TYPE = 'L'
 BACKUP LOG @DBNAME TO @CUR_TARGET_DEV_NAME
IF @BACKUP_TYPE = 'D'
 BACKUP DATABASE @DBNAME TO @CUR_TARGET_DEV_NAME WITH INIT
  , NAME = @CUR_TARGET_DEV_NAME
  , NOSKIP
  , NOFORMAT
  , DIFFERENTIAL
 
IF @XP_CMDSHELL_CHANGE_STATUS = 1 BEGIN
 EXEC SP_CONFIGURE 'XP_CMDSHELL', 0
 RECONFIGURE WITH OVERRIDE
END
 

go
exec ap_db_backup 'DB1','c:\backup2\','F',1









declare @BMK varchar(max)

set @BMK = 'BACKUP SERVICE MASTER KEY TO FILE = ''E:\BACKUP_SQLTAG\' + replace(@@servername,'\','_') + '.service_master.key'' ENCRYPTION BY PASSWORD = ''P@SSW0RD'''

exec (@BMK)