SQL BACKUP SCRIPT

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)

출처: https://sqlsql.tistory.com/43 [보미네]

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 'test','d:\sql\backup\','L',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)
*/