MSSQL FULL 백업 및 TRANSACTION LOG 백업 프로시저

/*
 
새벽 2시 FULL 백업(bak)
 
새벽 3시30분부터 4시간 단위로 트랜잭션 로그 백업, 하루 총 6개의 파일(trn)
 
MTBACKUP -> 백업 완료 일시 체크 테이블
 
 
 
--DB명
 
DECLARE @sid VARCHAR(20) = 'BACKUPTESTDB'
 
 
 
--BACKUP 폴더 경로(아래 경로에는 당일 데이터만 존재
DECLARE @backupFilePath NVARCHAR(1000) = N'D:\BACKUPTEST\'
 
 
 
--ARCHIVE 폴더 경로(아래 경로에는 당일 제외 3일치 데이터 존재
DECLARE @archiveFilePath NVARCHAR(1000) = N'D:\ARCHIVETEST\'
 
 
DECLARE @fullBackupFileExtension VARCHAR(10) = '.bak' --FULL 백업 확장자
DECLARE @tranBackupFileExtension VARCHAR(10) = '.trn' --TRN 백업 확장자
 
*/
 
USE BACKUPTESTDB
GO
/****** Object:  StoredProcedure [dbo].[SP_MT_MMI_CHECK]    Script Date: 2021-02-22 오후 2:24:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_BACKUP]
 
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;
 
 
/* jh hyun, 2021.02.22 */
/* jh hyun, 2021.02.22 */
/* jh hyun, 2021.02.22 */
/* jh hyun, 2021.02.22 */
 
/*
--XP_CMDSHELL을 사용하기 위해 설정 변경하는 쿼리 / DEFAULT : 0
EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS', 1
GO
RECONFIGURE
GO
EXEC SP_CONFIGURE 'XP_CMDSHELL', 1
GO
RECONFIGURE
GO
*/
 
 
/*
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MTBACKUP](
    [BackupDate] [varchar](8) NOT NULL,
    [FullBackupTime] [varchar](6) NULL,
    [TransactionLogTime1] [varchar](6) NULL,
    [TransactionLogTime2] [varchar](6) NULL,
    [TransactionLogTime3] [varchar](6) NULL,
    [TransactionLogTime4] [varchar](6) NULL,
    [TransactionLogTime5] [varchar](6) NULL,
    [TransactionLogTime6] [varchar](6) NULL,
    CONSTRAINT [PK_MTBACKUP] PRIMARY KEY CLUSTERED ( [BackupDate] ASC ) 
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MTBACKUP] ADD  CONSTRAINT [DF_MTBACKUP_FullBackupTime]  DEFAULT ('') FOR [FullBackupTime]
GO
ALTER TABLE [dbo].[MTBACKUP] ADD  CONSTRAINT [DF_MTBACKUP_TransactionLogTime1]  DEFAULT ('') FOR [TransactionLogTime1]
GO
ALTER TABLE [dbo].[MTBACKUP] ADD  CONSTRAINT [DF_MTBACKUP_TransactionLogTime2]  DEFAULT ('') FOR [TransactionLogTime2]
GO
ALTER TABLE [dbo].[MTBACKUP] ADD  CONSTRAINT [DF_MTBACKUP_TransactionLogTime3]  DEFAULT ('') FOR [TransactionLogTime3]
GO
ALTER TABLE [dbo].[MTBACKUP] ADD  CONSTRAINT [DF_MTBACKUP_TransactionLogTime4]  DEFAULT ('') FOR [TransactionLogTime4]
GO
ALTER TABLE [dbo].[MTBACKUP] ADD  CONSTRAINT [DF_MTBACKUP_TransactionLogTime5]  DEFAULT ('') FOR [TransactionLogTime5]
GO
ALTER TABLE [dbo].[MTBACKUP] ADD  CONSTRAINT [DF_MTBACKUP_TransactionLogTime6]  DEFAULT ('') FOR [TransactionLogTime6]
GO
*/
 
/*
 * DB FULL BACKUP File Name : @SID + '_' + yyyyMMdd + '_' + 'hhmmss' + '.' + @EXTENSION
 * TRANSACTION LOG File Name : @SID + '_' + 'LOG' + '_' + yyyyMMdd + '_' + 'hhmmss' + '.' + @EXTENSION
*/
 
 
 
