恢复SQLSERVER被误删除的数据

发布时间：2020-12-12 12:40:38 所属栏目：MsSql教程来源：网络整理

导读：恢复SQLSERVER被误删除的数据曾经想实现Log Explorer for SQL Server的功能，利用ldf里面的日志来还原误删除的数据这里有一篇文章做到了，不过似乎不是所有的数据类型都支持以下为译文：http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-

恢复SQLSERVER被误删除的数据

曾经想实现Log Explorer for SQL Server的功能，利用ldf里面的日志来还原误删除的数据

这里有一篇文章做到了，不过似乎不是所有的数据类型都支持

以下为译文：http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/

在我使用SQLSERVER的这些年里面，大部分人都会问我一个问题：“能不能恢复被删除的数据？？”

现在，从SQLSERVER2005 或以上版本能很容易能够恢复被删除的数据

（注意：这个脚本能恢复下面的数据类型的数据而且兼容CS 排序规则）

image
text
uniqueidentifier
tinyint
smallint
int
smalldatetime
real
money
datetime
float
sql_variant
ntext
bit
decimal
numeric
smallmoney
bigint
varbinary
varchar
binary
char
timestamp
nvarchar
nchar
xml
sysname

让我来用demo来解释一下我是怎么做到的

USE master
GO
--创建数据库
CREATE DATABASE test
GO

USE [test]
GO


--创建表
CREATE TABLE [dbo].[aa](
    [id] [int] IDENTITY(1,1) NOT NULL,[NAME] [nvarchar](200) NULL
) ON [PRIMARY]
GO


--插入测试数据
INSERT [dbo].[aa]
        ( [NAME] )
SELECT '你好'
GO



--删除数据
Delete from aa
Go



--验证数据是否已经删除
Select * from aa
Go

现在你需要创建一个存储过程来恢复你的数据

-- Script Name: Recover_Deleted_Data_Proc
-- Script Type : Recovery Procedure 
-- Develop By: Muhammad Imran
-- Date Created: 15 Oct 2011
-- Modify Date: 22 Aug 2012
-- Version    : 3.1
-- Notes : Included BLOB data types for recovery.& Compatibile with Default,CS collation,Arabic_CI_AS.
 

CREATE PROCEDURE Recover_Deleted_Data_Proc
    @Database_Name NVARCHAR(MAX),@SchemaName_n_TableName NVARCHAR(MAX),@Date_From DATETIME = '1900/01/01',@Date_To DATETIME = '9999/12/31'
AS
    DECLARE @RowLogContents VARBINARY(8000)
    DECLARE @TransactionID NVARCHAR(MAX)
    DECLARE @AllocUnitID BIGINT
    DECLARE @AllocUnitName NVARCHAR(MAX)
    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @Compatibility_Level INT
 
 
    SELECT  @Compatibility_Level = dtb.compatibility_level
    FROM    master.sys.databases AS dtb
    WHERE   dtb.name = @Database_Name
 
    IF ISNULL(@Compatibility_Level,0) <= 80
        BEGIN
            RAISERROR('The compatibility level should be equal to or greater SQL SERVER 2005 (90)',16,1)
            RETURN
        END
 
    IF ( SELECT COUNT(*)
         FROM   INFORMATION_SCHEMA.TABLES
         WHERE  [TABLE_SCHEMA] + '.' + [TABLE_NAME] = @SchemaName_n_TableName
       ) = 0
        BEGIN
            RAISERROR('Could not found the table in the defined database',1)
            RETURN
        END
 
    DECLARE @bitTable TABLE
        (
          [ID] INT,[Bitvalue] INT
        )
--Create table to set the bit position of one byte.
 
    INSERT  INTO @bitTable
            SELECT  0,2
            UNION ALL
            SELECT  1,2
            UNION ALL
            SELECT  2,4
            UNION ALL
            SELECT  3,8
            UNION ALL
            SELECT  4,16
            UNION ALL
            SELECT  5,32
            UNION ALL
            SELECT  6,64
            UNION ALL
            SELECT  7,128
 
--Create table to collect the row data.
    DECLARE @DeletedRecords TABLE
        (
          [Row ID] INT IDENTITY(1,1),[RowLogContents] VARBINARY(8000),[AllocUnitID] BIGINT,[Transaction ID] NVARCHAR(MAX),[FixedLengthData] SMALLINT,[TotalNoOfCols] SMALLINT,[NullBitMapLength] SMALLINT,[NullBytes] VARBINARY(8000),[TotalNoofVarCols] SMALLINT,[ColumnOffsetArray] VARBINARY(8000),[VarColumnStart] SMALLINT,[Slot ID] INT,[NullBitMap] VARCHAR(MAX)
        )
--Create a common table expression to get all the row data plus how many bytes we have for each row.
;
    WITH    RowData
              AS ( SELECT   [RowLog Contents 0] AS [RowLogContents],[AllocUnitID] AS [AllocUnitID],[Transaction ID] AS [Transaction ID]  
 
--[Fixed Length Data] = Substring (RowLog content 0,Status Bit A+ Status Bit B + 1,2 bytes),CONVERT(SMALLINT,CONVERT(BINARY(2),REVERSE(SUBSTRING([RowLog Contents 0],2 + 1,2)))) AS [FixedLengthData]  --@FixedLengthData
 
-- [TotalnoOfCols] =  Substring (RowLog content 0,[Fixed Length Data] + 1,CONVERT(INT,2)))) + 1,2)))) AS [TotalNoOfCols]
 
--[NullBitMapLength]=ceiling([Total No of Columns] /8.0),CEILING(CONVERT(INT,2)))) / 8.0)) AS [NullBitMapLength] 
 
--[Null Bytes] = Substring (RowLog content 0,Status Bit A+ Status Bit B + [Fixed Length Data] +1,[NullBitMapLength] ),SUBSTRING([RowLog Contents 0],2)))) + 3,2)))) / 8.0))) AS [NullBytes]
 
--[TotalNoofVarCols] = Substring (RowLog content 0,[Null Bitmap length] + 2 ),( CASE WHEN SUBSTRING([RowLog Contents 0],1,1) IN (
                                        0x10,0x30,0x70 )
                                   THEN CONVERT(INT,2)))) + 3
                                                              + CONVERT(INT,2)))) / 8.0)),2))))
                                   ELSE NULL
                              END ) AS [TotalNoofVarCols] 
 
