[테스트]SQL Server Truncate가 되지 않은 트랜잭션 로그를 통한 삭제 데이터 확인 및 복원

해당 쿼리는 목적에 해당하는 이슈 발생시 빠른 조치 방안을 고민하던 중 웹에서 검색된 쿼리를 제가 원하는 형태로 변형 (특정 테이블과 row별 삭제된 시점 표기)한 것입니다

개요

• 목적

  • 마지막 TRN 백업 이후 Truncate 되지 않은 트랜잭션 로그에 기록된 삭제 데이터 확인 및 복원

    • 사용자 실수로 백업본 없이 데이터 삭제시 빠른 롤백을 위함 

• 참고 사이트 

  • www.sqlservercentral.comarticlesreading-sql-servers-transaction-log

  • 해당 사이트 내 쿼리 변경

    • ASIS : 트랜잭션 로그 내 개별 [RowLog Contents 0]  추출 후 단 건 출력

    • TOBE : 특정 테이블/ 시간에 삭제된 모든 데이터 출력

테스트

• 데이터 삭제

  • Test.dbo.TestT2내 컬럼 Num 1~5 / 2080~2086 삭제

• 쿼리 수행 결과

  • 검정블록 : 삭제된 데이터 조회 : 결과 0건

  • 빨강블록 : 트랜잭션로그 내 삭제 기록 조회 : 결과 12건

 

쿼리문

--by jyoh. 201230
--입력 매개변수 : @tableName/@FromDate/@EndDate

dbcc traceon (2537)
 
 
DECLARE @tableName nvarchar(200), @FromDate varchar(19), @EndDate varchar(19)
DECLARE @cnt INT , @RowLogContents VARBINARY(MAX), @AllocUnitid bigint,@cntTempT int=0
DECLARE @lenFixedBytes SMALLINT, @noOfCols SMALLINT, @nullBitMapLength SMALLINT, @nullByteMap VARBINARY(MAX),
@nullBitMap VARCHAR(MAX), @noVarCols SMALLINT, @columnOffsetArray VARBINARY(MAX), @varColPointer SMALLINT
 
select * from test..testt2 where num between 2080 and 2086 or num between 1 and 5
-----------------------------------------
-----------------입력--------------------
-----------------------------------------
 
set @tableName ='dbo.testt2'
set @FromDate = '2020/12/29 23:20:00'
set @EndDate = '2020/12/30 00:38:00'
-----------------------------------------
-----------------------------------------
-----------------------------------------
 
 
if OBJECT_ID('tempdb..#byteTable' ) is not null
drop table #byteTable
if OBJECT_ID('tempdb..#schema' ) is not null
drop table #schema
if OBJECT_ID('tempdb..#colOffsetTable' ) is not null
drop table #colOffsetTable
if OBJECT_ID('tempdb..#temp_T' ) is not null
drop table #temp_T
if OBJECT_ID('tempdb..#temp_T2' ) is not null
drop table #temp_T2
 
create table #byteTable(
byte INT
)
create table #schema(
[column] INT,
[length] INT,
[name] NVARCHAR(255),
[system_type_id] INT,
[bitpos] INT,
[xprec] INT,
[xscale] INT,
[leaf_offset] INT,
[is_uniqueifier] BIT,
[is_null] BIT NULL
)
create table #colOffsetTable(
colNum SMALLINT,
columnOffset VARBINARY(2),
columnOffvalue SMALLINT,
columnLength SMALLINT
)
create table #temp_T (
num int identity(1,1),
userName varchar(100),
LSN NVARCHAR(Max),
TransactionID NVARCHAR(Max),
AllocUnitid bigint,
Pageid nVARCHAR(MAX),
slotid int,
[begin Time] datetime,
Content VARBINARY(8000)
)
create table #temp_T2(
num int ,
columnName nvarchar(100),
Text nvarchar(max)
)
 
insert into #temp_T (userName ,LSN ,TransactionID ,AllocUnitid ,Pageid ,slotid ,[begin Time] ,Content)
SELECT sl.name , who.[Current LSN], what.[Transaction ID], what.AllocUnitId,
what.[Page ID], what.[Slot ID], who.[Begin Time], what.[RowLog Contents 0]
from fn_dblog (NULL, NULL) who
join fn_dblog (NULL, NULL) what on left (who.[Current LSN], 17) = left (what.[Current LSN], 17)
join sys.syslogins sl on who.[Transaction SID] = sl.sid
WHERE 1=1
and CONVERT(NVARCHAR(19),who.[Begin Time]) BETWEEN @FromDate AND @EndDate
and what.PartitionId = (select partition_id from sys.partitions where object_id = object_id(@tableName))
and what.[Context] IN ('LCX_HEAP','LCX_CLUSTERED') and what.[Operation] = 'LOP_DELETE_ROWS'
order by who.[Begin Time] desc
 
 
select @cntTempT=max(num) from #temp_T
while (@cntTempT >0)
begin
 
 
select @AllocUnitid=AllocUnitid, @RowLogContents=Content from #temp_T where num = @cntTempT
 
