【sqlserver】在没有数据库备份的情况下，获得操作记录信息

关于insert和delete,园子里面有译文：恢复SQL Server被误删除的数据http://www.cnblogs.com/lyhabc/p/3683147.html#3522519

下面看如何获取编辑的信息。

原文链接：https://raresql.com/2012/02/01/how-to-recover-modified-records-from-sql-server-part-2/

===================翻译是按个人理解翻译的。===================

在开始之前，我们做如下例子 :

--Create Table
CREATE TABLE [dbo].[Student]( 
      [Sno] [int] NOT NULL, 
      [Student ID] nvarchar(6) Not NULL , 
      [Student name] [varchar](50) NOT NULL, 
      [Date of Birth]  datetime not null, 
      [Weight] [int] NULL) 
--Inserting data into table
Insert into dbo.[Student] values (1,'STD001','Bob','2003-12-31',40) 
Insert into dbo.[Student] values (2,'STD002','Alexander','2004-11-15',35)
--Check the existence of the data
Select * from dbo.[Student]

例-1 --Update [Student] Set [Student Name]='Ben' Where [SNO]=1
--Operation 的值是 'LOP_MODIFY_ROW'
 
例-2 --Update [Student] Set [Student Name]='Ben',[Weight]=25 Where [SNO]=1
--Operation 的值是 'LOP_MODIFY_COLUMNS'

SQL Server保持每个记录表在不同的时刻在一个特定的Slot页面。如果你有一个Slot ID和Page ID，你可以跟踪记录。

declare
@Database_Name NVARCHAR(MAX)='dbLogTest',
@SchemaName_n_TableName NVARCHAR(MAX)='dbo.Student',
@Date_From datetime='1900/01/01',
@Date_To datetime ='9999/12/31'
--AS
DECLARE @parms nvarchar(1024)
DECLARE @Fileid INT
DECLARE @Pageid INT
DECLARE @Slotid INT
DECLARE @RowLogContents0 VARBINARY(8000)
DECLARE @RowLogContents1 VARBINARY(8000)
DECLARE @RowLogContents3 VARBINARY(8000)
DECLARE @RowLogContents3_Var VARCHAR(MAX)
 
DECLARE @RowLogContents4 VARBINARY(8000)
DECLARE @LogRecord VARBINARY(8000)
DECLARE @LogRecord_Var VARCHAR(MAX)
 
DECLARE @ConsolidatedPageID VARCHAR(MAX)
Declare @AllocUnitID as bigint
Declare @TransactionID as VARCHAR(MAX)
Declare @Operation as VARCHAR(MAX)
Declare @DatabaseCollation VARCHAR(MAX)
--第一步
--用fn_dblog（）从修改的记录获取Slot ID和Page ID。因为通过这些ID在DBCC页功能得到修改的信息。但这些修改后的信息 是用十六进制格式保存的。
--Page ID是文件标识和页号的组合。所以我们需要分开，那么我们需要通过DBCC Page函数来过滤记录。根据Page ID 和Alloc Unit Id我们有一个详细的页面数据表。
所以，我们可以很容易地过滤根据slot获得所需的数据（修改的数据行）。
SELECT [PAGE ID],[Slot ID],[AllocUnitId]
FROM    sys.fn_dblog(NULL, NULL)
WHERE   AllocUnitId IN (Select [Allocation_unit_id] 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)
Where object_id=object_ID('' + @SchemaName_n_TableName + ''))
AND Operation IN ('LOP_MODIFY_ROW','LOP_MODIFY_COLUMNS')  AND [Context] IN ('LCX_HEAP','LCX_CLUSTERED')
AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM    sys.fn_dblog(NULL, NULL)
WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')
AND [Transaction Name]='Update'
AND  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)



--In example -1 & 2 :
--Slot ID=0
--如果 Page ID =0001:00000096
--拆分 Page ID : 0001 是 file ID 
--00000096 是page No.但是他们都是 Hex 格式的. 所以我们需要进行转换到integer然后到DBCC页过滤信息.
Select Convert(int,0x0001)-- = 1 (In Integer)
Select Convert(int,0x00000096)-- = 150 (In Integer)
--We need to pass this file ID and Page ID in DBCC page function to get the page data along with 
--the modified data.

declare @temppagedata table
(
[ParentObject] sysname,
[Object] sysname,
[Field] sysname,
[Value] sysname)
 