--[ColumnOffsetArray]= Substring (RowLog content 0,[Null Bitmap length] + 2,[TotalNoofVarCols]*2 ),0x70 )
                                   THEN SUBSTRING([RowLog Contents 0],2)))) + 3
                                                  + CONVERT(INT,2)))) / 8.0))
                                                  + 2,1) IN ( 0x10,0x70 )
                                                         THEN CONVERT(INT,2))))
                                                         ELSE NULL
                                                    END ) * 2)
                                   ELSE NULL
                              END ) AS [ColumnOffsetArray] 
 
--  Variable column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] + 2+([TotalNoofVarCols]*2),CASE WHEN SUBSTRING([RowLog Contents 0],1) IN (
                                      0x10,0x70 )
                                 THEN ( CONVERT(SMALLINT,2)))) + 4
                                        + CONVERT(INT,2)))) / 8.0))
                                        + ( ( CASE WHEN SUBSTRING([RowLog Contents 0],0x70 )
                                                   THEN CONVERT(INT,2))))
                                                   ELSE NULL
                                              END ) * 2 ) )
                                 ELSE NULL
                            END AS [VarColumnStart],[Slot ID]
                   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 Context IN ( 'LCX_MARK_AS_GHOST','LCX_HEAP' )
                            AND Operation IN ( 'LOP_DELETE_ROWS' )
                            AND SUBSTRING([RowLog Contents 0],0x70 )
 
/*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] IN ( 'DELETE','user_transaction' )
                                    AND CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From
                                                              AND
                                                              @Date_To )
                 ),--Use this technique to repeate the row till the no of bytes of the row.
            N1 ( n )
              AS ( SELECT   1
                   UNION ALL
                   SELECT   1
                 ),N2 ( n )
              AS ( SELECT   1
                   FROM     N1 AS X,N1 AS Y
                 ),N3 ( n )
              AS ( SELECT   1
                   FROM     N2 AS X,N2 AS Y
                 ),N4 ( n )
              AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY X.n )
                   FROM     N3 AS X,N3 AS Y
                 )
        INSERT  INTO @DeletedRecords
                SELECT  RowLogContents,[AllocUnitID],[Transaction ID],[FixedLengthData],[TotalNoOfCols],[NullBitMapLength],[NullBytes],[TotalNoofVarCols],[ColumnOffsetArray],[VarColumnStart],[Slot ID]
         ---Get the Null value against each column (1 means null zero means not null),[NullBitMap] = ( REPLACE(STUFF(( SELECT
                                                              ','
                                                              + ( CASE
                                                              WHEN [ID] = 0
                                                              THEN CONVERT(NVARCHAR(1),( SUBSTRING(NullBytes,n,1) % 2 ))
                                                              ELSE CONVERT(NVARCHAR(1),( ( SUBSTRING(NullBytes,1)
                                                              / [Bitvalue] )
                                                              % 2 ))
                                                              END ) --as [nullBitMap]
                                                         FROM N4 AS Nums
                                                              JOIN RowData AS C ON n <= NullBitMapLength
                                                              CROSS JOIN @bitTable
                                                         WHERE
                                                              C.[RowLogContents] = D.[RowLogContents]
                                                         ORDER BY [RowLogContents],n ASC
                                                       FOR
                                                         XML PATH('')
                                                       ),''),',','') )
                FROM    RowData D
 
    IF ( SELECT COUNT(*)
         FROM   @DeletedRecords
       ) = 0
        BEGIN
            RAISERROR('There is no data in the log as per the search criteria',1)
            RETURN
        END
 
    DECLARE @ColumnNameAndData TABLE
        (
          [Row ID] INT,[Rowlogcontents] VARBINARY(MAX),[NAME] SYSNAME,[nullbit] SMALLINT,[leaf_offset] SMALLINT,[length] SMALLINT,[system_type_id] TINYINT,[bitpos] TINYINT,[xprec] TINYINT,[xscale] TINYINT,[is_null] INT,[Column value Size] INT,[Column Length] INT,[hex_Value] VARBINARY(MAX),[Update] INT
        )
 
--Create common table expression and join it with the rowdata table
-- to get each column details
/*This part is for variable data columns*/
--@RowLogContents,
--(col.columnOffValue - col.columnLength) + 1,
--col.columnLength
--)
    INSERT  INTO @ColumnNameAndData
            SELECT  [Row ID],Rowlogcontents,NAME,cols.leaf_null_bit AS nullbit,leaf_offset,ISNULL(syscolumns.length,cols.max_length) AS [length],cols.system_type_id,cols.leaf_bit_position AS bitpos,ISNULL(syscolumns.xprec,cols.precision) AS xprec,ISNULL(syscolumns.xscale,cols.scale) AS xscale,SUBSTRING([nullBitMap],cols.leaf_null_bit,1) AS is_null,( CASE WHEN leaf_offset < 1
                                AND SUBSTRING([nullBitMap],1) = 0
                           THEN ( CASE WHEN CONVERT(INT,REVERSE(SUBSTRING([ColumnOffsetArray],( 2
                                                              * leaf_offset
                                                              * -1 ) - 1,2)))) > 30000
                                       THEN CONVERT(INT,2))))
                                            - POWER(2,15)
                                       ELSE CONVERT(INT,2))))
                                  END )
                      END ) AS [Column value Size],2)))) > 30000
                                            AND ISNULL(NULLIF(CONVERT(INT,( 2
                                                              * ( ( leaf_offset
                                                              * -1 ) - 1 ) )
                                                              - 1,2)))),0),[varColumnStart]) < 30000
                                       THEN ( CASE WHEN [System_type_id] IN (
                                                        35,34,99 ) THEN 16
                                                   ELSE 24
                                              END )
                                       WHEN CONVERT(INT,[varColumnStart]) > 30000
                                       THEN ( CASE WHEN [System_type_id] IN (
                                                        35,99 ) THEN 16
                                                   ELSE 24
                                              END ) --24 
                                       WHEN CONVERT(INT,2)))) < 30000
                                            AND ISNULL(NULLIF(CONVERT(INT,[varColumnStart]) < 30000
                                       THEN ( CONVERT(INT,2))))
                                              - ISNULL(NULLIF(CONVERT(INT,[varColumnStart]) )
                                       WHEN CONVERT(INT,[varColumnStart]) > 30000
                                       THEN POWER(2,15)
                                            + CONVERT(INT,2))))
                                            - ISNULL(NULLIF(CONVERT(INT,[varColumnStart])
                                  END )
                      END ) AS [Column Length],( CASE WHEN SUBSTRING([nullBitMap],1) = 1
                           THEN NULL
                           ELSE SUBSTRING(Rowlogcontents,( ( CASE WHEN CONVERT(INT,2)))) > 30000
                                                   THEN CONVERT(INT,2))))
                                                        - POWER(2,15)
                                                   ELSE CONVERT(INT,2))))
                                              END )
                                            - ( CASE WHEN CONVERT(INT,2)))) > 30000
                                                          AND ISNULL(NULLIF(CONVERT(INT,[varColumnStart]) < 30000
                                                     THEN ( CASE
                                                              WHEN [System_type_id] IN (
                                                              35,99 )
                                                              THEN 16
                                                              ELSE 24
                                                            END ) --24 
                                                     WHEN CONVERT(INT,[varColumnStart]) > 30000
                                                     THEN ( CASE
                                                              WHEN [System_type_id] IN (
                                                              35,2)))) < 30000
                                                          AND ISNULL(NULLIF(CONVERT(INT,[varColumnStart]) < 30000
                                                     THEN CONVERT(INT,2))))
                                                          - ISNULL(NULLIF(CONVERT(INT,[varColumnStart])
                                                     WHEN CONVERT(INT,[varColumnStart]) > 30000
                                                     THEN POWER(2,15)
                                                          + CONVERT(INT,[varColumnStart])
                                                END ) ) + 1,( CASE WHEN CONVERT(INT,2)))) > 30000
                                                      AND ISNULL(NULLIF(CONVERT(INT,[varColumnStart]) < 30000
                                                 THEN ( CASE WHEN [System_type_id] IN (
                                                              35,99 )
                                                             THEN 16
                                                             ELSE 24
                                                        END ) --24 
                                                 WHEN CONVERT(INT,[varColumnStart]) > 30000
                                                 THEN ( CASE WHEN [System_type_id] IN (
                                                              35,2)))) < 30000
                                                      AND ISNULL(NULLIF(CONVERT(INT,[varColumnStart]) < 30000
                                                 THEN ABS(CONVERT(INT,[varColumnStart]))
                                                 WHEN CONVERT(INT,[varColumnStart]) > 30000
                                                 THEN POWER(2,15)
                                                      + CONVERT(INT,2))))
                                                      - ISNULL(NULLIF(CONVERT(INT,[varColumnStart])
                                            END ))
                      END ) AS hex_Value,[Slot ID],0
            FROM    @DeletedRecords A
                    INNER JOIN sys.allocation_units allocunits ON A.[AllocUnitId] = allocunits.[Allocation_Unit_Id]
                    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   leaf_offset < 0
            UNION
