데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

트랜잭션 로그 및 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
» 트랜잭션 로그 및 LSN을 이용한 삭제된 데이터 복구 jevida(강성욱) 2016.10.13 5365
1922 PFX 형식의 인증서를 SQL Server에서 사용하기 jevida(강성욱) 2016.10.13 1172
1921 SQL Server NUMA 메모리 노드와 Operating System 접근 jevida(강성욱) 2016.10.13 1319
1920 SQL Server 에러 핸들링 비용 비교 jevida(강성욱) 2016.10.13 1330
1919 압축 백업 시 Checksum 옵션으로 손상 확인하기 jevida(강성욱) 2016.10.13 1671
1918 SQL Server 2012 Memory Manager 구성 jevida(강성욱) 2016.10.13 1280
1917 SQL Server Memory Manager 변화 jevida(강성욱) 2016.10.13 1516
1916 Ring_Buffer_Resource_Monitor jevida(강성욱) 2016.10.13 1010
1915 SQL Server 프로파일러 템플릿 만들기 jevida(강성욱) 2016.10.13 1373
1914 SQL Server 메모리 병목 현상 식별 jevida(강성욱) 2016.10.13 1854
1913 SQL Server 시작 옵션 사용 jevida(강성욱) 2016.10.13 1865
1912 Ring buffer를 활용한 External Memory Pressure 확인 jevida(강성욱) 2016.10.12 1326
1911 Memory Pressure jevida(강성욱) 2016.10.12 1147
1910 64비트 버전의 SQL Server 버퍼 풀 메모리 페이지 수 줄이는 방법 jevida(강성욱) 2016.10.12 2580
1909 누락된 공유 잠금 (Missing Shared Locks) jevida(강성욱) 2016.10.12 1582
1908 Ad Hoc Distributed Queries 옵션 jevida(강성욱) 2016.10.12 3215
1907 높은 MAXDOP은 쿼리를 느리게 만들 수 있는가? jevida(강성욱) 2016.10.12 1346
1906 변경된 테이블 이름 복구하기 jevida(강성욱) 2016.10.12 1343
1905 Sys,dm_exec_connections jevida(강성욱) 2016.10.12 1995
1904 SQL Server 시작 매개 변수 설정 jevida(강성욱) 2016.10.12 2535





XE Login