MS-SQL 데이터 특정시점으로 복원하기

출처 : http://www.sqlworld.pe.kr

데이터베이스를 사용하다보면 잘못된 트랙젠션으로 인하여 그 이전의 상태로 복구해야 하는 필요성을 느끼게 되는 경우가 있습니다. 예를 들어 잘못해서 테이블을 전부 지워버린 경우 그 이전 상태로 복구하거나 아니면 수작업으로 입력해야 합니다.

로그 백업을 이용하여 특정 시점으로 데이터베이스를 복구하는 방법을 살펴보도록 하겠습니다.


가끔 이런 질문을 받습니다.

"큰일 났어요. 잘못해서 테이블을 다 지워버렸거든요. 이거 복구하지 못하면 저 짤려요. 제발 살려주세요.."

저도 위와 같은 경우를 경험한 적이 있었습니다. 분명히 SELECT를 하려는 생각으로 쿼리문을 날렸는데 아뿔사, 제가 수행한 쿼리문은 SELECT 문이 아니고 DELETE 문이었습니다. 참으로 황당하더군요.

SQL 서버에서는 트랜잭션 로그 백업을 이용하여 특정 시점으로 데이터베이스를 복구하는 방법이 있습니다. 위와 같이 테이블을 전부 지워버렸으면 지우기 바로 이전 상태로 복구하면 모든 문제는 해결이 됩니다. 물론 이와 같은 복구 작업이 항상 가능한 것은 아닙니다. 다음과 같은 조건이 만족된 상태여야 합니다.

① 데이터베이스 백업모델이 "최대" 여야 합니다.
② 전체 백업을 받은 상태여야 합니다.
③ 이전의 로그백업이 있다면 안전하게 보관된 상태여야 합니다.

지금 부터의 복구 예제는 다음과 같은 순서로 이루어 집니다.

o 예제 테이블 만들기
o 테이블에 5개의 레코드 추가
o 데이터베이스 전체 백업
o 테이블에 5개의 레코드 추가
o 데이터베이스 로그 백업
o 테이블에 5개의 레코드 추가
o 테이블 삭제 (문제발생)
o 현 시점의 로그 백업
o 전체백업 Restore
o 기존 로그 백업 Restore
o 문제 발생후 백업 받은 로그 Restore(테이블을 지우기 전까지만)

단, 예제에 사용되는 sqlworld 데이터베이스의 복구모델은 "최대" 입니다.

1) 데이터베이스 전체 백업

우선 Test1 이라는 예제 테이블을 만들고 레코드 5개를 추가하도록 하겠습니다.


CREATE TABLE Test1
(
col1 int,
col2 char(05)
)
GO
INSERT INTO Test1 VALUES(1,'AAAAA')
INSERT INTO Test1 VALUES(2,'BBBBB')
INSERT INTO Test1 VALUES(3,'CCCCC')
INSERT INTO Test1 VALUES(4,'DDDDD')
INSERT INTO Test1 VALUES(5,'EEEEE')


이제 지금 상태의 sqlworld 데이터베이스를 전체 백업 받도록 하겠습니다.


BACKUP DATABASE sqlworld TO DISK = 'D:\sqlworld.bak'

위 전체 백업으로 인해 우리는 어떤 사태가 발생하더라도 Test1 테이블에 5개의 레코드가 추가된 상태로 복구를 할 수 있습니다.(당연하죠)

2) 로그백업

이제Test1 테이블에 레코드를 5개 더 추가하도록 하겠습니다.


INSERT INTO Test1 VALUES(6,'FFFFF')
INSERT INTO Test1 VALUES(7,'GGGGG')
INSERT INTO Test1 VALUES(8,'HHHHH')
INSERT INTO Test1 VALUES(9,'IIIII')
INSERT INTO Test1 VALUES(10,'JJJJJ')

앞에서 전체 백업을 받은 상태이므로 이제는 방금 작업한(5개의 레코드 추가) 트랜잭션만을 보관하도록 로그 백업을 받도록 하겠습니다. (물론 지금 같은 경우는 예제이므로 데이터량이 많지 않아 전체 백업을 다시 받아도 되지만 실무에서는 전체 데이터베이스 양이 엄청 큰 경우라면 로그 백업을 받는게 더 효율적일 수 있습니다.)


BACKUP LOG sqlworld TO DIsk = 'D:\sqlworld_log.bak'

만일 이 상태에서 데이터베이스에 문제가 발생하여 복구를 해야 하는 경우는 처음에 받은 전체 백업을 Restore 한 후에 뒤에 받은 로그 백업을 Restore 하면 됩니다. 여기 까지는 누구나 다 아는 사실입니다.