/*This part is for fixed data columns*/
            SELECT  [Row ID],( SELECT TOP 1
                                ISNULL(SUM(CASE WHEN C.leaf_offset > 1
                                                THEN max_length
                                                ELSE 0
                                           END),0)
                      FROM      sys.system_internals_partition_columns C
                      WHERE     cols.partition_id = C.partition_id
                                AND C.leaf_null_bit < cols.leaf_null_bit
                    ) + 5 AS [Column value Size],syscolumns.length AS [Column Length],CASE WHEN SUBSTRING([nullBitMap],1) = 1
                         THEN NULL
                         ELSE SUBSTRING(Rowlogcontents,( SELECT TOP 1
                                                    ISNULL(SUM(CASE
                                                              WHEN C.leaf_offset > 1
                                                              AND C.leaf_bit_position = 0
                                                              THEN max_length
                                                              ELSE 0
                                                              END),0)
                                          FROM      sys.system_internals_partition_columns C
                                          WHERE     cols.partition_id = C.partition_id
                                                    AND C.leaf_null_bit < cols.leaf_null_bit
                                        ) + 5,syscolumns.length)
                    END AS hex_Value,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   leaf_offset > 0
            ORDER BY nullbit
 
    DECLARE @BitColumnByte AS INT
    SELECT  @BitColumnByte = CONVERT(INT,CEILING(COUNT(*) / 8.0))
    FROM    @ColumnNameAndData
    WHERE   [System_Type_id] = 104;
    WITH    N1 ( n )
              AS ( SELECT   1
                   UNION ALL
                   SELECT   1
                 ),N3 AS Y
                 ),CTE
              AS ( SELECT   RowLogContents,[nullbit],[BitMap] = CONVERT(VARBINARY(1),SUBSTRING(( REPLACE(STUFF(( SELECT
                                                              ',( SUBSTRING(hex_Value,( ( SUBSTRING(hex_Value,1)
                                                              / [Bitvalue] )
                                                              % 2 ))
                                                              END ) --as [nullBitMap]
                                                              FROM
                                                              N4 AS Nums
                                                              JOIN @ColumnNameAndData
                                                              AS C ON n <= @BitColumnByte
                                                              AND [System_Type_id] = 104
                                                              AND bitpos = 0
                                                              CROSS JOIN @bitTable
                                                              WHERE
                                                              C.[RowLogContents] = D.[RowLogContents]
                                                              ORDER BY [RowLogContents],n ASC
                                                              FOR
                                                              XML
                                                              PATH('')
                                                              ),'') ),bitpos + 1,1)))
                   FROM     @ColumnNameAndData D
                   WHERE    [System_Type_id] = 104
                 )
        UPDATE  A
        SET     [hex_Value] = [BitMap]
        FROM    @ColumnNameAndData A
                INNER JOIN CTE B ON A.[RowLogContents] = B.[RowLogContents]
                                    AND A.[nullbit] = B.[nullbit]
 
 