declare @pagedata table
(
[Page ID] sysname,
[AllocUnitId] bigint,
[ParentObject] sysname,
[Object] sysname,
[Field] sysname,
[Value] sysname)
  DECLARE Page_Data_Cursor CURSOR FOR
    /*We need to filter LOP_MODIFY_ROW,LOP_MODIFY_COLUMNS from log for modified records & Get its Slot No, Page ID & AllocUnit ID*/
    SELECT [PAGE ID],[Slot ID],[AllocUnitId]
    FROM    sys.fn_dblog(NULL, NULL)  
    WHERE   
    AllocUnitId IN
    (Select [Allocation_unit_id] 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)  
    Where object_id=object_ID('' + @SchemaName_n_TableName + ''))
    AND Operation IN ('LOP_MODIFY_ROW','LOP_MODIFY_COLUMNS')  AND [Context] IN ('LCX_HEAP','LCX_CLUSTERED')
    /*Use this subquery to filter the date*/
 
    AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM    sys.fn_dblog(NULL, NULL) 
    WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')  
    AND [Transaction Name]='UPDATE'
    AND  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)
 
    /****************************************/
 
    GROUP BY [PAGE ID],[Slot ID],[AllocUnitId]
    ORDER BY [Slot ID]  
DBCC Page ('test',1,150) with tableresults,no_infomsgs;
OPEN Page_Data_Cursor
 
    FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID
 
    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @hex_pageid AS VARCHAR(Max)
        SET @Fileid=SUBSTRING(@ConsolidatedPageID,0,CHARINDEX(':',@ConsolidatedPageID))     
        SET @hex_pageid ='0x'+ SUBSTRING(@ConsolidatedPageID,CHARINDEX(':',@ConsolidatedPageID)+1,Len(@ConsolidatedPageID))
 
        SELECT @Pageid=Convert(INT,cast('' AS XML).value('xs:hexBinary(substring(sql:variable("@hex_pageid"),sql:column("t.pos")) )',
 'varbinary(max)'))        
        FROM (SELECT CASE substring(@hex_pageid, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos)
 
        DELETE @temppagedata
            INSERT INTO @temppagedata EXEC( 'DBCC PAGE(' + @DataBase_Name + ', ' + @fileid + ', ' + @pageid + ', 3) with tableresults,no_infomsgs;');
                INSERT INTO @pagedata SELECT @ConsolidatedPageID,@AllocUnitID,[ParentObject],[Object],[Field] ,[Value] FROM @temppagedata
 
        FETCH NEXT FROM Page_Data_Cursor INTO  @ConsolidatedPageID, @Slotid,@AllocUnitID
    END
CLOSE Page_Data_Cursor
DEALLOCATE Page_Data_Cursor
DECLARE @Newhexstring VARCHAR(MAX);
--第二步：
--当我们过滤slot的数据，它通常是在多行（取决于数据量），但目标是将其转换成单行进行进一步处理。
--这里是实现这一目标的源代码。
DECLARE @ModifiedRawData TABLE
(
  [ID] INT IDENTITY(1,1),
  [PAGE ID] VARCHAR(MAX),
  [Slot ID] INT,
  [AllocUnitId] BIGINT,
  [RowLog Contents 0_var] VARCHAR(MAX),
  [RowLog Contents 0] VARBINARY(8000)
)
--This hex value is in string format
INSERT INTO @ModifiedRawData ([PAGE ID],[Slot ID],[AllocUnitId]
,[RowLog Contents 0_var])
SELECT B.[PAGE ID],A.[Slot ID],A.[AllocUnitId]
,(
SELECT REPLACE(STUFF((SELECT REPLACE(SUBSTRING([VALUE],CHARINDEX(':',[Value])+1,48),'†','')
FROM @pagedata C  WHERE B.[Page ID]= C.[Page ID] And A.[Slot ID] =LTRIM(RTRIM(SUBSTRING(C.[ParentObject],5,3))) And [Object] Like '%Memory Dump%'
FOR XML PATH('') ),1,1,'') ,' ','')
) AS [Value]
 
FROM sys.fn_dblog(NULL, NULL) A
INNER JOIN @pagedata B On A.[PAGE ID]=B.[PAGE ID]
AND A.[AllocUnitId]=B.[AllocUnitId]
AND A.[Slot ID] =LTRIM(RTRIM(Substring(B.[ParentObject],5,3)))
AND B.[Object] Like '%Memory Dump%'
WHERE A.AllocUnitId IN
(Select [Allocation_unit_id] 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)
Where object_id=object_ID('' + @SchemaName_n_TableName + ''))
AND Operation in ('LOP_MODIFY_ROW','LOP_MODIFY_COLUMNS') AND [Context] IN ('LCX_HEAP','LCX_CLUSTERED')
/*Use this sub query to filter the date*/
 
