SQL Server 백업 및 복원 시나리오 스크립트

/* Level 100 의 백업 복원 전략 수립하기 */

https://msdn.microsoft.com/ko-kr/library/ms186858(v=sql.120).aspx

 

[환경구성]

 

    USE MASTER

    GO

 

    CREATE DATABASE BACKUPTEST

    GO

 

    USE BACKUPTEST

    GO

 

    CREATE TABLE TBL(COL1 VARCHAR(50), COL2 DATETIME)

    GO

 

    INSERT INTO TBL(COL1, COL2) VALUES ('일상데이터', GETDATE())

 

    SELECT * FROM TBL

    /*

    COL1        COL2

    일상데이터    2016-03-17 11:28:00.547

    */

 

[백업 정책 적용 및 데이터 입력]

 

1. 최초 전체백업 수행

    USE master

    GO

    BACKUP DATABASE BACKUPTEST TO DISK='E:\SQLData9\BACKUPTEST_FULL_01.BAK' WITH NAME='전체백업1'

 

    INSERT INTO BACKUPTEST.DBO.TBL(COL1, COL2) VALUES ('전체백업1_후', GETDATE())

 

2. 트랜잭션 로그 백업 수행

 

    BACKUP LOG BACKUPTEST TO DISK='E:\SQLData9\BACKUPTEST_LOG_01.TRN' WITH NAME='로그백업1'

 

    INSERT INTO BACKUPTEST.DBO.TBL(COL1, COL2) VALUES ('로그백업1_후', GETDATE())

    INSERT INTO BACKUPTEST.DBO.TBL(COL1, COL2) VALUES ('로그백업1_후1', GETDATE())

    INSERT INTO BACKUPTEST.DBO.TBL(COL1, COL2) VALUES ('로그백업1_후2', GETDATE())

    INSERT INTO BACKUPTEST.DBO.TBL(COL1, COL2) VALUES ('로그백업1_후3', GETDATE())

    INSERT INTO BACKUPTEST.DBO.TBL(COL1, COL2) VALUES ('로그백업1_후4', GETDATE())

    INSERT INTO BACKUPTEST.DBO.TBL(COL1, COL2) VALUES ('로그백업1_후5', GETDATE())

 

 

    BACKUP LOG BACKUPTEST TO DISK='E:\SQLData9\BACKUPTEST_LOG_02.TRN' WITH NAME='로그백업2'

    INSERT INTO BACKUPTEST.DBO.TBL(COL1, COL2) VALUES ('로그백업2_후', GETDATE())

 

3. 증분백업 수행.

 

    BACKUP DATABASE BACKUPTEST TO DISK='E:\SQLData9\BACKUPTEST_DIFF_01.BAK' WITH DIFFERENTIAL, NAME='증분백업1'

    INSERT INTO BACKUPTEST.DBO.TBL(COL1, COL2) VALUES ('증분백업1_후', GETDATE())

 

 

4. 트랜잭션 로그 백업 수행

 

    BACKUP LOG BACKUPTEST TO DISK='E:\SQLData9\BACKUPTEST_LOG_03.TRN' WITH NAME='로그백업3'

 

    INSERT INTO BACKUPTEST.DBO.TBL(COL1, COL2) VALUES ('로그백업3_후', GETDATE())

 

    BACKUP LOG BACKUPTEST TO DISK='E:\SQLData9\BACKUPTEST_LOG_04.TRN' WITH NAME='로그백업4'

    INSERT INTO BACKUPTEST.DBO.TBL(COL1, COL2) VALUES ('로그백업4_후', GETDATE())

 

 

5. 데이터 확인

 

    USE BACKUPTEST

    GO

 

    SELECT * FROM TBL

 

    /*

    COL1    COL2

    일상데이터    2016-03-17 13:42:23.300

    전체백업1_후    2016-03-17 13:47:44.900

    로그백업1_후    2016-03-17 13:47:48.497

    로그백업1_후1    2016-03-17 13:47:48.497

    로그백업1_후2    2016-03-17 13:47:48.497

    로그백업1_후3    2016-03-17 13:47:48.497

    로그백업1_후4    2016-03-17 13:47:48.500

    로그백업1_후5    2016-03-17 13:47:48.500

    로그백업2_후    2016-03-17 13:47:51.847

    증분백업1_후    2016-03-17 13:47:58.173

    로그백업3_후    2016-03-17 13:48:01.673

    로그백업4_후    2016-03-17 13:48:01.803

    */

 

 

[복원 시나리오별]