/**************Check for BLOB DATA TYPES******************************/
    DECLARE @Fileid INT
    DECLARE @Pageid INT
    DECLARE @Slotid INT
    DECLARE @CurrentLSN INT
    DECLARE @LinkID INT
    DECLARE @Context VARCHAR(50)
    DECLARE @ConsolidatedPageID VARCHAR(MAX)
    DECLARE @LCX_TEXT_MIX VARBINARY(MAX)
 
    DECLARE @temppagedata TABLE
        (
          [ParentObject] SYSNAME,[Object] SYSNAME,[Field] SYSNAME,[Value] SYSNAME
        )
 
    DECLARE @pagedata TABLE
        (
          [Page ID] SYSNAME,[File IDS] INT,[Page IDS] INT,[AllocUnitId] BIGINT,[ParentObject] SYSNAME,[Value] SYSNAME
        )
 
    DECLARE @ModifiedRawData TABLE
        (
          [ID] INT IDENTITY(1,[PAGE ID] VARCHAR(MAX),[FILE IDS] INT,[PAGE IDS] INT,[RowLog Contents 0_var] VARCHAR(MAX),[RowLog Length] VARCHAR(50),[RowLog Len] INT,[RowLog Contents 0] VARBINARY(MAX),[Link ID] INT DEFAULT ( 0 ),[Update] INT
        )
 
    DECLARE Page_Data_Cursor CURSOR
    FOR
        /*We need to filter LOP_MODIFY_ROW,LOP_MODIFY_COLUMNS from log for deleted records of BLOB data type& Get its Slot No,Page ID & AllocUnit ID*/
            SELECT  LTRIM(RTRIM(REPLACE([Description],'Deallocated',''))) AS [PAGE ID],[AllocUnitId],NULL AS [RowLog Contents 0],Context
            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' )
                    AND [Context] IN ( 'LCX_PFS' )
                    AND Description LIKE '%Deallocated%'
            /*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] = 'DELETE'
                            AND CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From
                                                              AND
                                                              @Date_To )
            GROUP BY [Description],Context
            UNION
            SELECT  [PAGE ID],15,LEN([RowLog Contents 0])) AS [RowLog Contents 0],7,2)),Context --,CAST(RIGHT([Current LSN],4) AS INT) AS [Current LSN]
            FROM    sys.fn_dblog(NULL,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 Context IN ( 'LCX_TEXT_MIX' )
                    AND Operation IN ( 'LOP_DELETE_ROWS' ) 
            /*Use this subquery to filter the date*/
                    AND [TRANSACTION ID] IN (
                    SELECT DISTINCT
                            [TRANSACTION ID]
                    FROM    sys.fn_dblog(NULL,[Begin Time]) BETWEEN @Date_From
                                                              AND
                                                              @Date_To )
                         
            /****************************************/
 
    OPEN Page_Data_Cursor
 
    FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID,@Slotid,@AllocUnitID,@LCX_TEXT_MIX,@LinkID,@Context
 
    WHILE @@FETCH_STATUS = 0
        BEGIN
            DECLARE @hex_pageid AS VARCHAR(MAX)
            /*Page ID contains File Number and page number It looks like 0001:00000130.
              In this example 0001 is file Number &  00000130 is Page Number & These numbers are in Hex format*/
            SET @Fileid = SUBSTRING(@ConsolidatedPageID,0,CHARINDEX(':',@ConsolidatedPageID)) -- Seperate File ID from Page ID
         
            SET @hex_pageid = '0x' + SUBSTRING(@ConsolidatedPageID,@ConsolidatedPageID)
                                               + 1,LEN(@ConsolidatedPageID))  ---Seperate the page ID
            SELECT  @Pageid = CONVERT(INT,CAST('' AS XML).value('xs:hexBinary(substring(sql:variable("@hex_pageid"),sql:column("t.pos")) )','varbinary(max)')) -- Convert Page ID from hex to integer
            FROM    ( SELECT    CASE SUBSTRING(@hex_pageid,2)
                                  WHEN '0x' THEN 3
                                  ELSE 0
                                END
                    ) AS t ( pos ) 
             
            IF @Context = 'LCX_PFS'
                BEGIN
                    DELETE  @temppagedata
                    INSERT  INTO @temppagedata
                            EXEC
                                ( 'DBCC PAGE(' + @DataBase_Name + ','
                                  + @fileid + ',' + @pageid
                                  + ',1) with tableresults,no_infomsgs;'
                                ); 
                    INSERT  INTO @pagedata
                            SELECT  @ConsolidatedPageID,@fileid,@pageid,[ParentObject],[Object],[Field],[Value]
                            FROM    @temppagedata
                END
            ELSE
                IF @Context = 'LCX_TEXT_MIX'
                    BEGIN
                        INSERT  INTO @ModifiedRawData
                                SELECT  @ConsolidatedPageID,NULL,CONVERT(VARBINARY,REVERSE(SUBSTRING(@LCX_TEXT_MIX,11,0
                    END    
            FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID,@Context
        END
     
    CLOSE Page_Data_Cursor
    DEALLOCATE Page_Data_Cursor
 
    DECLARE @Newhexstring VARCHAR(MAX);
 
    --The data is in multiple rows in the page,so we need to convert it into one row as a single hex value.
    --This hex value is in string format
    INSERT  INTO @ModifiedRawData
            ( [PAGE ID],[FILE IDS],[PAGE IDS],[RowLog Contents 0_var],[RowLog Length]
            )
            SELECT  [Page ID],SUBSTRING([ParentObject],CHARINDEX('Slot',[ParentObject]) + 4,( CHARINDEX('Offset',[ParentObject])
                                - ( CHARINDEX('Slot',[ParentObject]) + 4 ) )
                              - 2) AS [Slot ID],SUBSTRING(( SELECT  REPLACE(STUFF(( SELECT
                                                              REPLACE(SUBSTRING([Value],[Value]) + 1,CHARINDEX('?',[Value])
                                                              - CHARINDEX(':',[Value])),'?','')
                                                        FROM  @pagedata C
                                                        WHERE B.[Page ID] = C.[Page ID]
                                                              AND SUBSTRING(B.[ParentObject],B.[ParentObject])
                                                              + 4,B.[ParentObject])
                                                              - ( CHARINDEX('Slot',B.[ParentObject])
                                                              + 4 ) )) = SUBSTRING(C.[ParentObject],C.[ParentObject])
                                                              + 4,C.[ParentObject])
                                                              - ( CHARINDEX('Slot',C.[ParentObject])
                                                              + 4 ) ))
                                                              AND [Object] LIKE '%Memory Dump%'
                                                        ORDER BY '0x'
                                                              + LEFT([Value],[Value]) - 1)
                                                      FOR
                                                        XML PATH('')
                                                      ),' ','')
                              ),20000) AS [Value],SUBSTRING(( SELECT  '0x'
                                        + REPLACE(STUFF(( SELECT
                                                              REPLACE(SUBSTRING([Value],'')
                                                          FROM
                                                              @pagedata C
                                                          WHERE
                                                              B.[Page ID] = C.[Page ID]
                                                              AND SUBSTRING(B.[ParentObject],C.[ParentObject])
                                                              + 4 ) ))
                                                              AND [Object] LIKE '%Memory Dump%'
                                                          ORDER BY '0x'
                                                              + LEFT([Value],[Value]) - 1)
                                                        FOR
                                                          XML PATH('')
                                                        ),4) AS [Length]
            FROM    @pagedata B
            WHERE   [Object] LIKE '%Memory Dump%'
            GROUP BY [Page ID],[AllocUnitId]--,[Current LSN]
            ORDER BY [Slot ID]
 
    UPDATE  @ModifiedRawData
    SET     [RowLog Len] = CONVERT(VARBINARY(8000),REVERSE(CAST('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Length]"),0))','varbinary(Max)')))
    FROM    @ModifiedRawData
    WHERE   [LINK ID] = 0
 
    UPDATE  @ModifiedRawData
    SET     [RowLog Contents 0] = CAST('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Contents 0_var]"),'varbinary(Max)')
    FROM    @ModifiedRawData
    WHERE   [LINK ID] = 0
 
    UPDATE  B
    SET     B.[RowLog Contents 0] = ( CASE WHEN A.[RowLog Contents 0] IS NOT NULL
                                                AND C.[RowLog Contents 0] IS NOT NULL
                                           THEN A.[RowLog Contents 0]
                                                + C.[RowLog Contents 0]
                                           WHEN A.[RowLog Contents 0] IS NULL
                                                AND C.[RowLog Contents 0] IS NOT NULL
                                           THEN C.[RowLog Contents 0]
                                           WHEN A.[RowLog Contents 0] IS NOT NULL
                                                AND C.[RowLog Contents 0] IS NULL
                                           THEN A.[RowLog Contents 0]
                                      END ),B.[Update] = ISNULL(B.[Update],0) + 1
    FROM    @ModifiedRawData B
            LEFT JOIN @ModifiedRawData A ON A.[Page IDS] = CONVERT(INT,CONVERT(VARBINARY(MAX),REVERSE(SUBSTRING(B.[RowLog Contents 0],15 + 14,2))))
                                            AND A.[File IDS] = CONVERT(INT,19 + 14,2))))
                                            AND A.[Link ID] = B.[Link ID]
            LEFT JOIN @ModifiedRawData C ON C.[Page IDS] = CONVERT(INT,27 + 14,2))))
                                            AND C.[File IDS] = CONVERT(INT,31 + 14,2))))
                                            AND C.[Link ID] = B.[Link ID]
    WHERE   ( A.[RowLog Contents 0] IS NOT NULL
              OR C.[RowLog Contents 0] IS NOT NULL
            )
 
 
    UPDATE  B
    SET     B.[RowLog Contents 0] = ( CASE WHEN A.[RowLog Contents 0] IS NOT NULL
                                                AND C.[RowLog Contents 0] IS NOT NULL
                                           THEN A.[RowLog Contents 0]
                                                + C.[RowLog Contents 0]
                                           WHEN A.[RowLog Contents 0] IS NULL
                                                AND C.[RowLog Contents 0] IS NOT NULL
                                           THEN C.[RowLog Contents 0]
                                           WHEN A.[RowLog Contents 0] IS NOT NULL
                                                AND C.[RowLog Contents 0] IS NULL
                                           THEN A.[RowLog Contents 0]
                                      END )
    --,B.[Update]=ISNULL(B.[Update],0)+1
    FROM    @ModifiedRawData B
            LEFT JOIN @ModifiedRawData A ON A.[Page IDS] = CONVERT(INT,2))))
                                            AND A.[Link ID] <> B.[Link ID]
                                            AND B.[Update] = 0
            LEFT JOIN @ModifiedRawData C ON C.[Page IDS] = CONVERT(INT,2))))
                                            AND C.[Link ID] <> B.[Link ID]
                                            AND B.[Update] = 0
    WHERE   ( A.[RowLog Contents 0] IS NOT NULL
              OR C.[RowLog Contents 0] IS NOT NULL
            )
 
    UPDATE  @ModifiedRawData
    SET     [RowLog Contents 0] = ( CASE WHEN [RowLog Len] >= 8000
                                         THEN SUBSTRING([RowLog Contents 0],[RowLog Len])
                                         WHEN [RowLog Len] < 8000
                                         THEN SUBSTRING([RowLog Contents 0],15 + 6,6)))))
                                    END )
    FROM    @ModifiedRawData
    WHERE   [LINK ID] = 0
 
    UPDATE  @ColumnNameAndData
    SET     [hex_Value] = [RowLog Contents 0] 
    --,A.[Update]=A.[Update]+1
    FROM    @ColumnNameAndData A
            INNER JOIN @ModifiedRawData B ON CONVERT(INT,REVERSE(SUBSTRING([hex_value],17,4)))) = [PAGE IDS]
                                             AND CONVERT(INT,SUBSTRING([hex_value],9,2)) = B.[Link ID]
    WHERE   [System_Type_Id] IN ( 99,167,175,231,239,241,165,98 )
            AND [Link ID] <> 0 
 
    UPDATE  @ColumnNameAndData
    SET     [hex_Value] = ( CASE WHEN B.[RowLog Contents 0] IS NOT NULL
                                      AND C.[RowLog Contents 0] IS NOT NULL
                                 THEN B.[RowLog Contents 0]
                                      + C.[RowLog Contents 0]
                                 WHEN B.[RowLog Contents 0] IS NULL
                                      AND C.[RowLog Contents 0] IS NOT NULL
                                 THEN C.[RowLog Contents 0]
                                 WHEN B.[RowLog Contents 0] IS NOT NULL
                                      AND C.[RowLog Contents 0] IS NULL
                                 THEN B.[RowLog Contents 0]
                            END )
    --,A.[Update]=A.[Update]+1
    FROM    @ColumnNameAndData A
            LEFT JOIN @ModifiedRawData B ON CONVERT(INT,5,4)))) = B.[PAGE IDS]
                                            AND B.[Link ID] = 0
            LEFT JOIN @ModifiedRawData C ON CONVERT(INT,4)))) = C.[PAGE IDS]
                                            AND C.[Link ID] = 0
    WHERE   [System_Type_Id] IN ( 99,98 )
            AND ( B.[RowLog Contents 0] IS NOT NULL
                  OR C.[RowLog Contents 0] IS NOT NULL
                )
 
    UPDATE  @ColumnNameAndData
    SET     [hex_Value] = [RowLog Contents 0] 
    --,3,2)) = [Link ID]
    WHERE   [System_Type_Id] IN ( 35,99 )
            AND [Link ID] <> 0 
     
    UPDATE  @ColumnNameAndData
    SET     [hex_Value] = [RowLog Contents 0]
    --,A.[Update]=A.[Update]+10
    FROM    @ColumnNameAndData A
            INNER JOIN @ModifiedRawData B ON CONVERT(INT,4)))) = [PAGE IDS]
    WHERE   [System_Type_Id] IN ( 35,99 )
            AND [Link ID] = 0
 
    UPDATE  @ColumnNameAndData
    SET     [hex_Value] = [RowLog Contents 0] 
    --,99 )
            AND [Link ID] = 0
 
    UPDATE  @ColumnNameAndData
    SET     [hex_value] = 0xFFFE + SUBSTRING([hex_value],LEN([hex_value]))
    --,[Update]=[Update]+1
    WHERE   [system_type_id] = 241
 
    CREATE TABLE [#temp_Data]
        (
          [FieldName] VARCHAR(MAX),[FieldValue] NVARCHAR(MAX),[Rowlogcontents] VARBINARY(8000),[Row ID] INT
        )
 
    INSERT  INTO #temp_Data
            SELECT  NAME,CASE WHEN system_type_id IN ( 231,239 )
                         THEN LTRIM(RTRIM(CONVERT(NVARCHAR(MAX),hex_Value)))  --NVARCHAR,NCHAR
                         WHEN system_type_id IN ( 167,175 )
                         THEN LTRIM(RTRIM(CONVERT(VARCHAR(MAX),hex_Value)))  --VARCHAR,CHAR
                         WHEN system_type_id IN ( 35 )
                         THEN LTRIM(RTRIM(CONVERT(VARCHAR(MAX),hex_Value))) --Text
                         WHEN system_type_id IN ( 99 )
                         THEN LTRIM(RTRIM(CONVERT(NVARCHAR(MAX),hex_Value))) --nText 
                         WHEN system_type_id = 48
                         THEN CONVERT(VARCHAR(MAX),CONVERT(TINYINT,CONVERT(BINARY(1),REVERSE(hex_Value)))) --TINY INTEGER
                         WHEN system_type_id = 52
                         THEN CONVERT(VARCHAR(MAX),REVERSE(hex_Value)))) --SMALL INTEGER
                         WHEN system_type_id = 56
                         THEN CONVERT(VARCHAR(MAX),CONVERT(BINARY(4),REVERSE(hex_Value)))) -- INTEGER
                         WHEN system_type_id = 127
                         THEN CONVERT(VARCHAR(MAX),CONVERT(BIGINT,CONVERT(BINARY(8),REVERSE(hex_Value))))-- BIG INTEGER
                         WHEN system_type_id = 61
                         THEN CONVERT(VARCHAR(MAX),CONVERT(DATETIME,CONVERT(VARBINARY(8000),REVERSE(hex_Value))),100) --DATETIME
                         WHEN system_type_id = 58
                         THEN CONVERT(VARCHAR(MAX),CONVERT(SMALLDATETIME,100) --SMALL DATETIME
                         WHEN system_type_id = 108
                         THEN CONVERT(VARCHAR(MAX),CONVERT(NUMERIC(38,20),CONVERT(VARBINARY(1),xprec)
                              + CONVERT(VARBINARY(1),xscale))
                              + CONVERT(VARBINARY(1),0) + hex_Value)) --- NUMERIC
                         WHEN system_type_id = 106
                         THEN CONVERT(VARCHAR(MAX),CONVERT(DECIMAL(38,0) + hex_Value)) --- DECIMAL
                         WHEN system_type_id IN ( 60,122 )
                         THEN CONVERT(VARCHAR(MAX),CONVERT(MONEY,2) --MONEY,SMALLMONEY
                         WHEN system_type_id = 104
                         THEN CONVERT(VARCHAR(MAX),CONVERT (BIT,hex_Value)
                              % 2))  -- BIT
                         WHEN system_type_id = 62
                         THEN RTRIM(LTRIM(STR(CONVERT(FLOAT,SIGN(CAST(CONVERT(VARBINARY(8000),REVERSE(hex_Value)) AS BIGINT))
                                              * ( 1.0
                                                  + ( CAST(CONVERT(VARBINARY(8000),REVERSE(hex_Value)) AS BIGINT)
                                                      & 0x000FFFFFFFFFFFFF )
                                                  * POWER(CAST(2 AS FLOAT),-52) )
                                              * POWER(CAST(2 AS FLOAT),( ( CAST(CONVERT(VARBINARY(8000),REVERSE(hex_Value)) AS BIGINT)
                                                          & 0x7ff0000000000000 )
                                                        / EXP(52 * LOG(2))
                                                        - 1023 ))),53,LEN(hex_Value)))) --- FLOAT
                         WHEN system_type_id = 59
                         THEN LEFT(LTRIM(STR(CAST(SIGN(CAST(CONVERT(VARBINARY(8000),REVERSE(hex_Value)) AS BIGINT))
                                             * ( 1.0
                                                 + ( CAST(CONVERT(VARBINARY(8000),REVERSE(hex_Value)) AS BIGINT)
                                                     & 0x007FFFFF )
                                                 * POWER(CAST(2 AS REAL),-23) )
                                             * POWER(CAST(2 AS REAL),( ( ( CAST(CONVERT(VARBINARY(8000),REVERSE(hex_Value)) AS INT) )
                                                         & 0x7f800000 )
                                                       / EXP(23 * LOG(2))
                                                       - 127 )) AS REAL),23,23)),8) --Real
                         WHEN system_type_id IN ( 165,173 )
                         THEN ( CASE WHEN CHARINDEX(0x,CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))','VARBINARY(8000)')) = 0
                                     THEN '0x'
                                     ELSE ''
                                END ) + CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))','varchar(max)') -- BINARY,VARBINARY
                         WHEN system_type_id = 34
                         THEN ( CASE WHEN CHARINDEX(0x,'varchar(max)')  --IMAGE
                         WHEN system_type_id = 36
                         THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,hex_Value)) --UNIQUEIDENTIFIER
                         WHEN system_type_id = 231
                         THEN CONVERT(VARCHAR(MAX),CONVERT(SYSNAME,hex_Value)) --SYSNAME
                         WHEN system_type_id = 241
                         THEN CONVERT(VARCHAR(MAX),CONVERT(XML,hex_Value)) --XML
                         WHEN system_type_id = 189
                         THEN ( CASE WHEN CHARINDEX(0x,'varchar(max)') --TIMESTAMP
                         WHEN system_type_id = 98
                         THEN ( CASE WHEN CONVERT(INT,SUBSTRING(hex_Value,1)) = 56
                                     THEN CONVERT(VARCHAR(MAX),REVERSE(SUBSTRING(hex_Value,LEN(hex_Value))))))  -- INTEGER
                                     WHEN CONVERT(INT,1)) = 108
                                     THEN CONVERT(VARCHAR(MAX),1))
                                          + CONVERT(VARBINARY(1),4,0)
                                          + SUBSTRING(hex_Value,LEN(hex_Value)))) --- NUMERIC
                                     WHEN CONVERT(INT,1)) = 167
                                     THEN LTRIM(RTRIM(CONVERT(VARCHAR(MAX),LEN(hex_Value))))) --VARCHAR,CHAR
                                     WHEN CONVERT(INT,1)) = 36
                                     THEN CONVERT(VARCHAR(MAX),SUBSTRING(( hex_Value ),20))) --UNIQUEIDENTIFIER
                                     WHEN CONVERT(INT,1)) = 61
                                     THEN CONVERT(VARCHAR(MAX),LEN(hex_Value))))),100) --DATETIME
                                     WHEN CONVERT(INT,1)) = 165
                                     THEN '0x'
                                          + SUBSTRING(( CASE WHEN CHARINDEX(0x,'VARBINARY(8000)')) = 0
                                                             THEN '0x'
                                                             ELSE ''
                                                        END )
                                                      + CAST('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))','varchar(max)'),LEN(hex_Value)) -- BINARY,VARBINARY
                                END )
                    END AS FieldValue,[Rowlogcontents],[Row ID]
            FROM    @ColumnNameAndData
            ORDER BY nullbit
 