BEGIN TRY
 
-- Default Setting
DECLARE @sid                        VARCHAR(20)            = 'BACKUPTESTDB'
DECLARE @backupFilePath                NVARCHAR(1000)        = N'D:\BACKUPTEST\'
DECLARE @archiveFilePath            NVARCHAR(1000)        = N'D:\ARCHIVETEST\'
DECLARE @fullBackupFileExtension    VARCHAR(10)            = '.bak'
DECLARE @tranBackupFileExtension    VARCHAR(10)            = '.trn'
 
DECLARE @fullBackupFileCreateQuery    NVARCHAR(2000)
DECLARE @tranBackupFileCreateQuery    NVARCHAR(2000)
 
DECLARE @fileMoveQuery                NVARCHAR(2000)
DECLARE @fileMoveOupt                NVARCHAR(1000)
 
DECLARE @fileDeleteQuery            NVARCHAR(2000)
DECLARE @fileDeleteOupt                NVARCHAR(1000)
 
DECLARE @fileCheckDate                NVARCHAR(100)
 
DECLARE @transactionSeq                VARCHAR(10)
 
DECLARE @MOVETABLE TABLE 
( 
    OUPT NVARCHAR(MAX) 
)
DECLARE @DELETETABLE TABLE 
( 
    OUPT NVARCHAR(MAX) 
)
 
 
/************************************************************************************************************************************************************************************
* STEP 1. DB FULL BACKUP 
************************************************************************************************************************************************************************************/
IF  LEFT(REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':',''),4) >= '0200'                                                                            -- After 02:00 AM
    AND (
            NOT EXISTS (SELECT 1 FROM MTBACKUP WITH(NOLOCK) WHERE BackupDate = CONVERT(VARCHAR(8),GETDATE(),112))                                -- Not exists backup history
             OR EXISTS (SELECT 1 FROM MTBACKUP WITH(NOLOCK) WHERE BackupDate = CONVERT(VARCHAR(8),GETDATE(),112) AND FullBackupTime = '' )        -- Exists backup history + not execute full backup
    )
    
    BEGIN
            /************************************************************************************************************************************************************************************
            * STEP 1-1. File Move 'Backup' folder to 'Archive' folder - bak file
            ************************************************************************************************************************************************************************************/
            SELECT @fileMoveQuery = N' EXEC XP_CMDSHELL '' for %i in (' + @backupFilePath + @sid + '*' + @fullBackupFileExtension + ') do echo %i'' '
 
            INSERT INTO @MOVETABLE
            EXEC SP_EXECUTESQL @fileMoveQuery
 
            
            DECLARE bakFileMoveCursor CURSOR FOR
            (
                SELECT 
                    OUPT
                FROM @MOVETABLE 
                WHERE OUPT IS NOT NULL
                AND CHARINDEX('echo',OUPT) = 0
            )
            OPEN bakFileMoveCursor
            FETCH NEXT FROM bakFileMoveCursor INTO @fileMoveOupt
            WHILE @@FETCH_STATUS = 0
                BEGIN
                    SELECT @fileCheckDate = LEFT(REPLACE(REPLACE(@fileMoveOupt,@backupFilePath + @sid + '_',''),@fullBackupFileExtension,''),8)
 
                    IF @fileCheckDate = CONVERT(VARCHAR(8),GETDATE(),112)
                        BEGIN
                            FETCH NEXT FROM bakFileMoveCursor INTO @fileMoveOupt
                            CONTINUE;
                        END
 
                    SELECT @fileMoveQuery = N' EXEC XP_CMDSHELL '' move ' + @fileMoveOupt + ' ' + @archiveFilePath + REPLACE(@fileMoveOupt,@backupFilePath,'') +' '' '
 
                    EXEC SP_EXECUTESQL @fileMoveQuery
 
                    FETCH NEXT FROM bakFileMoveCursor INTO @fileMoveOupt
                END
            CLOSE bakFileMoveCursor
            DEALLOCATE bakFileMoveCursor
 
 
 
 
            /************************************************************************************************************************************************************************************
            * STEP 1-2. Database Full Backup - bak file
            ************************************************************************************************************************************************************************************/
            SELECT @fullBackupFileCreateQuery = N'BACKUP DATABASE ' + @sid + ' TO DISK = ''' + @backupFilePath    + @sid + '_' + CONVERT(VARCHAR(10),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':','') + @fullBackupFileExtension + ''''+ ' WITH NOINIT;'
 
            EXEC SP_EXECUTESQL @fullBackupFileCreateQuery
 
            IF EXISTS ( SELECT 1 FROM MTBACKUP WITH(NOLOCK) WHERE BackupDate = CONVERT(VARCHAR(8),GETDATE(),112) )
                BEGIN
                    UPDATE MTBACKUP
                    SET FullBackupTime = REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':','')
                    WHERE BackupDate = CONVERT(VARCHAR(8),GETDATE(),112)
                END
            ELSE
                BEGIN
                    INSERT INTO MTBACKUP ( BackupDate, FullBackupTime )
                    VALUES ( CONVERT(VARCHAR(8),GETDATE(),112), REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':','') )
                END
 
 
 
            /************************************************************************************************************************************************************************************
            * STEP 1-3. Expired File Delete 'Archive' folder - bak file
            ************************************************************************************************************************************************************************************/
            SELECT @fileDeleteQuery = N' EXEC XP_CMDSHELL '' for %i in (' + @archiveFilePath + @sid + '*' + @fullBackupFileExtension + ') do echo %i'' '
 
            INSERT INTO @DELETETABLE
            EXEC SP_EXECUTESQL @fileDeleteQuery
 
 
            DECLARE bakFileDeleteCursor CURSOR FOR
            (
                SELECT 
                    OUPT
                FROM @DELETETABLE 
                WHERE OUPT IS NOT NULL
                AND CHARINDEX('echo',OUPT) = 0
            )
            OPEN bakFileDeleteCursor
            FETCH NEXT FROM bakFileDeleteCursor INTO @fileDeleteOupt
            WHILE @@FETCH_STATUS = 0
                BEGIN
                    SELECT @fileCheckDate = LEFT(REPLACE(REPLACE(@fileDeleteOupt,@archiveFilePath + @sid + '_',''),@fullBackupFileExtension,''),8)
 
                    IF @fileCheckDate < CONVERT(VARCHAR(8),DATEADD(DAY,-3,GETDATE()),112)
                        BEGIN
                            SELECT @fileDeleteQuery = N' EXEC XP_CMDSHELL '' del /f /q "' + @fileDeleteOupt + '" '' '
                
                            EXEC SP_EXECUTESQL @fileDeleteQuery
                        END
                    FETCH NEXT FROM bakFileDeleteCursor INTO @fileDeleteOupt
                END
            CLOSE bakFileDeleteCursor
            DEALLOCATE bakFileDeleteCursor
            
    END
 
DELETE @MOVETABLE
DELETE @DELETETABLE
SELECT @fileMoveQuery = ''
SELECT @fileDeleteQuery = ''
SELECT @fileMoveOupt = ''
SELECT @fileDeleteOupt = ''
 
/************************************************************************************************************************************************************************************
* STEP 2. TRANSACTION LOG BACKUP 
************************************************************************************************************************************************************************************/
SELECT @transactionSeq = NULL
 
IF LEFT(REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':',''),4) >= '0330' AND LEFT(REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':',''),4) < '0730'
    BEGIN
        SELECT @transactionSeq = '1' FROM MTBACKUP WITH(NOLOCK) WHERE BackupDate = CONVERT(VARCHAR(8),GETDATE(),112) AND TransactionLogTime1 = ''
    END
ELSE IF LEFT(REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':',''),4) >= '0730' AND LEFT(REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':',''),4) < '1130'
    BEGIN
        SELECT @transactionSeq = '2' FROM MTBACKUP WITH(NOLOCK) WHERE BackupDate = CONVERT(VARCHAR(8),GETDATE(),112) AND TransactionLogTime2 = ''
    END
ELSE IF LEFT(REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':',''),4) >= '1130' AND LEFT(REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':',''),4) < '1530'
    BEGIN
        SELECT @transactionSeq = '3' FROM MTBACKUP WITH(NOLOCK) WHERE BackupDate = CONVERT(VARCHAR(8),GETDATE(),112) AND TransactionLogTime3 = ''
    END
ELSE IF LEFT(REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':',''),4) >= '1530' AND LEFT(REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':',''),4) < '1930'
    BEGIN
        SELECT @transactionSeq = '4' FROM MTBACKUP WITH(NOLOCK) WHERE BackupDate = CONVERT(VARCHAR(8),GETDATE(),112) AND TransactionLogTime4 = ''
    END
ELSE IF LEFT(REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':',''),4) >= '1930' AND LEFT(REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':',''),4) < '2330'
    BEGIN
        SELECT @transactionSeq = '5' FROM MTBACKUP WITH(NOLOCK) WHERE BackupDate = CONVERT(VARCHAR(8),GETDATE(),112) AND TransactionLogTime5 = ''
    END
ELSE IF LEFT(REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':',''),4) >= '2330' AND LEFT(REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':',''),4) <= '2359'
    BEGIN
        SELECT @transactionSeq = '6' FROM MTBACKUP WITH(NOLOCK) WHERE BackupDate = CONVERT(VARCHAR(8),GETDATE(),112) AND TransactionLogTime6 = ''
    END
ELSE IF LEFT(REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':',''),4) >= '0000' AND LEFT(REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':',''),4) < '0330'
    BEGIN
        SELECT @transactionSeq = '7' FROM MTBACKUP WITH(NOLOCK) WHERE BackupDate = CONVERT(VARCHAR(8),GETDATE() -1,112) AND TransactionLogTime6 = ''
    END
 
 
IF ISNULL(@transactionSeq, '') <> ''
    BEGIN
 
            /************************************************************************************************************************************************************************************
            * STEP 2-1. File Move 'Backup' folder to 'Archive' folder - trn file
            ************************************************************************************************************************************************************************************/
            SELECT @fileMoveQuery = N' EXEC XP_CMDSHELL '' for %i in (' + @backupFilePath + @sid +  + '_LOG' + '*' + @tranBackupFileExtension + ') do echo %i'' '
 
            INSERT INTO @MOVETABLE
            EXEC SP_EXECUTESQL @fileMoveQuery
 
            
            DECLARE trnFileMoveCurcor CURSOR FOR
            (
                SELECT 
                    OUPT
                FROM @MOVETABLE 
                WHERE OUPT IS NOT NULL
                AND CHARINDEX('echo',OUPT) = 0
            )
            OPEN trnFileMoveCurcor
            FETCH NEXT FROM trnFileMoveCurcor INTO @fileMoveOupt
            WHILE @@FETCH_STATUS = 0
                BEGIN
                    SELECT @fileCheckDate = LEFT(REPLACE(REPLACE(@fileMoveOupt,@backupFilePath + @sid  + '_LOG' + '_',''),@tranBackupFileExtension,''),8)
 
                    IF @fileCheckDate = CONVERT(VARCHAR(8),GETDATE(),112)
                        BEGIN
                            FETCH NEXT FROM trnFileMoveCurcor INTO @fileMoveOupt
                            CONTINUE;
                        END
 
                    SELECT @fileMoveQuery = N' EXEC XP_CMDSHELL '' move ' + @fileMoveOupt + ' ' + @archiveFilePath + REPLACE(@fileMoveOupt,@backupFilePath,'') +' '' '
        
                    EXEC SP_EXECUTESQL @fileMoveQuery
 
                    FETCH NEXT FROM trnFileMoveCurcor INTO @fileMoveOupt
                END
            CLOSE trnFileMoveCurcor
            DEALLOCATE trnFileMoveCurcor
 
            /************************************************************************************************************************************************************************************
            * STEP 2-2. Database Full Backup - trn file
            ************************************************************************************************************************************************************************************/
            SELECT @tranBackupFileCreateQuery = N'BACKUP LOG ' + @sid + ' TO DISK = ''' + @backupFilePath    + @sid  + '_LOG' + '_' + CONVERT(VARCHAR(10),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':','') + @tranBackupFileExtension + ''''+ ' WITH NOINIT;'
 
            EXEC SP_EXECUTESQL @tranBackupFileCreateQuery
 
            IF (@transactionSeq = '7' AND EXISTS ( SELECT 1 FROM MTBACKUP WITH(NOLOCK) WHERE BackupDate = CONVERT(VARCHAR(8),GETDATE() - 1,112) ))
                OR (@transactionSeq IN ('1','2','3','4','5','6') AND EXISTS ( SELECT 1 FROM MTBACKUP WITH(NOLOCK) WHERE BackupDate = CONVERT(VARCHAR(8),GETDATE(),112) ))
                BEGIN
                    IF @transactionSeq = '7'
                        BEGIN
                            SELECT @tranBackupFileCreateQuery =
                                              ' UPDATE MTBACKUP '
                                            + ' SET TransactionLogTime' + '6' + ' = REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),'':'','''') '
                                            + ' WHERE BackupDate = CONVERT(VARCHAR(8),GETDATE() - 1,112) '
                        END
                    ELSE 
                        BEGIN
                            SELECT @tranBackupFileCreateQuery =
                                              ' UPDATE MTBACKUP '
                                            + ' SET TransactionLogTime' + @transactionSeq + ' = REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),'':'','''') '
                                            + ' WHERE BackupDate = CONVERT(VARCHAR(8),GETDATE(),112) '
                        END
                    
                    EXEC SP_EXECUTESQL @tranBackupFileCreateQuery
                END
            ELSE
                BEGIN
                    IF @transactionSeq = '7'
                        BEGIN
                            SELECT @tranBackupFileCreateQuery =
                                              ' INSERT INTO MTBACKUP ( BackupDate, TransactionLogTime' + '6' + ' ) '
                                            + ' VALUES ( CONVERT(VARCHAR(8),GETDATE() - 1,112), REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),'':'','''') ) '
                        END
                    ELSE
                        BEGIN
                            SELECT @tranBackupFileCreateQuery =
                                              ' INSERT INTO MTBACKUP ( BackupDate, TransactionLogTime' + @transactionSeq + ' ) '
                                            + ' VALUES ( CONVERT(VARCHAR(8),GETDATE(),112), REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),'':'','''') ) '
                        END
                    
                    EXEC SP_EXECUTESQL @tranBackupFileCreateQuery
                END
    
            /************************************************************************************************************************************************************************************
            * STEP 2-3. Expired File Delete 'Archive' folder - trn file
            ************************************************************************************************************************************************************************************/
            SELECT @fileDeleteQuery = N' EXEC XP_CMDSHELL '' for %i in (' + @archiveFilePath + @sid + '*' + @tranBackupFileExtension + ') do echo %i'' '
 
            INSERT INTO @DELETETABLE
            EXEC SP_EXECUTESQL @fileDeleteQuery
 
 
            DECLARE trnFileDeleteCurcor CURSOR FOR
            (
                SELECT 
                    OUPT
                FROM @DELETETABLE 
                WHERE OUPT IS NOT NULL
                AND CHARINDEX('echo',OUPT) = 0
            )
            OPEN trnFileDeleteCurcor
            FETCH NEXT FROM trnFileDeleteCurcor INTO @fileDeleteOupt
            WHILE @@FETCH_STATUS = 0
                BEGIN
                    SELECT @fileCheckDate = LEFT(REPLACE(REPLACE(@fileDeleteOupt,@archiveFilePath + @sid + '_LOG' + '_',''), @tranBackupFileExtension,''),8)
 
                    IF @fileCheckDate < CONVERT(VARCHAR(8),DATEADD(DAY,-3,GETDATE()),112)
                        BEGIN
                            SELECT @fileDeleteQuery = N' EXEC XP_CMDSHELL '' del /f /q "' + @fileDeleteOupt + '" '' '
                
                            EXEC SP_EXECUTESQL @fileDeleteQuery
                        END
                    FETCH NEXT FROM trnFileDeleteCurcor INTO @fileDeleteOupt
                END
            CLOSE trnFileDeleteCurcor
            DEALLOCATE trnFileDeleteCurcor
 
    END
 
--SELECT * FROM MTBACKUP
--DELETE FROM MTBACKUP
 
END TRY
BEGIN CATCH
 
END CATCH