backup 권한 변경
impersonate 가능 사용자 만들기
--CREATE LOGIN MS_BACKUP_OP WITH PASSWORD = 'P@ssw0rd' -- 로그인생성
--GO
--USE MASTER
--GO
--CREATE USER MS_BACKUP_OP FOR LOGIN MS_BACKUP_OP -- User 생성
--GO
--GRANT EXECUTE TO MS_BACKUP_OP -- 프로시저 실행권한
--GO
--ALTER SERVER ROLE [DISKADMIN] ADD MEMBER [MS_BACKUP_OP] -- 백업할 디바이스 생성을 위해 diskadmin 필요
--GO
--USE [DB_PLAN_GUIDE_TEST]
--GO
--CREATE USER MS_BACKUP_OP FOR LOGIN MS_BACKUP_OP
--GO
--ALTER ROLE [DB_BACKUPOPERATOR] ADD MEMBER [MS_BACKUP_OP] -- 해당 데이터베이스에 db_backupoperator role 필요
--GO
백업 권한을 위한 계정은 로그인이 불가능하게 만든다.
SYSADMIN 계정을 하나 만들어 두고 이렇게 설정하고 쓰면 될 듯
USE [master]
GO
DENY CONNECT SQL TO [ms_backup_op]
GO
ALTER LOGIN [ms_backup_op] DISABLE
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(100) = '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
AS
-- 계정 생성후 로그인 불가 처리한다.
EXECUTE AS login = 'sa' -- *DISKADMIN*DB_BACKUPOPERATOR*
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- SCRIPT BY MINSOUK KIM
-- VERSION 1.20130104
-- BLOG, SQLSQL.TISTORY.COM
-- MAIL, MINSOUK@HOTMAIL.COM
-- xp_cmdshell 제거
-- 특정 유저로 백업
IF @BACKUP_TYPE = 'L'
WAITFOR DELAY '00:00:03.000'
IF @BACKUP_TYPE = 'D'
WAITFOR DELAY '00:00:10.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(500)
SELECT @INSTANCE_NAME = REPLACE(@@SERVERNAME , '\','_')
, @DATE = CONVERT(VARCHAR(100), GETDATE(), 112)
, @TIME = REPLACE(CONVERT(VARCHAR(100), GETDATE(), 108),':','')
select @instance_name , @date, @time
SELECT @CAL_BEFORE_TARGET_TIME = DATEADD(HH,-1*ABS(@BEFORE_TARGET_TIME_HH),GETDATE())
DECLARE @LOG_BACKUP_DELETE_TARGET TABLE
(IDX INT IDENTITY(1,1)
,NAME VARCHAR(500)
)
INSERT INTO @LOG_BACKUP_DELETE_TARGET (NAME)
SELECT NAME
FROM MASTER.DBO.SYSDEVICES
WHERE ISDATE(LEFT(RIGHT(NAME, 15),8)) = 1
AND CAST(REPLACE(STUFF(STUFF(RIGHT(NAME, 15),12,0,':'),15,0,':'),'_',' ') AS DATETIME) < @CAL_BEFORE_TARGET_TIME
AND NAME LIKE 'SQL'+@BACKUP_TYPE+'B_' +REPLACE(@@SERVERNAME,'\','_') +'_'+@DBNAME+'%'
ORDER BY 1
select * from @LOG_BACKUP_DELETE_TARGET
DECLARE @MAX_IDX INT
SELECT TOP 1 @MAX_IDX = IDX
FROM @LOG_BACKUP_DELETE_TARGET
ORDER BY IDX DESC
WHILE (@MAX_IDX > 0) BEGIN
SELECT @DEL_TARGET_DEV_NAME = NAME
FROM @LOG_BACKUP_DELETE_TARGET
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, 40),40) + '_'
+ @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
go
exec master.dbo.ap_db_backup 'master','e:\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)
--RESTORE SERVICE MASTER KEY FROM FILE = 'E:\BACKUP_SQLTAG\filename' DECRYPTION BY PASSWORD = 'P@SSW0RD' -- [FORCE]
IF OBJECT_ID('AP_DB_BACKUP_ENZIP') IS NULL
EXEC ('CREATE PROC AP_DB_BACKUP_ENZIP AS SELECT 1 ')
GO
ALTER PROC AP_DB_BACKUP_ENZIP
(
@DBNAME VARCHAR(20) = 'MASTER' -- BACKUP TARGET DATABASE
, @BEFORE_TARGET_TIME_HH INT = 0 -- DELETE FILE BEFORE TIME HH
, @COMPRESS_0123 VARCHAR(100) = '0' -- 0은 압축하지 않음
, @PASSWORD VARCHAR(100) = 'MINSOUK'
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- 7ZIP COMMANDLINE 프로그램을 C:\WINDOWS\SYSTEM32 에 넣어줘야 한다.
-- SP_CONFIGURE XP_CMDSHELL 확인하고 원복한다.
-- 작업파일 시간전 예전파일을 지우고 시작한다.
-- fbak 파일이 없으면 그냥 끝난다.
-- VERSION 20131115
IF NOT EXISTS (SELECT * FROM MASTER.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AP_DB_BACKUP_ENZIP_HISTORY')
BEGIN
EXEC ('
CREATE TABLE MASTER.DBO.AP_DB_BACKUP_ENZIP_HISTORY
(
IDX INT IDENTITY(1,1) PRIMARY KEY
, DBNAME NVARCHAR(50)
, EN_ZIP_TIME DATETIME
, EN_ZIP_FILENAME NVARCHAR(1000)
, PHYNAME NVARCHAR(1000)
)
')
END
DECLARE @LOG_BACKUP_DELETE_TARGET TABLE
(IDX INT IDENTITY(1,1)
,NAME VARCHAR(1000)
)
DECLARE @CAL_BEFORE_TARGET_TIME DATETIME = DATEADD(HOUR,-1*ABS(@BEFORE_TARGET_TIME_HH),GETDATE())
, @PHYNAME NVARCHAR(1000)
, @CMD_STRING NVARCHAR(1000)
, @DEL_TARGET_DEV_NAME VARCHAR(1000)
, @FILE_EXISTS INT
INSERT INTO @LOG_BACKUP_DELETE_TARGET
SELECT EN_ZIP_FILENAME
FROM MASTER.DBO.AP_DB_BACKUP_ENZIP_HISTORY
WHERE EN_ZIP_TIME < @CAL_BEFORE_TARGET_TIME
DECLARE @MAX_IDX INT
SELECT TOP 1 @MAX_IDX = IDX
FROM @LOG_BACKUP_DELETE_TARGET
ORDER BY IDX DESC
WHILE (@MAX_IDX > 0)
BEGIN
SELECT @DEL_TARGET_DEV_NAME = NAME
FROM @LOG_BACKUP_DELETE_TARGET
WHERE IDX = @MAX_IDX
IF @@ROWCOUNT = 0 BREAK;
BEGIN TRY
SET @CMD_STRING = 'DEL '+ @DEL_TARGET_DEV_NAME
DELETE MASTER..AP_DB_BACKUP_ENZIP_HISTORY WHERE EN_ZIP_FILENAME = @DEL_TARGET_DEV_NAME
EXEC MASTER..XP_CMDSHELL @CMD_STRING, NO_OUTPUT
SELECT @CMD_STRING
SELECT 1
END TRY
BEGIN CATCH
DELETE MASTER..AP_DB_BACKUP_ENZIP_HISTORY WHERE EN_ZIP_FILENAME = @DEL_TARGET_DEV_NAME
SELECT 'ERROR !'
END CATCH
SET @MAX_IDX -= 1
END
SELECT TOP 1 @PHYNAME = A.PHYNAME
FROM MASTER..SYSDEVICES A
LEFT OUTER JOIN MASTER..AP_DB_BACKUP_ENZIP_HISTORY B
ON A.PHYNAME = B.PHYNAME
WHERE NAME LIKE '%'+'SQLFB_'+ REPLACE(@@SERVERNAME,'\','_')+'_'+ @DBNAME+'%'
AND B.PHYNAME IS NULL
ORDER BY RIGHT (NAME, 15) DESC
IF @PHYNAME IS NULL OR @PHYNAME = ''
BEGIN
SELECT 'EXECUTE AP_DB_BACKUP FIRST!'
RETURN 0
END
DECLARE @TBL_SP_CONFIGURE TABLE (NAME VARCHAR(100), MINIMUM INT, MAXMUM INT, CONFIG_VALUE INT, RUN_VALUE INT)
DECLARE @PREVIOUS_XP_CMDSHELL_BIT INT
INSERT INTO @TBL_SP_CONFIGURE EXEC SP_CONFIGURE 'XP_CMDSHELL'
SELECT @PREVIOUS_XP_CMDSHELL_BIT = RUN_VALUE
FROM @TBL_SP_CONFIGURE
IF @PREVIOUS_XP_CMDSHELL_BIT = 0
BEGIN
EXEC SP_CONFIGURE 'XP_CMDSHELL' ,1
RECONFIGURE WITH OVERRIDE
END
EXEC MASTER.DBO.XP_FILEEXIST @PHYNAME, @FILE_EXISTS OUT
IF @FILE_EXISTS = 1
BEGIN
-- CASE SENSETIVE
set @cmd_string = '7za.exe a -p'+@password+' -mx'+@compress_0123+' -t7z '+@phyname+'.ENZIP ' + @phyname
-- CASE SENSETIVE
EXEC MASTER..XP_CMDSHELL @CMD_STRING, NO_OUTPUT
SET @CMD_STRING = 'DEL '+ @PHYNAME
EXEC MASTER..XP_CMDSHELL @CMD_STRING, NO_OUTPUT
INSERT INTO MASTER.DBO.AP_DB_BACKUP_ENZIP_HISTORY (DBNAME, EN_ZIP_TIME, EN_ZIP_FILENAME, PHYNAME) VALUES (@DBNAME, GETDATE(), @PHYNAME+'.ENZIP',@PHYNAME)
END
IF @PREVIOUS_XP_CMDSHELL_BIT = 0
BEGIN
EXEC SP_CONFIGURE 'XP_CMDSHELL' ,0
RECONFIGURE WITH OVERRIDE
END
GO