--Create the column name in the same order to do pivot table.
 
    DECLARE @FieldName VARCHAR(MAX)
    SET @FieldName = STUFF(( SELECT ','
                                    + CAST(QUOTENAME([Name]) AS VARCHAR(MAX))
                             FROM   syscolumns
                             WHERE  id = OBJECT_ID(''
                                                   + @SchemaName_n_TableName
                                                   + '')
                           FOR
                             XML PATH('')
                           ),'')
 
--Finally did pivot table and get the data back in the same format.
 
    SET @sql = 'SELECT ' + @FieldName
        + ' FROM #temp_Data PIVOT (Min([FieldValue]) FOR FieldName IN ('
        + @FieldName + ')) AS pvt'
    EXEC sp_executesql @sql
 
GO

View Code

恢复你的数据

--恢复数据，不加时间段条件  参数：数据库名，表名
--EXAMPLE #1 : FOR ALL DELETED RECORDS
EXEC Recover_Deleted_Data_Proc 'test','dbo.aa'
GO



--恢复数据，加时间段条件
--EXAMPLE #2 : FOR ANY SPECIFIC DATE RANGE
EXEC Recover_Deleted_Data_Proc 'test','dbo.aa','2014-04-23','2014-04-23'

执行了下面的存储过程之后你会发现会显示出刚才删除的数据