AND [TRANSACTION ID] IN (Select DISTINCT [TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL)
Where Context IN ('LCX_NULL') AND Operation IN ('LOP_BEGIN_XACT')
AND [Transaction Name]='Update'
AND  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)
 
/****************************************/
GROUP BY B.[PAGE ID],A.[Slot ID],A.[AllocUnitId]--,[Transaction ID]
ORDER BY [Slot ID]
--But the issue is, this modified data is in string format, we need to convert it into hex format.
 
UPDATE @ModifiedRawData  SET [RowLog Contents 0] = cast('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Contents 0_var]"), 0) )', 
'varbinary(max)')
FROM @ModifiedRawData

select * from @ModifiedRawData
--3.
--下一步是从SQL Server得到修改后的记录。通过使用标准的SQL Server的功能fn_blog。我们只需要从事务日志中选择修改过的记录。
所以我们在SELECT语句包括三个条件（Context, Operation & AllocUnitId）。

--   Context (‘LCX_CLUSTERED’and ‘LCX_HEAP’)
--    Operation (‘LOP_MODIFY_ROW’,’LOP_MODIFY_COLUMNS’)
--    AllocUnitId

--这里是代码片段：
Select [PAGE ID],[Slot ID],[AllocUnitId],[Transaction ID],[RowLog Contents 0], [RowLog Contents 1],[RowLog Contents 3],[RowLog Contents 4]
,[Log Record]
FROM    sys.fn_dblog(NULL, NULL)  
WHERE    AllocUnitId IN
(Select [Allocation_unit_id] 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)  
Where object_id=object_ID('' + 'dbo.student' + ''))
AND Operation in ('LOP_MODIFY_ROW','LOP_MODIFY_COLUMNS') And [Context] IN ('LCX_HEAP','LCX_CLUSTERED')
--AllocUnitId : Allocation Unit ID of the table name.

--step-4：

--在继续之前，我们需要了解SQL Server如何保持更改记录日志。

--在两种操作型的情况下，SQL Server将修改的数据在数据块的形式（这是唯一修改以前的状态的部分）。但问题是它的位置在哪里？

--1 'lop_modify_row”

--如果操作类型是“lop_modify_row然后我们需要选择 [RowLog Contents 0] （包含旧数据（改造前）和 [RowLog Contents 1] ）内容（包含修改的数据）从sys.fn_dblog功能。
但这些数据块是在十六进制格式。
Select [PAGE ID],[Slot ID]
,[RowLog Contents 0], [RowLog Contents 1]
FROM    sys.fn_dblog(NULL, NULL)  
WHERE AllocUnitId IN
(Select [Allocation_unit_id] 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)  
Where object_id=object_ID('' + 'dbo.student' + ''))
AND Operation in ('LOP_MODIFY_ROW') And [Context] IN ('LCX_HEAP','LCX_CLUSTERED')
 
--[RowLog Contents 0]= 0x6F62 --Select Convert (varchar(max),0x6F62)
--[RowLog Contents 1]= 0x656E --Select Convert (varchar(max),0x656E)
--2-‘LOP_MODIFY_COLUMNS’
--如果操作类型是“lop_modify_columns然后实际数据（改造前）和修改后的数据可在[在sys.fn_dblog功能[Log Record] ]栏目。但此数据以合并格式和在一个十六进制数据块中的数据。
Select [PAGE ID],[Slot ID]
,[RowLog Contents 0], [RowLog Contents 1]
,[RowLog Contents 3],[RowLog Contents 4]
,[Log Record]
FROM    sys.fn_dblog(NULL, NULL)  
WHERE AllocUnitId IN
(Select [Allocation_unit_id] 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)  
Where object_id=object_ID('' + 'dbo.student' + ''))
AND Operation in ('LOP_MODIFY_COLUMNS') And [Context] IN ('LCX_HEAP','LCX_CLUSTERED')
--但是，如果数据在列 [Log Record] 中，那么为什么我们需要选择其他列，其他列信息将有助于我们选择和分离合并数据块到修改和实际数据基于 [Log Record] 列。