SELECT
@lenFixedBytes = CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING(@RowLogContents, 2 + 1, 2)))),
@noOfCols = CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING(@RowLogContents, @lenFixedBytes + 1, 2)))),
@nullBitMapLength = CONVERT(INT, ceiling(@noOfCols/8.0)),
@nullByteMap = SUBSTRING(@RowLogContents, @lenFixedBytes + 3, @nullBitMapLength),
@noVarCols =
CASE WHEN SUBSTRING(@RowLogContents, 1, 1) = 0x30 THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING(@RowLogContents, @lenFixedBytes + 3 + @nullBitMapLength, 2))))
ELSE null
END,
@columnOffsetArray = CASE WHEN SUBSTRING(@RowLogContents, 1, 1) = 0x30 THEN
SUBSTRING(@RowLogContents, @lenFixedBytes + 3 + @nullBitMapLength + 2, @noVarCols * 2)
ELSE null
END,
@varColPointer = CASE WHEN SUBSTRING(@RowLogContents, 1, 1) = 0x30 THEN
(@lenFixedBytes + 2 + @nullBitMapLength + 2 + (@noVarCols * 2))
ELSE null
END
 
SET @cnt = 1
WHILE (@cnt < @nullBitMapLength + 1)
BEGIN
INSERT INTO #byteTable(byte)
VALUES(@cnt)
SET @cnt = @cnt +1
END
SELECT
@nullBitMap = COALESCE(@nullBitMap, '') +
CONVERT(NVARCHAR(1), (SUBSTRING(@nullByteMap, byte, 1) / 128) % 2) +
CONVERT(NVARCHAR(1), (SUBSTRING(@nullByteMap, byte, 1) / 64) % 2) +
CONVERT(NVARCHAR(1), (SUBSTRING(@nullByteMap, byte, 1) / 32) % 2) +
CONVERT(NVARCHAR(1), (SUBSTRING(@nullByteMap, byte, 1) / 16) % 2) +
CONVERT(NVARCHAR(1), (SUBSTRING(@nullByteMap, byte, 1) / 8) % 2) +
CONVERT(NVARCHAR(1), (SUBSTRING(@nullByteMap, byte, 1) / 4) % 2) +
CONVERT(NVARCHAR(1), (SUBSTRING(@nullByteMap, byte, 1) / 2) % 2) +
CONVERT(NVARCHAR(1), SUBSTRING(@nullByteMap, byte, 1) % 2)
FROM #byteTable b
ORDER BY byte DESC
 
 
SET @cnt = 1
WHILE (@cnt <= @noVarCols)
BEGIN
INSERT INTO #colOffsetTable(colNum, columnOffset, columnOffValue, columnLength)
VALUES(
@cnt * - 1,
SUBSTRING (@columnOffsetArray, (2 * @cnt) - 1, 2),
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING (@columnOffsetArray, (2 * @cnt) - 1, 2)))),
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING (@columnOffsetArray, (2 * @cnt) - 1, 2))))
- ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING (@columnOffsetArray, (2 * (@cnt - 1)) - 1, 2)))), 0), @varColPointer)
)
SET @cnt = @cnt + 1
END
 
 
INSERT INTO #schema
SELECT
cols.leaf_null_bit AS nullbit,
ISNULL(syscolumns.length, cols.max_length) AS [length],
CASE WHEN is_uniqueifier = 1 THEN 'UNIQUIFIER'
ELSE isnull(syscolumns.name, 'DROPPED')
END [name],
cols.system_type_id,
cols.leaf_bit_position AS bitpos,
ISNULL(syscolumns.xprec, cols.precision) AS xprec,
ISNULL(syscolumns.xscale, cols.scale) AS xscale,
cols.leaf_offset,
is_uniqueifier,
SUBSTRING(REVERSE(@nullBitMap), cols.leaf_null_bit, 1) AS is_null
FROM sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id)
INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id
LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id
AND syscolumns.colid = cols.partition_column_id
WHERE allocunits.allocation_unit_id = @AllocUnitid
ORDER BY nullbit
INSERT INTO #schema
SELECT -3, 1, 'StatusBitsA', 0, 0, 0, 0, 2147483647, 0, 0
INSERT INTO #schema
SELECT -2, 1, 'StatusBitsB', 0, 0, 0, 0, 2147483647, 0, 0
INSERT INTO #schema
SELECT -1, 2, 'LenFixedBytes', 52, 0, 10, 0, 2147483647, 0, 0
 