EXEC Recover_Deleted_Data_Proc 'test','dbo.aa'
GO

解释

究竟他是如何工作的?让我们来一步一步来,这个过程涉及到7个步骤:

步骤1:

我们需要获得SQLSERVER删除的数据记录.使用标准SQLSERVER函数fn_dblog，我们能够容易的获得事务日志记录（包括

已删的数据。不过，我们只需要事务日志中选中的被删数据，所以我们的过滤条件需要包含3个字段 Context,Operation &?AllocUnitName）

We need to get the deleted records from?sql?server. By using the standard SQL Server function?fn_blog,we can easily get all transaction log (Including deleted data. But,we need only the selected deleted records from the transaction log. So we included three filters (Context,Operation,AllocUnitName).

Context (‘LCX_MARK_AS_GHOST’and?‘LCX_HEAP’)
Operation (‘LOP_DELETE_ROWS’)
AllocUnitName(‘dbo.aa’) –- Schema + table Name

Context可以说明是堆表还是聚集表

Operation：删除操作

AllocUnitName：分配单元名称，表名

下面是一个代码片段

SELECT  [RowLog Contents 0]
FROM    sys.fn_dblog(NULL,NULL)
WHERE   AllocUnitName = 'dbo.aa'
        AND Context IN ( 'LCX_MARK_AS_GHOST','LCX_HEAP' )
        AND Operation IN ( 'LOP_DELETE_ROWS' )

这个查询会返回不同列的信息，但是我们只需要选择[RowLog Contents 0]列，去获得被删除的数据的内容

RowLog?content 0列的内容类似于这样

“0x300018000100000000000000006B0000564920205900000

00500E001002800426F62206A65727279″

步骤2:

现在，我们已经删除了数据，这些数据以hex码的形式放在事务日志里，这些hex码是有规律的，我们根据这些规律可以很容易恢复这些数据。

不过在恢复这些数据之前，我们需要理解这些格式。这些格式在KalenDelaney’s SQL Internal’s book.的书里面有讲解

1 Byte : Status Bit A
1 Byte : Status Bit B
2 Bytes : Fixed length size
n Bytes : Fixed length data
2 Bytes : Total Number of Columns
n Bytes : NULL Bitmap (1 bit for each column as 1 indicates that the column is null and 0 indicate that the column is not null)
2 Bytes : Number of variable-length columns
n Bytes : Column offset array (2x variable length column)
n Bytes : Data for variable length columns

所以,hex码的“RowLog?content 0″列的内容就等价于

“Status Bit A +Status Bit B +Fixed length size +Fixed length data +Total Number of Columns +NULL Bitmap +Number of variable-length columns +NULL Bitmap+Number of variable-length columns +Column offset array +Data for variable length columns.”

更详细的可以参考：SQL Server2008存储结构之堆表、行溢出

关于数据行的结构我们还可以采用稍微宏观一些的视角来查看。

?

　　

步骤3:

现在，我们需要解剖RowLog Content o列的内容（我们删除的数据的Hex码），利用上面的数据行的结构

[Fixed Length Data]?= Substring (RowLog?content 0,?Status Bit A+Status Bit B?+ 1,2 bytes)
[Total No of Columns]= Substring (RowLog?content 0,?[Fixed Length Data]?+ 1,2 bytes)
[Null Bitmap length]?= Ceiling?([Total No of Columns]/8.0)
[Null Bytes]= Substring (RowLog?content 0,?Status Bit A+ Status Bit B?+[Fixed Length Data]?+1,?[Null Bitmap length]?)
Total no of variable columns?= Substring (RowLog?content 0,?Status Bit A+ Status Bit B?+?[Fixed Length Data]?+1,?[Null Bitmap length]?+ 2 )
Column Offset Array= Substring (RowLog?content 0,?Status Bit A+ Status Bit B?+?[Fixed Length Data]?+1,?[Null Bitmap length]?+ 2,?Total no of variable columns*2 )
Variable Column Start =?Status Bit A+ Status Bit B?+?[Fixed Length Data]?+?[Null Bitmap length]?+ 2+(?Total no of variable columns*2)

步骤4:

现在我们已经将hex码切开了(0x300008000100000002000001001300604F7D59),所以,我们能找到删除的行的某列的数据是否为null值

根据NULL位图。为了完成将NULL Bytes的hex码转换为二进制格式（正如之前讨论的，1表示行中对应的那一列为null，而0则表示对应的列有实际的数据）

如果还不是明白的童鞋可以看一下我写的这篇文章：《SQLSERVER中NULL位图的作用》

步骤5:

现在，我们已经做了初步的数据分割?(Step-3) 和null值判断(Step-4) 。然后我们需要使用代码片段去获得列数据，例如：列名，列大小，精度，范围

和最重要的叶子的null位（确保列数据是固定长度的（<=-1表示可变长度）或者固定长度的（>=1））

使用下面的SQL语句

SELECT  *
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

与(Step-1,2,3,4) 获得的数据表做join连接，根据allocunits.[Allocation_Unit_Id]。

现在我们知道表和表中的数据信息，那么我们需要利用这些数据去将?[RowLog?Contents 0] 列里的hex码的数据插入到表中的相应列

现在我们需要关心每一列的数据究竟是固定长度的还是可变长度的

步骤6:

我们收集了每列的hex格式的数据。现在我们需要利用[System_type_id]去转换这些数据回去正确的数据类型

每一种数据类型都有不同的数据类型转换机制。

--NVARCHAR,NCHAR
WHEN system_type_id IN (231,239) THEN  LTRIM(RTRIM(CONVERT(NVARCHAR(max),hex_Value)))
 
--VARCHAR,CHAR
 WHEN system_type_id IN (167,175) THEN  LTRIM(RTRIM(CONVERT(VARCHAR(max),REPLACE(hex_Value,0x00,0x20))))
 
--TINY INTEGER
 WHEN system_type_id = 48 THEN CONVERT(VARCHAR(MAX),REVERSE (hex_Value))))
 