--5

--在操作型的lop_modify_row '的情况下，它是非常简单的恢复。现在我们有修改后的行（我们准备在步骤2中），在十六进制格式和修改和实际数据块。
下面给出的是修改后的数据是十六进制格式的。
--0x30001400010000000000000060940000460000000500E0020029002C0
--053005400440030003000310042656E
--和数据块

--修改前的数据：0x6f62
--修改后的数据：0x656e

--现在我们需要将修改后的数据的匹配部分替换为实际数据以恢复实际数据。
--因此，更换后，我们将得到旧的（修改前的实际值）值。
--0x30001400010000000000000060940000460000000500e0020029002c0
--0530054004400300030003100426f62
INSERT INTO @ModifiedRawData ([PAGE ID],[Slot ID],[AllocUnitId],[RowLog Contents 0_var]) 
SELECT TOP 1  @ConsolidatedPageID AS [PAGE ID],@Slotid AS [Slot ID],@AllocUnitID AS [AllocUnitId],
REPLACE ([RowLog Contents 0_var],CAST('' AS XML).value('xs:hexBinary(sql:variable("@RowLogContents1") )', 'varchar(max)'),
cast('' AS XML).value('xs:hexBinary(sql:variable("@RowLogContents0") )', 'varchar(max)')) AS [RowLog Contents 0_var]
FROM  @ModifiedRawData WHERE [PAGE ID]=@ConsolidatedPageID And [Slot ID]=@Slotid And [AllocUnitId]=@AllocUnitID ORDER BY [ID] DESC
 
UPDATE @ModifiedRawData  SET [RowLog Contents 0] = cast('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Contents 0_var]"), 0) )',
 'varbinary(max)')
FROM @ModifiedRawData
--最后，我们恢复了数据。现在我们需要将这个十六进制值转换为它们的原始数据类型（可读格式）。


--step-6：

--如果操作类型的lop_modify_columns '，我们需要遵循不同的机制来恢复被修改的数据。

--step-6.1：

--正如我们讨论的，这种操作类型的数据在[Log record] 中，但在合并格式中，所以我们需要得到[Log record] 字段数据。这里是查询。
Select [PAGE ID],[Slot ID],[AllocUnitId],[Transaction ID],[RowLog Contents 0], [RowLog Contents 1],[RowLog Contents 3],[RowLog Contents 4]
,Substring ([Log Record],[Log Record Fixed Length],([Log Record Length]+1)-([Log Record Fixed Length])) as [Log Record]
,Operation
FROM    sys.fn_dblog(NULL, NULL)  
WHERE    --AllocUnitName =@SchemaName_n_TableName --'dbo.STUDENT' 
 AllocUnitId IN
(Select [Allocation_unit_id] 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)  
Where object_id=object_ID('' + @SchemaName_n_TableName + ''))
AND Operation in ('LOP_MODIFY_ROW','LOP_MODIFY_COLUMNS') And [Context] IN ('LCX_HEAP','LCX_CLUSTERED')
/*Use this subquery to filter the date*/
 
AND [TRANSACTION ID] IN (Select DISTINCT [TRANSACTION ID] FROM    sys.fn_dblog(NULL, NULL) 
WHERE Context IN ('LCX_NULL') AND Operation IN ('LOP_BEGIN_XACT')  
AND [Transaction Name]='Update'
AND  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)

--step-6.2：
--我们简化了[log record] 字段的数据，因为我们忽略到固定长度的数据长度选择其余的值。但我们仍然没有我们所需要的数据。这个日志数据，
我们需要在日志记录搜索[RowLog Contents 3] 列数据，
--因为在[RowLog Contents 3] 列数据之后，有我们需要的数据。
  DECLARE @RowLogData_Var VARCHAR(Max)
                            DECLARE @RowLogData_Hex VARBINARY(Max)
SET @RowLogData_Var = SUBSTRING(@LogRecord_Var, CHARINDEX(@RowLogContents3_Var,@LogRecord_Var) +LEN(@RowLogContents3_Var) ,LEN(@LogRecord_Var))
 