insert into #temp_T2
SELECT @cntTempT,
[name] AS ColumnName,
cast(CASE WHEN s.is_null = 1 THEN NULL
ELSE
CASE
WHEN s.system_type_id IN (167, 175, 231, 239) THEN LTRIM(RTRIM(CONVERT(NVARCHAR(MAX), REVERSE(REVERSE(REPLACE(hex_string, 0x00, 0x20))))))
WHEN s.system_type_id = 48 THEN CONVERT(NVARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY(1), REVERSE (hex_string))))
WHEN s.system_type_id = 52 THEN CONVERT(NVARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (hex_string))))
WHEN s.system_type_id = 56 THEN CONVERT(NVARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(hex_string))))
WHEN s.system_type_id = 127 THEN CONVERT(NVARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(8), REVERSE(hex_string))))
WHEN s.system_type_id = 61 THEN
CONVERT(VARCHAR(MAX),
CONVERT(DATETIME, SUBSTRING(hex_string, 4, 1) + SUBSTRING(hex_string, 3, 1) + SUBSTRING(hex_string, 2, 1) + SUBSTRING(hex_string, 1, 1))
+ CONVERT(DATETIME, DATEADD(dd, CONVERT(INT, SUBSTRING(hex_string, 8, 1) + SUBSTRING(hex_string, 7, 1) + SUBSTRING(hex_string, 6, 1) + SUBSTRING(hex_string, 5, 1)), 0x00000000))
, 109)
WHEN s.system_type_id = 108 AND s.xprec = 5 AND s.xscale = 2 THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(5,2), 0x050200 + hex_string))
WHEN s.system_type_id = 108 AND s.xprec = 6 AND s.xscale = 2 THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(6,2), 0x060200 + hex_string))
WHEN s.system_type_id = 108 AND s.xprec = 6 AND s.xscale = 3 THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(6,3), 0x060300 + hex_string))
WHEN s.system_type_id = 108 AND s.xprec = 7 AND s.xscale = 2 THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(7,2), 0x070200 + hex_string))
WHEN s.system_type_id = 108 AND s.xprec = 8 AND s.xscale = 2 THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(8,2), 0x080200 + hex_string))
WHEN s.system_type_id = 108 AND s.xprec = 9 AND s.xscale = 2 THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(9,2), 0x090200 + hex_string))
WHEN s.system_type_id = 108 AND s.xprec = 10 AND s.xscale = 2 THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(10,2), 0x0A0200 + hex_string))
END
END as nvarchar(max)) AS ClearText
FROM (
SELECT s.*,
CASE WHEN s.leaf_offset > 1 AND s.bitpos = 0 THEN
SUBSTRING
(
@RowLogContents,
ISNULL((SELECT TOP 1 SUM(x.length) FROM #schema x WHERE x.[column] < s.[column] AND x.leaf_offset > 1 AND x.bitpos = 0), 0) + 1,
s.LENGTH
)
ELSE
SUBSTRING
(
@RowLogContents,
(col.columnOffValue - col.columnLength) + 1,
col.columnLength
)
END AS hex_string
FROM #schema s
LEFT OUTER JOIN #colOffsetTable col ON col.colNum = (s.leaf_offset)
) AS s
WHERE [column] > 0 AND is_uniqueifier = 0
 
 
set @cntTempT =@cntTempT-1;
 
truncate table #schema
truncate table #byteTable
truncate table #colOffsetTable
end
 
 
 
DECLARE @Query VARCHAR(MAX);
SET @Query = '';
SELECT @Query = @Query + '[' + columnname + '],' FROM (SELECT DISTINCT columnname FROM #temp_T2) A
SET @Query = LEFT(@Query, LEN(@Query)-1)
 
EXEC ('select * from (select A.num as Seq,[begin Time] ,username ,LSN ,columnName,text from #temp_T2 A left join #temp_T B on A.num=B.num ) as Result PIVOT(MAX(Text) FOR columnname IN (' + @Query + ')) AS PVT order by [begin Time] desc' )
 
 
 
if OBJECT_ID('tempdb..#byteTable' ) is not null
drop table #byteTable
if OBJECT_ID('tempdb..#schema' ) is not null
drop table #schema
if OBJECT_ID('tempdb..#colOffsetTable' ) is not null
drop table #colOffsetTable
if OBJECT_ID('tempdb..#temp_T' ) is not null
drop table #temp_T
if OBJECT_ID('tempdb..#temp_T2' ) is not null
drop table #temp_T2