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

 

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

 

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

 

이번 시간에는 트랜잭션 로그 및 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

 

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

 

 

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을 사용하여 데이터 복구와 동일한 프로세스를 따라 복원할 수 있다.

 

 

[참고자료]

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

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 38622
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 20785
1934 확장이벤트를 사용하여 데드락 정보 확인 jevida(강성욱) 2016.10.15 1663
1933 확장 이벤트를 사용한 CPU 고부하 쿼리 추적 [1] jevida(강성욱) 2016.10.15 2226
1932 데이터에 대한 이해와 spill in tempdb jevida(강성욱) 2016.10.13 1824
1931 로그 파일이 많으면 왜 안 좋은가 jevida(강성욱) 2016.10.13 2120
1930 트랜잭션 백업 실패와 전체 백업 성공 그리고 대처 방안 jevida(강성욱) 2016.10.13 1618
1929 Fast recovery 와 로그 잠금 jevida(강성욱) 2016.10.13 2079
1928 고스트 클린업 jevida(강성욱) 2016.10.13 2352
1927 페이지 분할이 발생 하였을 때 롤백을 하면 어떻게 될까? jevida(강성욱) 2016.10.13 1711
1926 DBCC WRITEPAGE - DBCC 명령을 사용한 데이터 파괴하기 jevida(강성욱) 2016.10.13 1703
1925 SQL Server Backup Error 3023 jevida(강성욱) 2016.10.13 2450
1924 Delete 작업과 페이지 offset 변화 jevida(강성욱) 2016.10.13 1550
» 트랜잭션 로그 및 LSN을 이용한 삭제된 데이터 복구 jevida(강성욱) 2016.10.13 5846
1922 PFX 형식의 인증서를 SQL Server에서 사용하기 jevida(강성욱) 2016.10.13 1212
1921 SQL Server NUMA 메모리 노드와 Operating System 접근 jevida(강성욱) 2016.10.13 1369
1920 SQL Server 에러 핸들링 비용 비교 jevida(강성욱) 2016.10.13 1357
1919 압축 백업 시 Checksum 옵션으로 손상 확인하기 jevida(강성욱) 2016.10.13 1756
1918 SQL Server 2012 Memory Manager 구성 jevida(강성욱) 2016.10.13 1303
1917 SQL Server Memory Manager 변화 jevida(강성욱) 2016.10.13 1561
1916 Ring_Buffer_Resource_Monitor jevida(강성욱) 2016.10.13 1093
1915 SQL Server 프로파일러 템플릿 만들기 jevida(강성욱) 2016.10.13 1424





XE Login