--SMALL INTEGER
 WHEN system_type_id = 52 THEN CONVERT(VARCHAR(MAX),REVERSE (hex_Value))))
 
-- INTEGER
 WHEN system_type_id = 56 THEN CONVERT(VARCHAR(MAX),REVERSE(hex_Value))))
 
-- BIG INTEGER
 WHEN system_type_id = 127 THEN CONVERT(VARCHAR(MAX),REVERSE(hex_Value))))
 
--DATETIME
 WHEN system_type_id = 61 Then CONVERT(VARCHAR(Max),Convert(VARBINARY(max),REVERSE (hex_Value))),100)
 
--SMALL DATETIME
WHEN system_type_id =58 Then CONVERT(VARCHAR(Max),100) --SMALL DATETIME
 
--- NUMERIC
 WHEN system_type_id = 108 THEN CONVERT(VARCHAR(MAX),CAST(CONVERT(NUMERIC(18,14),xprec)+CONVERT(VARBINARY,xscale))+CONVERT(VARBINARY(1),0) + hex_Value) as FLOAT))
 
--MONEY,SMALLMONEY
 WHEN system_type_id In(60,122) THEN CONVERT(VARCHAR(MAX),Convert(MONEY,Convert(VARBINARY(MAX),Reverse(hex_Value))),2)
 
