SQL Server 백업 with copy_only 사용하기 2탄

-- 시나리오

전체백업 + 전체백업 with copy_only + 로그백업 ..... + 로그백업

백업파일이 존재한다.

여기서 복원시

전체백업 with copy_only + 마지막 로그백까지 전체복원이 되는가?

결론 : 잘된다. [로그백업 with copy_only] 이후의 로그백업을 순차적으로 복원하면 가능하다.

 

use master

GO

 

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'BACKUPTEST')

    DROP DATABASE BACKUPTEST

GO

 

CREATE DATABASE BACKUPTEST

GO

 

USE BACKUPTEST

GO

 

--drop table TBL

IF OBJECT_ID('dbo.TBL') IS NOT NULL

    drop table TBL

GO

 

CREATE TABLE TBL(COL1 VARCHAR(50))

GO

 

INSERT INTO TBL VALUES('백업전')

BACKUP DATABASE BACKUPTEST TO DISK='E:\LAB\D___FUL1.BAK'

INSERT INTO TBL VALUES('카피백업전')

BACKUP DATABASE BACKUPTEST TO DISK='E:\LAB\D__COPY_FUL2.BAK' with copy_only

INSERT INTO TBL VALUES('로그백업전1')

BACKUP LOG BACKUPTEST TO DISK='E:\LAB\D___TRN1.TRN'

INSERT INTO TBL VALUES('로그백업전2')

BACKUP LOG BACKUPTEST TO DISK='E:\LAB\D___TRN2.TRN'

INSERT INTO TBL VALUES('로그백업전3')

BACKUP LOG BACKUPTEST TO DISK='E:\LAB\D___TRN3.TRN'

 

select * from tbl

백업전

카피백업전

로그백업전1

로그백업전2

로그백업전3

 

-- 복원해보자

use master

go

 

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'BACKUPTEST_BAK')

    DROP DATABASE BACKUPTEST_BAK

GO

 

-- 전체백업 with copy_only

RESTORE DATABASE BACKUPTEST_BAK FROM DISK = N'E:\LAB\D__COPY_FUL2.BAK' WITH FILE = 1,

MOVE N'BACKUPTEST' TO N'E:\SQLDATA\BACKUPTEST_BAK.mdf',

MOVE N'BACKUPTEST_log' TO N'E:\SQLDATA\BACKUPTEST_BAK_log.ldf', NOUNLOAD, STATS = 5

, NORECOVERY

 

GO

RESTORE LOG [BACKUPTEST_BAK] FROM DISK = N'E:\LAB\D___TRN1.TRN' WITH FILE = 1, NOUNLOAD, STATS = 10 , RECOVERY

 

 

use BACKUPTEST_BAK

go

select * from tbl

 

 

=====================

그럼 처음 전체백업 + 로그백업 복원은 가능한가?

결론 : 이렇게 해도 복원은 잘된다.

 

-- 복원해보자

use master

go

 

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'BACKUPTEST_BAK')

    DROP DATABASE BACKUPTEST_BAK

GO

 

-- 전체백업 with copy_only

RESTORE DATABASE BACKUPTEST_BAK FROM DISK = N'E:\LAB\D___FUL1.BAK' WITH FILE = 1,

MOVE N'BACKUPTEST' TO N'E:\SQLDATA\BACKUPTEST_BAK.mdf',

MOVE N'BACKUPTEST_log' TO N'E:\SQLDATA\BACKUPTEST_BAK_log.ldf', NOUNLOAD, STATS = 5

, NORECOVERY

 

GO

RESTORE LOG [BACKUPTEST_BAK] FROM DISK = N'E:\LAB\D___TRN1.TRN' WITH FILE = 1, NOUNLOAD, STATS = 10 , NORECOVERY

RESTORE LOG [BACKUPTEST_BAK] FROM DISK = N'E:\LAB\D___TRN2.TRN' WITH FILE = 1, NOUNLOAD, STATS = 10 , RECOVERY

 

use BACKUPTEST_BAK

go

select * from tbl

백업전

카피백업전

로그백업전1

로그백업전2

 

 

-- LSN (Log Sequence Number) 확인

restore headeronly from DISK='E:\LAB\D___FUL1.BAK'

restore headeronly from DISK='E:\LAB\D__COPY_FUL1.BAK'

restore headeronly from DISK='E:\LAB\D___TRN1.TRN'

restore headeronly from DISK='E:\LAB\D___TRN2.TRN'

restore headeronly from DISK='E:\LAB\D___TRN3.TRN'