3) 사태발생

이제Test1 테이블에 레코드를 5개 더 추가하도록 하겠습니다.


INSERT INTO Test1 VALUES(11,'KKKKK')
INSERT INTO Test1 VALUES(12,'LLLLL')
INSERT INTO Test1 VALUES(13,'MMMMM')
INSERT INTO Test1 VALUES(14,'NNNNN')
INSERT INTO Test1 VALUES(15,'OOOOO')

자! 지금까지는 일반적인 작업이 순조롭게 진행이 되고 있는 상태임을 볼 수 있습니다.

그런데 제가 잘못해서 이 상태에서 다음과 같은 퀴리문을 이용하여 Test1 테이블을 전부 삭제 해 버렸습니다. 이 때의 시간이 2001-11-19 1:10AM 입니다.


DELETE FROM Test1

큰일 났습니다!
엄청난(?) 양의 Test1 테이블이 전부 지워져 버렸습니다!

4) 복구하기

이제 해야 할 일은 Test1 테이블을 지우기전 즉 15개의 레코드가 존재하는 상태로 복구하는 것입니다. 다음의 순서를 정확히 기억하시기 바랍니다.

1) 현재 시점의 로그를 NO_TRUNCATE 옵션을 이용하여 백업받기

다음과 같이 NO_TRUNCATE 옵션을 이용하여 현재 상태의 로그를 백업 받습니다.(실무에서는 "모두 작업을 멈추시오!" 라고 외치고 백업 받아야 합니다)


BACKUP LOG sqlworld TO DISK = 'D:\Check.bak' WITH NO_TRUNCATE

2) 전체 백업 Restore

우선 제일 먼저 백업 받은 전체 백업을 Restore해야 합니다. 단 계속해서 추가적인 Restore 작업이 수행되어야 하므로 WITH NORECOVERY 옵션을 이용해야 합니다.


RESTORE DATABASE sqlworld FROM DISK = 'D:\sqlworld.bak' WITH NORECOVERY

이제 처음 레코드 5개가 추가된 상태로는 복구가 되었습니다. 하지만 WITH NORECOVERY를 사용했으므로 아직은 접근할 수 없는 상태입니다.

3) 로그 백업 Restore

전체 백업 후에 5개의 레코드를 추가하고 백업받은 로그가 있습니다. 이것을 WITH NORECOVERY 옵션으로 계속해서 Restore 해야 합니다.


RESTORE LOG sqlworld FROM DISK = 'D:\sqlworld_log.bak' WITH NORECOVERY

이제 처음 레코드 5개를 포함해서 새로 추가된 5개의 레코드 까지는 복구가 된 셈입니다. 하지만 WITH NORECOVERY를 사용했으므로 여전히 접근할 수 없는 상태입니다

4) STOPAT을 이용한 특정 시점까지의 로그 백업 Restore

문제 발생후 백업받은 로그 안에는 새롭게 추가된 5개의 레코드에 대한 작업과 함께 테이블 전체를 지운 작업이 포함되어 있습니다. 여기서 우리는 테이블 전체를 지운 작업 바로 이전까지만 Restore를 하면 됩니다. 이때 사용되는 것이 STOPAT 이라는 옵션입니다.

이 옵션을 이용하여 다음과 같이 테이블 전체를 지웠던 2001-11-19 1:10AM 이전 까지만 Restore 하면 됩니다. 그래서 2001-11-19 01:09 까지만 Restore 하겠습니다.


RESTORE LOG sqlworld FROM DISK = 'D:\Check.bak'
WITH RECOVERY, STOPAT = '2001-11-19 01:09'

그리고 WITH RECOVERY 옵션을 사용한 이유는 Restore가 다 끝났으므로 사용자가 접근 할 수 있게 하기 위함 입니다.

이렇게 해서 우리는 완벽하게 테이블이 지워지기 바로 전까지를 복구 할 수 있었습니다.

5) 정리

위 내용을 직접 수행해서 결과를 확인해보시기 바랍니다. 그렇지 않고 실제 문제가 발생한 경우에 복구 작업을 하려고 하면 안절부절 하게되기 때문입니다. 많은 분들이 백업은 받을 줄 아는데 정작 중요한 복구는 못하는 경우가 있습니다.

물론 복구할 일이 없는데 제일 좋지만 만일의 경우에 대비해서 연습해 두는 것이 좋습니다. 그리고 백업받은 데이터는 안전하게 보관을 하고 있어야 합니다. 아무리 복구 방법을 잘 안다고 해도 백업받은 데이터가 손상이 된 상태라면 아무 의미가 없기 때문입니다.