SELECT @RowLogData_Hex=CAST('' AS XML).value('xs:hexBinary( substring(sql:variable("@RowLogData_Var"),0) )', 'varbinary(max)') 
FROM (SELECT CASE SUBSTRING(@RowLogData_Var, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos)
select @RowLogData_Var,@RowLogData_Hex
--例子- 2：

--日志记录= 0x00080008000400000014000100010002000200100010002a002a00010002000101000c00002364944e00000102000402030004464a61631962616d6f62000c656e2364

--[RowLog Contents3] = 0x0101000c00002364944e00000102000402030004

--我们所需要的数据：0x464a61631962616d6f62000c656e2364

--我们所需的数据结构是：实际的固定长度数据块+修改的固定长度数据块+实际可变长度数据块+修改变长数据块

--现在，我们有我们所需要的数据，我们也知道所需的数据的结构，但仍然我们不知道分开的块。

--step-6.3：

--所需的数据是@RowLogData_Hex 变量。的目标是单独的固定数据和可变数据相对于实际的和修改的数据块。

--为了实现这一点，我们需要知道块的长度和起始位置来分离数据。

--在 [RowLog Contents 1] 列，我们有固定长度和可变长度的数据长度。
 DECLARE @TotalFixedLengthData INT 
                            DECLARE @FixedLength_Offset INT
                            DECLARE @VariableLength_Offset INT
                            DECLARE @VariableLength_Offset_Start INT
                            DECLARE @VariableLengthIncrease INT
                            DECLARE @FixedLengthIncrease INT
                            DECLARE @OldFixedLengthStartPosition INT
                            DECLARE @FixedLength_Loc INT
                            DECLARE @VariableLength_Loc INT
                            DECLARE @FixedOldValues VARBINARY(MAX)
                            DECLARE @FixedNewValues VARBINARY(MAX)
                            DECLARE @VariableOldValues VARBINARY(MAX)
                            DECLARE @VariableNewValues VARBINARY(MAX)
--For Fixed Length Data Chunks (The length of actual and modified data chunks will be same)
SET @FixedLength_Loc= CONVERT(INT,SUBSTRING(@RowLogContents1,1,1))
Select  CONVERT(INT,SUBSTRING(0x01000200,1,1)) 
--@FixedLength_Loc=1 Means Fixed Length is 1.
 
-- For Variable Length Data Chunks (The length of actual and modified data chunks will be same)
SET @VariableLength_Loc =CONVERT(INT,SUBSTRING(@RowLogContents1,3,1))
Select CONVERT(INT,SUBSTRING(0x01000200,3,1))
--@VariableLength_Loc=2 Means variable Length is 2.
 
--In the [RowLog Contents 0] field, we have the start position of fixed length & variable length data --modification.
-- These fixed length and variable length start positions, we will use at the time of replacement.
SET @FixedLength_Offset= CONVERT(BINARY(2),REVERSE(CONVERT(BINARY(4),(@RowLogContents0))))--)
Select CONVERT(int,CONVERT(BINARY(2),REVERSE(CONVERT(BINARY(4),(0x100010002A002A00))))) 
--@FixedLength_Offset =16 Means Start Position of modification in Fixed Length is 16.
 
SET @VariableLength_Offset=CONVERT(int,CONVERT(BINARY(2),REVERSE(@RowLogContents0)))
Select CONVERT(int,CONVERT(BINARY(2),REVERSE(0x100010002A002A00)))
--@VariableLength_Offset42 Means Start Position of modification in variable Length is 42.

--step-6.4：

--现在，我们有长度和起始位置，所以我们可以很容易地得到实际的和修改的数据块 
-- Actual Fixed length data chunk
SET @FixedOldValues = SUBSTRING(@RowLogData_Hex,1,@FixedLength_Loc)
Select Substring(0x464A61631962616D6F62000C656E2364,1,1)--= 0x46 
 
--One more thing we need to find out that what is the reminder in the  @FixedOldValues because if there 
--is remaindar we need to add it to find the modified chunk.
SET @FixedLengthIncrease = (CASE WHEN (Len(@FixedOldValues)%4)=0 THEN 1 ELSE (4-(LEN(@FixedOldValues)%4))+1  END)
 
--@FixedLengthIncrease=4 
 
--Modified Fixed length data chunks
SET @FixedNewValues =SUBSTRING(@RowLogData_Hex,@FixedLength_Loc+@FixedLengthIncrease,@FixedLength_Loc)
Select SUBSTRING(0x464A61631962616D6F62000C656E2364,1+4,1)-- =0x19
 
-- Actual Variable length data chunk
SET @VariableOldValues =SUBSTRING(@RowLogData_Hex,@FixedLength_Loc+@FixedLengthIncrease+@FixedLength_Loc+
(@FixedLengthIncrease-1),@VariableLength_Loc)
Select SUBSTRING(0x464A61631962616D6F62000C656E2364,1+4+1+(4-1),2)--=0x6F62
 
--Modified Fixed length data chunks
--Same for variable increase.
SET @VariableLengthIncrease =  (CASE WHEN (LEN(@VariableOldValues)%4)=0 THEN 1 ELSE (4-(Len(@VariableOldValues)%4))  END)
 
Select SUBSTRING(SUBSTRING(0x464A61631962616D6F62000C656E2364,1+4+1+(4-1)+2+2,2+1),1,LEN(0x6F62))-- =0x656E
--step-6.5：

--在步骤2中，我们有修改过的数据

--0x30001400010000000000000060940000190000000500e0020029002c0053005400440030003000310042656e  和6.5步中我们收到实际的和修改过的数据块。
现在我们需要做的简单置换为起始位置从我们发现的[RowLog Contents 0] 内容。
SELECT TOP 1  @ConsolidatedPageID AS [PAGE ID],@Slotid AS [Slot ID],@AllocUnitID AS [AllocUnitId],NULL
,SUBSTRING([RowLog Contents 0] ,0,@FixedLength_Offset+1) 
+ CAST(REPLACE(SUBSTRING([RowLog Contents 0],@FixedLength_Offset+1,(@TotalFixedLengthData+4)-@FixedLength_Offset),
@FixedNewValues, @FixedOldValues) AS VARBINARY(max))
                            + SUBSTRING([RowLog Contents 0],@TotalFixedLengthData+5,(@VariableLength_Offset+1)-(@TotalFixedLengthData+5))
                            + CAST(REPLACE(SUBSTRING([RowLog Contents 0],@VariableLength_Offset+1,Len(@VariableNewValues))
                            , @VariableNewValues
, @VariableOldValues) AS VARBINARY)+ SUBSTRING([RowLog Contents 0],@VariableLength_Offset+1+Len(@VariableNewValues),
Len([RowLog Contents 0])-(@VariableLength_Offset+LEN(@VariableNewValues)))
  FROM  @ModifiedRawData WHERE [PAGE ID]=@ConsolidatedPageID And [Slot ID]=@Slotid And [AllocUnitId]=@AllocUnitID ORDER BY [ID] DESC
--所以它会像修改数据：
--0x30001400010000000000000060940000190000000500e0020029002c0053005400440030003000310042656e

--实际数据：
--0x30001400010000000000000060940000460000000500e0020029002c00530054004400300030003100426f62

--STEP-7： --现在，我们有两个实际的和修改的数据在十六进制值，我们需要遵循相同的步骤，遵循“如何恢复已删除的记录”，将其转换成可读的数据格式。
具体看园子里的那篇文章。

---步骤8： --我们有实际的格式的数据，现在我们可以确定哪些列已被更新，这是相同的。在基础上，我们可以创建一个更新语句，可以帮助您恢复修改后的数据。

