데이터베이스 개발자 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
1930 트랜잭션 백업 실패와 전체 백업 성공 그리고 대처 방안 jevida(강성욱) 2016.10.13 1538
1929 Fast recovery 와 로그 잠금 jevida(강성욱) 2016.10.13 2018
1928 고스트 클린업 jevida(강성욱) 2016.10.13 2242
1927 페이지 분할이 발생 하였을 때 롤백을 하면 어떻게 될까? jevida(강성욱) 2016.10.13 1637
1926 DBCC WRITEPAGE - DBCC 명령을 사용한 데이터 파괴하기 jevida(강성욱) 2016.10.13 1638
1925 SQL Server Backup Error 3023 jevida(강성욱) 2016.10.13 2239
1924 Delete 작업과 페이지 offset 변화 jevida(강성욱) 2016.10.13 1477
» 트랜잭션 로그 및 LSN을 이용한 삭제된 데이터 복구 jevida(강성욱) 2016.10.13 4953
1922 PFX 형식의 인증서를 SQL Server에서 사용하기 jevida(강성욱) 2016.10.13 1148
1921 SQL Server NUMA 메모리 노드와 Operating System 접근 jevida(강성욱) 2016.10.13 1279
1920 SQL Server 에러 핸들링 비용 비교 jevida(강성욱) 2016.10.13 1301
1919 압축 백업 시 Checksum 옵션으로 손상 확인하기 jevida(강성욱) 2016.10.13 1626
1918 SQL Server 2012 Memory Manager 구성 jevida(강성욱) 2016.10.13 1262
1917 SQL Server Memory Manager 변화 jevida(강성욱) 2016.10.13 1476
1916 Ring_Buffer_Resource_Monitor jevida(강성욱) 2016.10.13 994
1915 SQL Server 프로파일러 템플릿 만들기 jevida(강성욱) 2016.10.13 1331
1914 SQL Server 메모리 병목 현상 식별 jevida(강성욱) 2016.10.13 1755
1913 SQL Server 시작 옵션 사용 jevida(강성욱) 2016.10.13 1777
1912 Ring buffer를 활용한 External Memory Pressure 확인 jevida(강성욱) 2016.10.12 1256
1911 Memory Pressure jevida(강성욱) 2016.10.12 1120





XE Login