--- DECIMAL
 WHEN system_type_id = 106 THEN CONVERT(VARCHAR(MAX),CAST(CONVERT(Decimal(38,34),Convert(VARBINARY,0) + hex_Value) as FLOAT))
 
-- BIT
 WHEN system_type_id = 104 THEN CONVERT(VARCHAR(MAX),hex_Value)%2))
 
--- FLOAT
 WHEN system_type_id = 62 THEN  RTRIM(LTRIM(Str(Convert(FLOAT,SIGN(CAST(Convert(VARBINARY(max),Reverse(hex_Value)) AS BIGINT)) * (1.0 + (CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) AS BIGINT) & 0x000FFFFFFFFFFFFF) * POWER(CAST(2 AS FLOAT),-52)) * POWER(CAST(2 AS FLOAT),((CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) AS BIGINT) & 0x7ff0000000000000) / EXP(52 * LOG(2))-1023))),LEN(hex_Value))))
 
--REAL
When  system_type_id =59 THEN  Left(LTRIM(STR(Cast(SIGN(CAST(Convert(VARBINARY(max),Reverse(hex_Value)) AS BIGINT))* (1.0 + (CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) AS BIGINT) & 0x007FFFFF) * POWER(CAST(2 AS Real),-23)) * POWER(CAST(2 AS Real),(((CAST(CONVERT(VARBINARY(max),Reverse(hex_Value)) AS INT) )& 0x7f800000)/ EXP(23 * LOG(2))-127))AS REAL),8)
 
--BINARY,VARBINARY
WHEN system_type_id In (165,173) THEN (CASE WHEN Charindex(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_value"))','varbinary(max)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_value"))','varchar(max)') 
 
--UNIQUEIDENTIFIER 
WHEN system_type_id =36 THEN CONVERT(VARCHAR(MAX),hex_Value))

步骤7:

最终我们做一个数据透视表，你会看到最后的结果：被删的数据回来了！

注意：这些数据只是展示出来并没有自动插入回表中，你需要将这些数据重新插入回去表中！

我的测试

经过测试，作者写的这个存储过程还是有些问题

如果你创建的测试表的数据类型有xml或者是一些text数据类型的字段会有报错

Msg 537,Level 16,State 3,Procedure Recover_Deleted_Data_Proc,Line 525
Invalid length parameter passed to the LEFT or SUBSTRING function.

Msg 9420,State 1,Line 651
XML parsing: line 1,character 2,illegal xml character

但是一般的数据类型则不会，例如nvarchar这些

还有不要在存储过程的最后加

--Recover the deleted data without date range
EXEC Recover_Deleted_Data_Proc 'test','dbo.Test_Table'
GO
--Recover the deleted data it with date range
EXEC Recover_Deleted_Data_Proc 'test','dbo.Test_Table','2012-06-01','2012-06-30'

否则会报错

消息 50000，级别 16，状态 1，过程 Recover_Deleted_Data_Proc，第 290 行
There is no data in the log as per the search criteria

总结

实际上这个存储过程还是挺有研究意义的，对于想做一个跟Log Explorer for SQL Server软件功能差不多的软件出来

还是有可能的，跟着作者的思路，一步一步实现

苦于最近太忙，先分享出来，以后再研究这个存储过程了~

如有不对的地方，欢迎大家拍砖o(∩_∩)o?

支持date类型感谢园友?dwchaoyue

EXEC Recover_Deleted_Data_Proc 'sss','dbo.testdaterecover','2014-10-23'
--DROP TABLE testdaterecover
CREATE TABLE testdaterecover(dd DATE,cc DATETIME)
GO
INSERT INTO [dbo].[testdaterecover]
        ( [dd],[cc] )
VALUES  ( GETDATE(),-- dd - date
          GETDATE()  -- cc - datetime
          )


SELECT CONVERT(VARCHAR(MAX),[cc])),120) FROM [dbo].[testdaterecover]

DELETE [dbo].[testdaterecover]


THEN CONVERT(VARCHAR(MAX),121) --DATETIME

View Code

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!