SQL Server의 mdf, ldf 장애 발생 시나리오 정리
SQL Server의 mdf, ldf 파일에서 장애 발생시 대처할 수 있는 시나리오를 테스트 해 봤습니다.
- mdf 파일 장애 발생
전체 복구모델을 사용하고 5분 주기로 트랜잭션 로그 백업을 수행하고 있다고 가정하겠습니다.
mdf 파일에 장애 발생시 마지막 트랜잭션 로그 백업 이후 수행된 트랜잭션에 대한 로그백업을 수행하여 최대한 데이터를 살려내야 하며 이를 비상 로그 백업이라 합니다.
비상 로그 백업을 위해서는 다음과 같이 BACKUP LOG 명령에 NO_TRUNCATE 또는 CONTINUE_AFTER_ERROR 옵션을 붙여서 수행하면 됩니다.
BACKUP LOG CrashDB to disk = 'D:\ CrashDB.trn' with NO_TRUNCATE
BACKUP LOG CrashDB to disk = 'D:\ CrashDB.trn' with CONTINUE_AFTER_ERROR
이후 RESTORE 명령으로 (전체 백업 파일 + 트랜잭션 로그 백업 파일 + 비상 로그 백업 파일)을 이용하여 최대한 데이터를 복구해 낼 수 있습니다. - ldf 파일 장애 발생
ldf 파일 장애가 발생할 경우 트랜잭션 로그 백업을 할 수 없기 때문에 마지막 트랜잭션 로그 백업 이후에 수행된 트랜잭션에 대한 유실이 발생하게 됩니다.
이때 mdf 파일은 데이터 정합성이 맞지 않는 상태로 DB Attach 하여 유실된 데이터를 확인하는데 참고하는 정도로 사용할 수 있습니다.
결국 mdf파일의 장애는 복구 가능하지만 ldf파일의 장애 발생시 일부 데이터 유실이 발생하게 됩니다.
== 테스트 환경
select @@version
Microsoft SQL Server 2014 - 12.0.4422.0 (X64)
Jul 27 2015 16:56:19
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
== mdf 장애 발생
CREATE DATABASE CrashDB ON PRIMARY ( NAME = N'CrashDB', FILENAME = N'D:\temp\CrashDB.mdf' , SIZE = 10MB) LOG ON ( NAME = N'CrashDB_log', FILENAME = N'D:\temp\CrashDB_log.ldf' , SIZE = 10MB) GO
use CrashDB go create table t1 (col1 varchar(1000)) BACKUP DATABASE CrashDB to disk = 'd:\temp\CrashDB_1.bak' insert into t1 select 'step 1' backup log CrashDB to disk = 'd:\temp\CrashDB_Log_1.trn' insert into t1 select 'step 2'
use master go alter database CrashDB set offline
-- delete mdf file 수행(장애 발생했다고 가정 함)
alter database CrashDB set online
-- NO_TRUNCATE, CONTINUE_AFTER_ERROR 사용하여 백업 수행 backup log CrashDB to disk = 'd:\temp\CrashDB_Log_2.trn' with no_truncate --backup log CrashDB to disk = 'd:\temp\CrashDB_Log_2.trn' with CONTINUE_AFTER_ERROR
RESTORE DATABASE CrashDB_Restore from disk = 'd:\temp\CrashDB_1.bak' with move 'CrashDB' to 'D:\temp\CrashDB_restore.mdf' , move 'CrashDB_log' to 'D:\temp\CrashDB_restore_log.ldf' , norecovery
RESTORE LOG CrashDB_Restore from disk = 'd:\temp\CrashDB_Log_1.trn' with norecovery RESTORE LOG CrashDB_Restore from disk = 'd:\temp\CrashDB_Log_2.trn' with norecovery RESTORE DATABASE CrashDB_Restore with recovery |