1. [로그백업4_후] 데이터 입력 후 잘못된 데이터로 UPDATE 수행했다.

    -- 장애 발생시키자.

    UPDATE A SET COL2 = '1900-01-01' FROM BACKUPTEST.DBO.TBL A WHERE COL1 = '로그백업4_후';

    SELECT GETDATE() --2016-03-17 13:56:53.040

    

    장애 시점 이전으로 돌리자.

    [Action Plan]

    -- 비상로그 백업을 수행하자.

    BACKUP LOG BACKUPTEST TO DISK='E:\SQLData9\BACKUPTEST_LOG_05.TRN' WITH NAME='로그백업5-비상', NO_TRUNCATE

 

    

 

    -- 논리적이름 및 파일위치를 확인한다.

    RESTORE FILELISTONLY FROM DISK='E:\SQLData9\BACKUPTEST_FULL_01.BAK'

    /*

    LogicalName        PhysicalName                                                                        ype

    BACKUPTEST        E:\Program Files\Microsoft SQL Server\MSSQL12.GHOST\MSSQL\DATA\BACKUPTEST.mdf        D

    BACKUPTEST_log    E:\Program Files\Microsoft SQL Server\MSSQL12.GHOST\MSSQL\DATA\BACKUPTEST_log.ldf    L

    */

 

 

 

 

    RESTORE DATABASE BACKUPTEST FROM DISK = 'C:\TEMP\sungjuk2_FULL.BAK' WITH NORECOVERY

    GO

    -- 백업 파일로 차례로 복원하자.

    -- 전체백업1 > 증분백업1 > 로그백업3 > 로그백업4 > 로그백업5-비상 시점복원하자.

 

    RESTORE DATABASE BACKUPTEST_RE

     FROM DISK='E:\SQLData9\BACKUPTEST_FULL_01.BAK'

     WITH MOVE 'BACKUPTEST' TO 'E:\SQLData9\BACKUPTEST_RE.mdf',

     MOVE 'BACKUPTEST_log' TO 'E:\SQLData9\BACKUPTEST_RE_log.ldf'

     , NORECOVERY

 

    RESTORE DATABASE BACKUPTEST_RE

     FROM DISK='E:\SQLData9\BACKUPTEST_DIFF_01.BAK'

     WITH NORECOVERY

 

    RESTORE LOG BACKUPTEST_RE

     FROM DISK='E:\SQLData9\BACKUPTEST_LOG_03.TRN'

     WITH NORECOVERY

 

    RESTORE LOG BACKUPTEST_RE

     FROM DISK='E:\SQLData9\BACKUPTEST_LOG_04.TRN'

     WITH NORECOVERY

 

    RESTORE LOG BACKUPTEST_RE

     FROM DISK='E:\SQLData9\BACKUPTEST_LOG_05.TRN'

     WITH STOPAT='2016-03-17 13:56:53.000' -- [2016-03-17 13:56:53.040 ]장애시간보다 작은 시간대로 변경하자.

     , RECOVERY -- 최종파일 옵원시 RECOVERY 옵션으로 변경하여 온라인시키자

 

 

    -- 데이터 확인하자.

    SELECT * FROM BACKUPTEST_RE.DBO.TBL

    /*

        COL1            COL2

        증분백업1_후    2016-03-17 13:47:58.173

        로그백업3_후    2016-03-17 13:48:01.673

        로그백업4_후    2016-03-17 13:48:01.803

    */

 

 

 

2. 비상로그는 몇번이고 받아도 된다고 전해라 ??

    결론 : COPY_ONLY 와 같은 옵션이라고 생각하면 된다.

    -- 비상로그 백업을 수행하자.

    BACKUP LOG BACKUPTEST TO DISK='E:\SQLData9\BACKUPTEST_LOG_06.TRN' WITH NAME='로그백업6-비상', NO_TRUNCATE

 

    /*

    name            first_lsn        last_lsn        checkpoint_lsn

    로그백업4        42000000040800001    42000000042400001    42000000036800038

    로그백업5-비상        42000000042400001    42000000045600001    42000000036800038

    로그백업6-비상        42000000042400001    42000000045600001    42000000036800038

    */

 

    -- 백업상태 확인하는 쿼리 --

    SELECT

        A.backup_set_id,

        B.physical_device_name AS FILE_LOCATION,

        A.database_name AS DATABASE_NAME,

        CAST(CAST(A.backup_size / 1000000 AS INT) AS VARCHAR(14))+ ' ' + 'MB' AS BACKUP_SIZE,

        CAST(DATEDIFF(second, A.backup_start_date, A.backup_finish_date) AS VARCHAR(4)) + ' '+ 'Seconds' AS BACKUP_TIME_TAKEN,

        A.backup_start_date AS BACKUP_DATE_TIME,

        CASE A.[type]

        WHEN 'D' THEN 'Full'

        WHEN 'I' THEN 'Differential'

        WHEN 'L' THEN 'Transaction Log'

        END AS BACKUP_TYPE,

        A.server_name AS SERVER_NAME,

        A.name

        ,A.first_lsn, A.last_lsn, A.checkpoint_lsn, A.database_backup_lsn

    FROM msdb.dbo.backupset A

         INNER JOIN msdb.dbo.backupmediafamily B

         ON A.media_set_id = B.media_set_id

    WHERE A.database_name IN ('BACKUPTEST')

    ORDER BY A.backup_set_id ASC;

키워드 : 백업스크립트