[스크랩] 트랜잭션 로그 및 LSN을 이용한 삭제된 데이터 복구

http://www.sqler.com/bColumn/873012

트랜잭션 로그 및 LSN을 이용한 삭제된 데이터 복구

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server에서 일어나는 모든 일은 트랜잭션 로그에 기록 된다. 지난 시간에 트랜잭션 로그를 활용하여 누가 어떤 명령을 실행하였는지 찾는 방법에 대해서 알아 보았다.

• SQL Server에서 Drop 및 Delete 사용자 찾기 : http://sqlmvp.kr/140202164558

 

이번 시간에는 트랜잭션 로그 및 LSN을 사용하여 삭제된 데이터를 복구하는 방법에 대해서 알아 본다. SQL Server 트랜잭션의 모든 레코드에는 LSN(로그 시퀀스 번호)으로 고유하게 식별된다. LSN은 numeric(25.0)의 값을 가지며 변경이 발생한 순서에 따라 번호가 매겨진다. 중요 이벤트가 발생한 로그 레코드의 LSN은 올바른 복원 시퀀스를 생성하는데 도움을 될 수 있다.

 

LSN은 <, >, =, <=, >= 과 같은 비교가 가능하며 RESTORE 순서 중에 내부적으로 사용되어 데이터가 복원될 지정 시간을 추적한다. 백업을 복구할 때 데이터는 백업이 이루어진 지정 시간에 해당하는 LSN으로 복원된다. 차등 및 로그 백업의 경우 데이터베이스 보다 나중의 것으로 복원되며 이는 더 높은 LSN에 해당한다. 자세한 내용은 다음 링크를 참고 한다.

• 로그 시퀀스 번호 소개 :

http://technet.microsoft.com/ko-kr/library/ms190411%28v=sql.105%29.aspx

 

 

[삭제 된 데이터 복원]

테이블에서 데이터가 삭제된 것을 복원하는 방법에 대해서 알아 본다. 실습용 데이터베이스와 테이블을 생성한다.

--Create DB. USE [master]; GO CREATE DATABASE ReadingDBLog; GO   -- Create tables. USE ReadingDBLog; GO CREATE TABLE [Location] ( [Sr.No] INT IDENTITY, [Date] DATETIME DEFAULT GETDATE (), [City] CHAR (25) DEFAULT 'Seoul');

 

 

생성된 테이블에 데이터를 입력 한다.

USE ReadingDBLog go INSERT INTO Location DEFAULT VALUES ; GO 100

 

 

일부 데이터를 삭제 한다. 10 이하의 행이 삭제 된 것을 확인 할 수 있다.

USE ReadingDBLog Go DELETE Location WHERE [Sr.No] < 10 go select * from Location

 

 

 

트랜잭션 로그에서 삭제 된 행에 대한 정보를 얻기 위해 다음의 스크립트를 실행 한다. Delete 구문이 실행된 트랜잭션 ID를 확인 할 수 있으며 AllocUnitName 열에서 테이블의 이름을 확인 할 수 있다. 트랜잭션ID가 동일하게 사용된 것은 삭제 된 행이 일괄적으로 수행 되었다는 것을 뜻한다.

use ReadingDBLog go   SELECT     [Current LSN],     [Transaction ID],     Operation,     Context,     AllocUnitName FROM fn_dblog(NULL, NULL) WHERE Operation = 'LOP_DELETE_ROWS'

 

 

 

다음 명령은 트랜잭션ID를 사용하여 LOP_BEGIN_XACT 작업의 LSN을 확인 할 수 있다. 또한 작업이 시작된 시간을 확인 할 수 있다.

USE ReadingDBLog go SELECT     [Current LSN],     Operation,     [Transaction ID],     [Begin Time],     [Transaction Name],     [Transaction SID] FROM fn_dblog(NULL, NULL) WHERE [Transaction ID] = '0000:000003c6'     AND [Operation] = 'LOP_BEGIN_XACT'

 

 

 

다음은 데이터를 복구하기 위해 16진수의 LSN 값을 변경하는 것이다. 데이터 복구를 위해 STOPBEFORREMARK 작업을 사용한다. STOPBEFOREMARK 작업을 실행하려면 16진수 값을 사용한다.

 

LSN의 값 00000025:00000160:0001에서 [:] 부호를 기준으로 3개의 파트로 나누어서 변환 한다.

Part	Hex Value	Decimal Value
A	00000025	37
B	00000160	352
C	0001	1

 

데이터 변환을 위해서 아래 링크를 참고하면 매우 편하게 값을 확인 할 수 있다.

• Hex to Decimal Convert 변환기 : http://www.binaryhexconverter.com/hex-to-decimal-converter

 

 

A파트의 10진수는 그대로 사용하고 B파트의 경우 10자리의 자릿수를 사용. C파트의 경우 5자릿수를 사용한다. 따라서 다음과 같이 LSN 조합이 생성된다.

37000000035200001

 

마지막으로 트랜잭션 백업을 실행하고 새로운 서버에 전체 백업 및 로그 백업을 복원한다.

backup log ReadingDBLog to disk = 'C:\SQL_Backup\ReadingDB_Log.trn'     --Restoring Full backup with norecovery. RESTORE DATABASE ReadingDBLog_COPY FROM DISK = 'C:\SQL_Backup\ReadingDBLog.bak' WITH REPLACE, NORECOVERY, MOVE 'ReadingDBlog' TO 'C:\SQL_Data\ReadingDBLog.mdf', MOVE 'ReadingDBlog_log' TO 'C:\SQL_Data\ReadingDBLog_log.ldf' GO   --Restore Log backup with STOPBEFOREMARK option to recover exact LSN. RESTORE LOG ReadingDBLog_COPY FROM DISK = N'C:\SQL_Backup\ReadingDB_Log.trn' WITH STOPBEFOREMARK = 'lsn:37000000035200001'

 

복원이 완료되면 다음과 같이 데이터를 조회한다. 삭제된 데이터가 트랜잭션로그 백업에서 복구 된 것을 확인 할 수 있다.

USE ReadingDBLog_COPY GO SELECT * from Location

 

 

 

[삭제된 테이블 복원]

테이블이 삭제된 경우 복원하는 방법은 위의 데이터 복원과 동일한다. 실습을 위해 테이블을 삭제 한다.

USE ReadingDBLog GO DROP TABLE Location

 

다음 스크립트를 사용하여 트랜잭션 로그에서 삭제된 테이블을 항목을 확인 할 수 있다.

USE ReadingDBLog GO SELECT     [Current LSN],     Operation,     [Transaction Id],     [Transaction SID],     [Transaction Name],     [Begin Time],     [SPID],     Description FROM fn_dblog (NULL, NULL) WHERE [Transaction Name] = 'DROPOBJ' GO

 

삭제 된 정보에서 시작 트랜잭션의 LSN을 확인 할 수 있다. LSN을 사용하여 데이터 복구와 동일한 프로세스를 따라 복원할 수 있다.

 

 

[참고자료]

• Recover deleted SQL Server data and tables with the help of Transaction Log and LSNs : http://www.mssqltips.com/sqlservertip/3160/recover-deleted-sql-server-data-and-tables-with-the-help-of-transaction-log-and-lsns/
• SQL Server에서 Drop 및 Delete 사용자 찾기 : http://sqlmvp.kr/140202164558
• 로그 시퀀스 번호 소개 :

http://technet.microsoft.com/ko-kr/library/ms190411%28v=sql.105%29.aspx

 

강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp