야심한 밤에 전화가 왔습니다. 원래 모르는 전화번호는 잘 안받는 편인데 혹시나싶어 받았더니 모 고객사를 지원하는 이대리님이었습니다. 아주 다급한 목소리로 “개발자가 SQL Server에서 2만건 정도되는 데이터를업데이트했는데, 잘못 업데이트 해서 롤백해야 한다는데 가능한가요 개발자말로는 'auto commit'된 상황이랍니다. 데이터를 살릴 수 있는 방법이 없을까요 ㅜ.ㅜ”

이런 경우 과연 Rollback이 가능할까요^^;;

여러분은 답을 다 아시죠

전화주신 대리님도 모르시는 것은 아닐텐데 장애상황이라 너무 다급한 나머지 commit과 rollback, 복구의 개념이 뒤죽박죽되고, 지푸라기라도 잡고싶은 심정으로 SQL Server는 오라클과 달리 뭔가 다른 방법이 있지 않을까 하고 저한테 도움을 요청하신 것이었지요.

'명시적이든 암시적이든 일단 commit이 되면 rollback이 안되는 것이고 그때는 데이터 ‘Recovery’가 필요합니다~' 가 정답입니다.

그렇다면 이처럼 잘못 업데이트한 데이터를 recovery할 수 있는 방법이 뭐가 있을까요

Full 복구가 아닌 특정 데이터를 복구하는 방법

첫 번째는 DB단위 백업본과 트랜잭션 로그를 이용한 복구입니다.

일반적으로 DB운영자들은 이 경우 전체 DB를 복구해야해서 다운타임이 발생한다고 생각하거나 특정 데이터 복구는 안된다고 생각하는데요. 꼭 그렇지만은 않습니다.

기존DB는 놔두고 다른 새로운 DB로 복구한 후 그 DB에서 원하는 데이터만 뽑아오는 방법을 사용하면 됩니다. (물론 이렇게 해도 데이터 일관성 문제가 없다는 가정하에 가능하겠죠.)

[복구절차]

1) 복구대상 DB의 현재의 트랜잭션 로그를 ‘Backup log.. no_truncate' 옵션을 사용해서 백업합니다.

2) 복구대상 DB의 마지막 Full백업과 그동안 받은 Differential backup, transaction log backup을 norecovery옵션으로 다른 이름으로 순서대로 복구하시고,

3) 1)에서 백업받은 트랜잭션 로그백업을 stopat=’복구시점’ 옵션을 써서 복구합니다.

4) 이제 복구한 DB의 테이블에서 필요한 데이터만 뽑아 운영DB의 잘못 업데이트된 테이블에 넣으면 됩니다.

여기까지 읽으신 분 중에 “백업을 이용한 복구는 너무 번거로워요~다른 방법은 없나요”라고 질문이 있을 수 있겠네요…그런 분들을 위해 한가지 방법을 더 알려드리면,,,,

Tool을 사용하여 트랜잭션 로그를 읽어 복구하는 방법

두번째는 3^rd party 복구 솔루션을 사용하는 방법입니다. 시중에 나와있는 SQL Server Tool중에는 트랜잭션 로그를 읽어서 특정 쿼리에 대한 redo, undo query를 생성해주는 SW가 있습니다. 이 SW를 이용하면 Commit 이후에도 개별 트랜잭션에 대한 undo query를 실행함으로써 데이터 복구가 가능합니다.

제가 사용해본 SW는 Q사에서 나온 SQL Server management solution의 log reader란 Tool이었는데요. ‘undo or redo transactions’란 기능이 제공되어 이 기능을 사용하면 개별 트랜잭션에 대한 undo 쿼리를 생성해주므로 롤백이 가능합니다. 단, 트랜잭션 로그를 읽어서 쿼리를 생성하는 구조이므로 복구하고자 하는 DB의 Recoverymode가 반드시 Full이어야 합니다.

또한 Truncate, drop으로 데이터를 일괄삭제하거나 테이블을 삭제한 경우는 복구가 불가능합니다. 이경우 SW가 후져서(^^;;) 그런 것이 아니고 truncate, drop 명령은 SQL Server가 트랜잭션 로그에데이터의 before값을 기록하지 않기 때문에 그런것이니까 SW를 원망하지 마세요~~. (혹시나해서 복구 스크립트를 뽑아보면 실제로 데이터는 제외한 테이블 껍데기를 생성하는 쿼리만 나옵니다. ^^;;)

그러나, 모든 일은 사후처리보다는사고가 나지 않도록 예방하는 것이 중요하니아래 내용을참고하셔서작업실수로 인한 DB장애를 예방하고, 장애시 빨리 복구하기 위한 조치들을 미리미리 해두시는 것이 좋겠습니다.

작업실수로 인한 DB장애를 예방하고, 장애시빨리복구하기위해서는…

[프로세스 관점]

1. DBMS에서 오브젝트 DDTP(Delete, Drop, Truncate, Purge) 작업을 할 때는 반드시 백업본(Full backup, object rename 등) 확보 등 Backoff Plan 작성을 해야하고,

2. DDPT 작업으로인한 장애발생시 그 피해를 최소화하기 위하여 반드시 해당시스템의 업무시간 종료후 처리해야합니다.

[기술적 관점]

1. SQL Server는 default 옵션이 auto commit이니 Delete, update 작업을 할때는 반드시 begin tran, commit/rollback을 사용하여 명시적 트랜잭션으로 처리되도록 해야합니다.

2. ReadOnly성격의 DB가 아니라면 DB의 Recovery mode는 항상 Full로 셋팅하시고, 수시로 Transaction log backup을 받으셔야 만약의 경우 복구가 가능합니다.

3. 중요한 DB이고 작업자의 delete/update 실수가 빈번히 발생한다면 해당 DB에 대해서는 주기적으로 snapshot backup을 고려하는 것도 방법입니다. Snapshot 활용에 대해서는 나중에 기회가 되면 좀 더 자세하게 다루겠습니다.

여하튼 모든 일은사고가 나지 않도록하는 것이 제일 중요하고,사고 후에는 빠른해결이 최선입니다.....^^