;With CTE AS (SELECT(CASE
WHEN system_type_id In (167,175,189) THEN QUOTENAME([Name]) + '=' + ISNULL(+ '''' + A.[FieldValue]+ '''','NULL')+ ' ,'+' '
WHEN system_type_id In (231,239) THEN  QUOTENAME([Name]) + '='  + ISNULL(+ 'N''' +A.[FieldValue]+ '''','NULL')+ ' ,'+''
WHEN system_type_id In (58,61,36) THEN QUOTENAME([Name]) + '='  + ISNULL(+  ''''+A.[FieldValue]+ '''','NULL') + '  ,'+' '
WHEN system_type_id In (48,52,56,59,60,62,104,106,108,122,127) THEN QUOTENAME([Name]) + '='  + ISNULL(A.[FieldValue],'NULL')+ ' ,'+' '
END) as [Field]
,A.[Slot ID]
,A.[Transaction ID] as [Transaction ID]
,'D' AS [Type] 
,[A].Rowlogcontents
FROM #temp_Data AS [A]
INNER JOIN #temp_Data AS [B] ON [A].[FieldName]=[B].[FieldName]
AND [A].[Slot ID]=[B].[Slot ID]
AND [B].[Transaction ID]=  (SELECT Min(Cast([Transaction ID] as int)) as [Transaction ID]  FROM [B] AS [C]
WHERE [A].[Slot ID]=[C].[Slot ID]
GROUP BY [Slot ID])
INNER JOIN sys.columns [D] On  [object_id]=object_id('' + @SchemaName_n_TableName + '')
AND A.[Fieldname]=D.[name]
WHERE ISNULL([A].[FieldValue],'')<>ISNULL([B].[FieldValue],'')
 
UNION ALL
 
SELECT(CASE
WHEN system_type_id In (167,175,189) THEN QUOTENAME([Name]) + '=' + ISNULL(+ '''' + A.[FieldValue]+ '''','NULL')+ ' AND '+''
WHEN system_type_id In (231,239) THEN  QUOTENAME([Name]) + '='+ ISNULL(+ 'N''' +A.[FieldValue]+ '''','NULL')+ ' AND '+''
WHEN system_type_id In (58,61,36) THEN QUOTENAME([Name]) + '=' + ISNULL(+  ''''+A.[FieldValue]+ '''','NULL') + ' AND '+''
WHEN system_type_id In (48,52,56,59,60,62,104,106,108,122,127) THEN QUOTENAME([Name]) + '='  + ISNULL(A.[FieldValue],'NULL') + ' AND '+''
END) AS [Field]
,A.[Slot ID]
,A.[Transaction ID] AS [Transaction ID]
,'S' AS [Type] 
,[A].Rowlogcontents
FROM #temp_Data AS [A]
INNER JOIN #temp_Data AS [B] ON [A].[FieldName]=[B].[FieldName]
AND [A].[Slot ID]=[B].[Slot ID]
AND [B].[Transaction ID]=  (SELECT Min(Cast([Transaction ID] as int)) as [Transaction ID] FROM [B] AS [C]
WHERE [A].[Slot ID]=[C].[Slot ID]
GROUP BY [Slot ID])
INNER JOIN sys.columns [D] ON  [object_id]=object_id('' + @SchemaName_n_TableName + '')
AND A.[Fieldname]=D.[name]
WHERE ISNULL([A].[FieldValue],'')=ISNULL([B].[FieldValue],'')
AND A.[Transaction ID] NOT IN (SELECT Min(Cast([Transaction ID] as int)) as [Transaction ID] FROM [B] AS [C]
WHERE [A].[Slot ID]=[C].[Slot ID]
GROUP BY [Slot ID])
)
 
,CTEUpdateQuery AS (SELECT 'UPDATE ' +  @SchemaName_n_TableName +  ' SET ' + LEFT(
STUFF((SELECT ' ' + ISNULL([Field],'')+ ' ' FROM CTE B 
WHERE A.[Slot ID]=B.[Slot ID] AND A.[Transaction ID]=B.[Transaction ID] And B.[Type]='D' FOR XML PATH('') ),1,1,''), 
 
LEN(STUFF((SELECT ' ' +ISNULL([Field],'')+ ' ' FROM CTE B 
WHERE A.[Slot ID]=B.[Slot ID] AND A.[Transaction ID]=B.[Transaction ID] And B.[Type]='D' FOR XML PATH('') ),1,1,'') )-3)
 
+ '  WHERE  ' +
 
LEFT(STUFF((SELECT ' ' +ISNULL([Field],'')+ ' ' FROM CTE C 
WHERE A.[Slot ID]=C.[Slot ID] AND A.[Transaction ID]=C.[Transaction ID] And C.[Type]='S' FOR XML PATH('') ),1,1,'') ,
 
LEN(STUFF((SELECT ' ' +ISNULL([Field],'')+ ' ' FROM CTE C 
WHERE A.[Slot ID]=C.[Slot ID] AND A.[Transaction ID]=C.[Transaction ID] And C.[Type]='S' FOR XML PATH('') ),1,1,''))-5)
AS [Update Statement],
[Slot ID]
,[Transaction ID]
,Rowlogcontents
FROM CTE A
GROUP BY [Slot ID]
,[Transaction ID]
,Rowlogcontents )
 
INSERT INTO #temp_Data SELECT 'UPDATE STATEMENT',[Update Statement],[Rowlogcontents],[Transaction ID],[Slot ID] FROM CTEUpdateQuery