[테스트] SQL Server TRN 백업을 통한 데이터 변경 시점 파악

개요

• 목적

  • TRN 백업 파일을 통한 데이터 복구시 시점 파악 필요시

  • 과거 특정 사용자의 DML 트랜잭션 발생 이력 사후 확인시

• 참고 사이트 

  • https://www.sqler.com/board_Column/873012

테스트

• 데이터 변경

  • Test..TestT1의 Num=45 행 데이터 변경 흐름

    • Sq2 num =45 / col1 =0 / col2 ='test45'

    • Sq3 num =45 / col1 =0 / col2 ='한글4' (Update Col2='한글4' )

    • Sq3 num =45 / col1 =321 / col2 ='한글4' (Update Col1=321 )

  • 복원 목표 지점

    • Sq 3의 두번째 Update 문 실행 전 상태 복원

      • num =45인 행의 Col1 ='321' 롤백 (Col1='321' --> Col1='0')

• 쿼리문 실행

  • 'C:\Test\bak\Test_T_2.trn' 분석

  • 실행 쿼리는 최하단 [쿼리문_TRN백업_읽기] 

• Page Id/ Slot Id를 통해 오변경된 (타겟)행 확인

• TRN 백업본을 통한 복원 지점 확인 (Current LSN)

  • page/slotid 자동 형변환 형태로 변경하고 싶다면 아래 예시를 참고하여 변경

    • Convert(INT,cast('' AS XML).value('xs:hexBinary(substring(sql:variable("@hexValueInStringType"),sql:column("t.pos")) )', 'varbinary(max)'))

• STOPBEFOREMARK 를 통한 시점 복원 시도

  • 해당 옵션은 기입한 LSN까지 복원이 아닌 이전까지 복원

•  결과 확인

  • 백업된 테이블 내 데이터 (testT1)과 복원 테이블 내 데이터 (TestT2) 비교를 통해

    Sq3 의 첫번째 Update 까지만 롤백됨을 확인

 

쿼리문_TRN백업_읽기 

--by jyoh.1230
--입력 매개변수 : @trnName /@tbName /@startT /@endT 
declare @trnName varchar(max), @tbName varchar(max), @startT varchar(19), @endT varchar(19)
 
set @trnName ='C:\Test\bak\Test_T_2.trn'
set @tbName = 'dbo.testT1'
set @startT ='2020/12/26 19:58:00:913'
set @endT = '2020/12/27 21:58:00:913'
 
select
convert(varchar(19) , whenT.[Begin Time],120) as [Begin Time] , whenT.[Current LSN], whenT.[Transaction ID], whenT.[Transaction Name] ,
SUSER_SNAME(whenT.[TRANSACTION SID]) as LoginName, whenT.[Operation],InformT.[Lock Information],InformT.[Page ID], InformT.[Slot ID] ,
InformT.[rowlog contents 0],InformT.[rowlog contents 1],InformT.[rowlog contents 2],InformT.[rowlog contents 3]
FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, @trnName,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) whenT
left join fn_dump_dblog (NULL, NULL, N'DISK', 1, @trnName,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) InformT
on left (whenT.[Current LSN], 17) = left (InformT.[Current LSN], 17)
left join sys.partitions pt
on InformT.[PartitionID] = pt.partition_id
where 1=1
AND whenT.Operation IN ( 'LOP_BEGIN_XACT','LOP_COMMIT_XACT')
and convert(varchar(19), whenT.[Begin Time],120) between @startT and @endT
and InformT.Operation IN ('LOP_INSERT_ROWS','LOP_MODIFY_ROW','LOP_MODIFY_COLUMNS', 'LOP_DELETE_ROWS','LOP_BEGIN_XACT','LOP_COMMIT_XACT')
and InformT.[Context] IN ('LCX_HEAP','LCX_CLUSTERED')
and pt.object_id = object_id(@tbName)