로그 백업 및 복구

시나리오

1. 복구 모델 확인

SELECT [name], [recovery_model_desc] FROM sys.databases WHERE [name] = DB_NAME()

2. 단순 복구 모델이면?

BACKUP LOG pubs TO DISK = 'C:\SQLBackup\Pubs.bak' WITH INIT go

로그 백업은 전체(FULL) 복구 모델이여야 백업을 할 수 있습니다.

현재 복구 모델이 단순(SIMPLE)이므로 복구 모델을 변경하라는 메시지를 볼 수 있습니다.

3. 복구 모델 변경(FULL) 및 확인

--복구 모델 변경(full) ALTER DATABASE pubs SET RECOVERY full go   -- 복구 모델 확인SELECT [name], [recovery_model_desc] SELECT [name], [recovery_model_desc] FROM sys.databases WHERE [name] = DB_NAME()

4. 전체 백업

BACKUP DATABASE [pubs] TO  DISK = 'C:\SQLBackup\Pubs.bak' go

로그 백업 전에 전체 백업을 받지 않으면 로그 백업을 받을 수 없습니다.

별도로 테스트 해보시기 바랍니다.

5. 데이터 변경 후 로그 백업(1)

-- 데이터 변경(1) use pubs update titles set price = price + 10000 where title_id = 'PC1035' or title_id = 'PS1372' GO   -- 로그 백업(1) BACKUP LOG [pubs] TO  DISK = 'C:\SQLBackup\Pubs.bak' GO

6. 데이터 변경 후 로그 백업(2)

-- 데이터 변경(2) Use pubs update titles set price = price + 10000 where title_id = 'PC1035' or title_id = 'PS1372' GO   -- 로그 백업(2) BACKUP LOG [pubs] TO  DISK = 'C:\SQLBackup\Pubs.bak' GO

7. 데이터 변경 후 로그 백업(3)

-- 데이터 변경(3) use pubs update titles set price = price + 10000 where title_id = 'PC1035' or title_id = 'PS1372' GO   -- 로그 백업(3) BACKUP LOG [pubs] TO  DISK = 'C:\SQLBackup\Pubs.bak' GO

8. 잘못된 데이터 업데이트 발생 !

update titles set price = 99999 GO   -- 현재 데이터 확인 select title, price from titles where price is not null order by price desc, title asc

9. 복원 데이터 확인

RESTORE  HEADERONLY  FROM  DISK = 'C:\SQLBackup\Pubs.bak' go

전체 백업 1번, 로그 백업 3번을 받은 상태입니다.

BackupType이 1은 전체 백업, 2는 로그 백업을 뜻합니다.

Position에 따라 원하는 시점으로 복구가 가능합니다.

10. 로그 백업 복원 실패 예제

RESTORE  DATABASE pubs        FROM  DISK = 'C:\SQLBackup\Pubs.bak'        WITH FILE= 1, REPLACE, norecovery RESTORE  LOG pubs  FROM  DISK = 'C:\SQLBackup\Pubs.bak' WITH FILE= 4 GO

전체 백업 복구는 성공했지만, 로그 백업은 실패했습니다.

그 이유는 첫 번째, 두 번째 로그 백업을 복구 하지않고 곧바로 세 번째 로그 백업 복구를 했기 때문입니다.

이럴 경우 데이터베이스에 적용하기에는 너무 이른 로그라는 메시지를 볼 수 있습니다.

로그 백업은 중간에 어느 하나라도 사라지면 안됩니다.

백업 받은 순서대로 복원해야 원하는 시점의 데이터로 복구가 가능합니다.

11. 로그 백업 복원 성공 예제

RESTORE  DATABASE pubs        FROM  DISK = 'C:\SQLBackup\Pubs.bak'        WITH FILE= 1, REPLACE, norecovery RESTORE  LOG pubs  FROM  DISK = 'C:\SQLBackup\Pubs.bak' WITH FILE= 2 , REPLACE, norecovery RESTORE  LOG pubs  FROM  DISK = 'C:\SQLBackup\Pubs.bak' WITH FILE= 3 , REPLACE, norecovery RESTORE  LOG pubs  FROM  DISK = 'C:\SQLBackup\Pubs.bak' WITH FILE= 4 GO

12. 복원된 데이터 확인

-- 현재 데이터 확인 Use pubs select title, price from titles where price is not null order by price desc, title asc