데이터 손실 시 복구

업무 중에 DBA는 많은 장애에 직면합니다. 

그중에 데이터의 손실은 아주 큰 장애라고 생각됩니다. 제가 겪은 대부분의 데이터 손실은 인재였으며, 다음과 같은 예제 시나리오를 통해서 복원하는 방법을 정리해봤습니다.

현재 상황

- FULL BACKUP 매일 하루 새벽에 진행되고 있음

- TRAN LOG BACKUP 1시간에 1번씩 진행되고 있음

장애 상황

- 실수로 테이블에 WHERE 조건을 잊고, 그대로 실행하여 모든 데이터가 손실된 상황

위와 같은 상황은 은근히(?) 많이 발생합니다. 그 은근히(?) 많은 상황을 대비해서 테스트를 진행해봤습니다.

순서는 다음과 같이 진행 됩니다.

① 유저가 5건의 Insert 를 통해 데이터를 넣음

② FULL BACKUP 진행 (하루 1회 도는 백업으로 간주)

③ 유저가 5건의 Insert 를 통해 데이터를 다시 넣음

④ TRAN LOG BACKUP 진행 (1시간에 1번씩 도는 백업으로 간주)

⑤ 유저가 5건의 Insert 를 통해 데이터를 다시 넣음

⑥ 유저가 실수로 모든 데이터를 Delete 시킴

Step 1 - 유저가 5건의 insert를 통해 데이터를 넣음

최초에 5건의 데이터를 넣습니다.

use Restore_DB
go

create table RE_Tbl
(
 col1 int
,col2 varchar(10)
)
go

insert into RE_Tbl values (1,'주형권1')
insert into RE_Tbl values (2,'주형권2')
insert into RE_Tbl values (3,'주형권3')
insert into RE_Tbl values (4,'주형권4')
insert into RE_Tbl values (5,'주형권5')
go

Step 2 - FULL BACKUP 진행

backup database [Restore_DB] to disk  = 'D:\MSSQL_BACKUP\Restore_DB.bak'

Step 3 - 유저가 5건의 Insert 를 통해 데이터를 다시 넣음

insert into RE_Tbl values (6,'주형권6')
insert into RE_Tbl values (7,'주형권7')
insert into RE_Tbl values (8,'주형권8')
insert into RE_Tbl values (9,'주형권9')
insert into RE_Tbl values (10,'주형권10')

Step 4 -  TRAN LOG BACKUP 진행

backup log  [Restore_DB] to disk  = 'D:\MSSQL_BACKUP\Restore_Tran_1.trn'

여기까지의 데이터를 확인해 보면 다음과 같습니다.

Step 5 - 유저가 5건의 Insert 를 통해 데이터를 다시 넣음

insert into RE_Tbl values (11,'주형권11')
insert into RE_Tbl values (12,'주형권12')
insert into RE_Tbl values (13,'주형권13')
insert into RE_Tbl values (14,'주형권14')
insert into RE_Tbl values (15,'주형권15')

Delete 하기 직전의 데이터를 다음과 같습니다.

마지막으로 모든 데이터를 손실 시킵니다.

delete RE_Tbl
go

이제 데이터는 존재하지 않습니다. 그리고, Tran 백업이 정상적으로 돌았다고 가정하고, 마지막으로 트랜잭션 백업을 한번 더 해줍니다.

backup log  [Restore_DB] to disk  = 'D:\MSSQL_BACKUP\Restore_Tran_2.trn'

이제 복원을 시켜보도록 하겠습니다.

Step 6 - 복원준비 

한가지 궁금한 점이 생겼습니다. 2번째 Tran Log 백업은 Delete 이후에 되었으로, 마지막 트랜잭션까지 모두 저장되었습니다. 그런데 2번째 트랜잭션 로그 백업을 이용해 복원하면

당연히 모든 데이터를 없는 상태가 맞지 않을까요? 

그래서 Restore에 옵션이 존재합니다. STOPAT 라는 옵션입니다. 이 옵션을 통해서 지우기 직전까지의 데이터로 복원이 가능 합니다.

복원을 하기 전에 모든 데이터에 변동이 없도록 모두에게 작업을 중지 할 것을 요청합니다. 만약 Live 중이라면, 점검을 걸고 유저의 유입을 막아야 하겠지요.

Step 7 - NORECOVERY 모드로 복원

데이터베이스를 NORECOVERY 모드로 FULL BACKUP을 복원 합니다.

restore database  Restore_Test from disk =  'D:\MSSQL_BACKUP\Restore_DB.bak'
with norecovery
,move 'Restore_DB' to 'D:\MSSQL_DATA\Restore_DB_1.mdf'
,move 'Restore_DB_log' to 'D:\MSSQL_LOG\Restore_DB_log_1.ldf'

NORECOVERY 모르도 복원하면 옆에 복원 중이라는 메시지가 표시됩니다.

현재 이 데이터베이스(Restore_Test)는 복원 중이므로, 엑세스가 되지 않습니다. 만약 엑세스를 시도하면 다음과 같은 메시지가 나옵니다.

두번째로, 첫번째 Tran Log BACKUP을 NORECOVERY 모드로 복원 합니다.

restore database  Restore_Test from disk = 'D:\MSSQL_BACKUP\Restore_Tran_1.trn'
with norecovery
,move 'Restore_DB' to 'D:\MSSQL_DATA\Restore_DB_1.mdf'
,move 'Restore_DB_log' to 'D:\MSSQL_LOG\Restore_DB_log_1.ldf'

여전히 데이터베이스는 복원중으로 표시됩니다. 아직 전체를 복원 한것이 아니므로, 계속해서 TRAN LOG BACKUP을 붙여야 합니다.

세번째로 두번째 Tran Log BACKUP을 RECOVERY 모드로 복원합니다. 이때 위에서 언급했던 STOPAT 옵션이 중요합니다. Delete 하였던 시점의 바로 직전의 시간을 넣어서

거기까지만 복원 되도록 해야합니다. 아까 제가 Delete 수행했을 시간은 2016.01.18 14:50 이였습니다. 그러므로, 2016.01.19 14:49까지의 데이터로 돌리겠습니다.

restore database  Restore_Test from disk ='D:\MSSQL_BACKUP\Restore_Tran_2.trn'
with recovery, STOPAT = '2016-01-18 14:48' 
,move 'Restore_DB' to 'D:\MSSQL_DATA\Restore_DB_1.mdf'
,move 'Restore_DB_log' to 'D:\MSSQL_LOG\Restore_DB_log_1.ldf'

복원한 데이터베이스에서 데이터를 확인 합니다.

확인 한 데이터베이스에서 기존의 데이터베이스로 데이터를 INSERT INTO 시켜주면 데이터의 손실없이 복원이 완료됩니다.

감사합니다.