恢复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 masterGO--创建数据库CREATE DATABASE testGOUSE [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 aaGo--验证数据是否已经删除Select * from aaGo

 

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

-- 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',16,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,2 bytes)                            ,                            CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              2 + 1, 2)))) + 1,                                                              2)))) AS [TotalNoOfCols]
 --[NullBitMapLength]=ceiling([Total No of Columns] /8.0)                            ,                            CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              2 + 1, 2)))) + 1,                                                              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],                                      CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              2 + 1, 2)))) + 3,                                      CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              2 + 1, 2)))) + 1,                                                              2)))) / 8.0))) AS [NullBytes]
 --[TotalNoofVarCols] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 )                            ,
                            ( CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) IN (                                        0x10, 0x30, 0x70 )                                   THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              2 + 1, 2)))) + 3
                                                              + CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              2 + 1, 2)))) + 1,                                                              2)))) / 8.0)), 2))))                                   ELSE NULL
                              END ) AS [TotalNoofVarCols] 
 --[ColumnOffsetArray]= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 , [TotalNoofVarCols]*2 )                            ,
                            ( CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) IN (                                        0x10, 0x30, 0x70 )                                   THEN SUBSTRING([RowLog Contents 0],                                                  CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              2 + 1, 2)))) + 3
                                                  + CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              2 + 1, 2)))) + 1,                                                              2)))) / 8.0))                                                  + 2,
                                                  ( CASE WHEN SUBSTRING([RowLog Contents 0],                                                              1, 1) IN ( 0x10,                                                              0x30, 0x70 )                                                         THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              2 + 1, 2)))) + 3
                                                              + CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              2 + 1, 2)))) + 1,                                                              2)))) / 8.0)), 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, 1) IN (                                      0x10, 0x30, 0x70 )                                 THEN ( CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              2 + 1, 2)))) + 4
                                        + CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              2 + 1, 2)))) + 1,                                                              2)))) / 8.0))                                        + ( ( CASE WHEN SUBSTRING([RowLog Contents 0],                                                              1, 1) IN ( 0x10,                                                              0x30, 0x70 )                                                   THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              2 + 1, 2)))) + 3
                                                              + CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],                                                              2 + 1, 2)))) + 1,                                                              2)))) / 8.0)), 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], 1, 1) IN ( 0x10,                                                              0x30, 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,
                                                              n, 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('')
                                                       ), 1, 1, ''), ',', '') )                FROM    RowData D 
    IF ( SELECT COUNT(*)         FROM   @DeletedRecords
       ) = 0
        BEGIN
            RAISERROR('There is no data in the log as per the search criteria',16,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) ,          [Slot ID] INT ,          [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], cols.leaf_null_bit,                                              1) = 0
                           THEN ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * leaf_offset                                                              * -1 ) - 1, 2)))) > 30000
                                       THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * leaf_offset                                                              * -1 ) - 1, 2))))                                            - POWER(2, 15)                                       ELSE CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * leaf_offset                                                              * -1 ) - 1, 2))))                                  END )                      END ) AS [Column value Size] ,
                    ( CASE WHEN leaf_offset < 1
                                AND SUBSTRING([nullBitMap], cols.leaf_null_bit,                                              1) = 0
                           THEN ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * leaf_offset                                                              * -1 ) - 1, 2)))) > 30000
                                            AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 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, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * leaf_offset                                                              * -1 ) - 1, 2)))) > 30000
                                            AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 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 ) --24 
                                       WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * leaf_offset                                                              * -1 ) - 1, 2)))) < 30000
                                            AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * ( ( leaf_offset                                                              * -1 ) - 1 ) )                                                              - 1, 2)))), 0),                                                       [varColumnStart]) < 30000
                                       THEN ( CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * leaf_offset                                                              * -1 ) - 1, 2))))                                              - ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * ( ( leaf_offset                                                              * -1 ) - 1 ) )                                                              - 1, 2)))), 0),                                                       [varColumnStart]) )                                       WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * leaf_offset                                                              * -1 ) - 1, 2)))) < 30000
                                            AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * ( ( leaf_offset                                                              * -1 ) - 1 ) )                                                              - 1, 2)))), 0),                                                       [varColumnStart]) > 30000
                                       THEN POWER(2, 15)                                            + CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * leaf_offset                                                              * -1 ) - 1, 2))))                                            - ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * ( ( leaf_offset                                                              * -1 ) - 1 ) )                                                              - 1, 2)))), 0),                                                     [varColumnStart])                                  END )                      END ) AS [Column Length] ,
                    ( CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) = 1
                           THEN NULL
                           ELSE SUBSTRING(Rowlogcontents,
                                          ( ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * leaf_offset                                                              * -1 ) - 1, 2)))) > 30000
                                                   THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * leaf_offset                                                              * -1 ) - 1, 2))))                                                        - POWER(2, 15)                                                   ELSE CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * leaf_offset                                                              * -1 ) - 1, 2))))                                              END )                                            - ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * leaf_offset                                                              * -1 ) - 1, 2)))) > 30000
                                                          AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 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 ) --24 
                                                     WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * leaf_offset                                                              * -1 ) - 1, 2)))) > 30000
                                                          AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 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 ) --24 
                                                     WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * leaf_offset                                                              * -1 ) - 1, 2)))) < 30000
                                                          AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * ( ( leaf_offset                                                              * -1 ) - 1 ) )                                                              - 1, 2)))), 0),                                                              [varColumnStart]) < 30000
                                                     THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * leaf_offset                                                              * -1 ) - 1, 2))))                                                          - ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * ( ( leaf_offset                                                              * -1 ) - 1 ) )                                                              - 1, 2)))), 0),                                                              [varColumnStart])                                                     WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * leaf_offset                                                              * -1 ) - 1, 2)))) < 30000
                                                          AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * ( ( leaf_offset                                                              * -1 ) - 1 ) )                                                              - 1, 2)))), 0),                                                              [varColumnStart]) > 30000
                                                     THEN POWER(2, 15)                                                          + CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * leaf_offset                                                              * -1 ) - 1, 2))))                                                          - ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * ( ( leaf_offset                                                              * -1 ) - 1 ) )                                                              - 1, 2)))), 0),                                                              [varColumnStart])                                                END ) ) + 1,
                                          ( CASE WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * leaf_offset                                                              * -1 ) - 1, 2)))) > 30000
                                                      AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 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 ) --24 
                                                 WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * leaf_offset                                                              * -1 ) - 1, 2)))) > 30000
                                                      AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 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 ) --24 
                                                 WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * leaf_offset                                                              * -1 ) - 1, 2)))) < 30000
                                                      AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * ( ( leaf_offset                                                              * -1 ) - 1 ) )                                                              - 1, 2)))), 0),                                                              [varColumnStart]) < 30000
                                                 THEN ABS(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * leaf_offset                                                              * -1 ) - 1, 2))))                                                          - ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * ( ( leaf_offset                                                              * -1 ) - 1 ) )                                                              - 1, 2)))), 0),                                                              [varColumnStart]))                                                 WHEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * leaf_offset                                                              * -1 ) - 1, 2)))) < 30000
                                                      AND ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * ( ( leaf_offset                                                              * -1 ) - 1 ) )                                                              - 1, 2)))), 0),                                                              [varColumnStart]) > 30000
                                                 THEN POWER(2, 15)                                                      + CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * leaf_offset                                                              * -1 ) - 1, 2))))                                                      - ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([ColumnOffsetArray],
                                                              ( 2
                                                              * ( ( leaf_offset                                                              * -1 ) - 1 ) )                                                              - 1, 2)))), 0),                                                              [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] ,
                    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 ,
                    ( 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], cols.leaf_null_bit, 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 ,                    [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
            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
                 ),
            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
                 ),
            CTE              AS ( SELECT   RowLogContents ,                            [nullbit] ,                            [BitMap] = CONVERT(VARBINARY(1), CONVERT(INT, SUBSTRING(( REPLACE(STUFF(( SELECT
                                                              ','
                                                              + ( CASE
                                                              WHEN [ID] = 0
                                                              THEN CONVERT(NVARCHAR(1), ( SUBSTRING(hex_Value,
                                                              n, 1) % 2 ))                                                              ELSE CONVERT(NVARCHAR(1), ( ( SUBSTRING(hex_Value,
                                                              n, 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('')
                                                              ), 1, 1, ''),                                                              ',', '') ),
                                                              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 ,          [Object] SYSNAME ,          [Field] SYSNAME ,          [Value] SYSNAME
        ) 
    DECLARE @ModifiedRawData TABLE
        (          [ID] INT IDENTITY(1, 1) ,          [PAGE ID] VARCHAR(MAX) ,          [FILE IDS] INT ,          [PAGE IDS] INT ,          [Slot ID] INT ,          [AllocUnitId] BIGINT ,          [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] ,                    [Slot ID] ,                    [AllocUnitId] ,                    NULL AS [RowLog Contents 0] ,                    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] ,                    [Slot ID] ,                    [AllocUnitId] ,
                    Context            UNION
            SELECT  [PAGE ID] ,                    [Slot ID] ,                    [AllocUnitId] ,                    SUBSTRING([RowLog Contents 0], 15,                              LEN([RowLog Contents 0])) AS [RowLog Contents 0] ,                    CONVERT(INT, SUBSTRING([RowLog Contents 0], 7, 2)) ,
                    Context --,CAST(RIGHT([Current LSN],4) AS INT) AS [Current LSN]
            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_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, 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 )                         
            /****************************************/
 
    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,                                               CHARINDEX(':',                                                         @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, 1, 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 ,                                    @AllocUnitID ,                                    [ParentObject] ,                                    [Object] ,                                    [Field] ,                                    [Value]
                            FROM    @temppagedata
                END
            ELSE
                IF @Context = 'LCX_TEXT_MIX'
                    BEGIN
                        INSERT  INTO @ModifiedRawData
                                SELECT  @ConsolidatedPageID ,                                        @fileid ,                                        @pageid ,                                        @Slotid ,                                        @AllocUnitID ,                                        NULL ,                                        0 ,                                        CONVERT(INT, CONVERT(VARBINARY, REVERSE(SUBSTRING(@LCX_TEXT_MIX,                                                              11, 2)))) ,                                        @LCX_TEXT_MIX ,                                        @LinkID ,                                        0
                    END    
            FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,                @AllocUnitID, @LCX_TEXT_MIX, @LinkID, @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] ,              [Slot ID] ,              [AllocUnitId] ,              [RowLog Contents 0_var] ,              [RowLog Length]
            )            SELECT  [Page ID] ,                    [FILE IDS] ,                    [PAGE IDS] ,                    SUBSTRING([ParentObject],                              CHARINDEX('Slot', [ParentObject]) + 4,
                              ( CHARINDEX('Offset', [ParentObject])                                - ( CHARINDEX('Slot', [ParentObject]) + 4 ) )                              - 2) AS [Slot ID] ,                    [AllocUnitId] ,                    SUBSTRING(( SELECT  REPLACE(STUFF(( SELECT
                                                              REPLACE(SUBSTRING([Value],                                                              CHARINDEX(':',                                                              [Value]) + 1,                                                              CHARINDEX('†',                                                              [Value])                                                              - CHARINDEX(':',                                                              [Value])), '†',                                                              '')                                                        FROM  @pagedata C                                                        WHERE B.[Page ID] = C.[Page ID]
                                                              AND SUBSTRING(B.[ParentObject],                                                              CHARINDEX('Slot',
                                                              B.[ParentObject])                                                              + 4,
                                                              ( CHARINDEX('Offset',
                                                              B.[ParentObject])                                                              - ( CHARINDEX('Slot',
                                                              B.[ParentObject])                                                              + 4 ) )) = SUBSTRING(C.[ParentObject],                                                              CHARINDEX('Slot',
                                                              C.[ParentObject])                                                              + 4,
                                                              ( CHARINDEX('Offset',
                                                              C.[ParentObject])                                                              - ( CHARINDEX('Slot',
                                                              C.[ParentObject])                                                              + 4 ) ))                                                              AND [Object] LIKE '%Memory Dump%'
                                                        ORDER BY '0x'
                                                              + LEFT([Value],                                                              CHARINDEX(':',                                                              [Value]) - 1)                                                      FOR
                                                        XML PATH('')
                                                      ), 1, 1, ''), ' ', '')
                              ), 1, 20000) AS [Value] ,                    SUBSTRING(( SELECT  '0x'
                                        + REPLACE(STUFF(( SELECT
                                                              REPLACE(SUBSTRING([Value],                                                              CHARINDEX(':',                                                              [Value]) + 1,                                                              CHARINDEX('†',                                                              [Value])                                                              - CHARINDEX(':',                                                              [Value])), '†',                                                              '')                                                          FROM
                                                              @pagedata C                                                          WHERE
                                                              B.[Page ID] = C.[Page ID]
                                                              AND SUBSTRING(B.[ParentObject],                                                              CHARINDEX('Slot',
                                                              B.[ParentObject])                                                              + 4,
                                                              ( CHARINDEX('Offset',
                                                              B.[ParentObject])                                                              - ( CHARINDEX('Slot',
                                                              B.[ParentObject])                                                              + 4 ) )) = SUBSTRING(C.[ParentObject],                                                              CHARINDEX('Slot',
                                                              C.[ParentObject])                                                              + 4,
                                                              ( CHARINDEX('Offset',
                                                              C.[ParentObject])                                                              - ( CHARINDEX('Slot',
                                                              C.[ParentObject])                                                              + 4 ) ))                                                              AND [Object] LIKE '%Memory Dump%'
                                                          ORDER BY '0x'
                                                              + LEFT([Value],                                                              CHARINDEX(':',                                                              [Value]) - 1)                                                        FOR
                                                          XML PATH('')
                                                        ), 1, 1, ''), ' ', '')
                              ), 7, 4) AS [Length]
            FROM    @pagedata B            WHERE   [Object] LIKE '%Memory Dump%'
            GROUP BY [Page ID] ,                    [FILE IDS] ,                    [PAGE IDS] ,                    [ParentObject] ,                    [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]"),0))',                                                        '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, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],                                                              19 + 14, 2))))                                            AND A.[Link ID] = B.[Link ID]
            LEFT JOIN @ModifiedRawData C ON C.[Page IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],                                                              27 + 14, 2))))                                            AND C.[File IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],                                                              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, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],                                                              15 + 14, 2))))                                            AND A.[File IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],                                                              19 + 14, 2))))                                            AND A.[Link ID] <> B.[Link ID]
                                            AND B.[Update] = 0
            LEFT JOIN @ModifiedRawData C ON C.[Page IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],                                                              27 + 14, 2))))                                            AND C.[File IDS] = CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING(B.[RowLog Contents 0],                                                              31 + 14, 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],                                                        15, [RowLog Len])                                         WHEN [RowLog Len] < 8000
                                         THEN SUBSTRING([RowLog Contents 0],                                                        15 + 6,                                                        CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([RowLog Contents 0],                                                              15, 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, CONVERT(VARBINARY(MAX), 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, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],                                                              5, 4)))) = B.[PAGE IDS]
                                            AND B.[Link ID] = 0
            LEFT JOIN @ModifiedRawData C ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],                                                              17, 4)))) = C.[PAGE IDS]
                                            AND C.[Link ID] = 0
    WHERE   [System_Type_Id] IN ( 99, 167, 175, 231, 239, 241, 165, 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] 
    --,A.[Update]=A.[Update]+1
    FROM    @ColumnNameAndData A            INNER JOIN @ModifiedRawData B ON CONVERT(INT, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],                                                              9, 4)))) = [PAGE IDS]
                                             AND CONVERT(INT, SUBSTRING([hex_value],                                                              3, 2)) = [Link ID]
    WHERE   [System_Type_Id] IN ( 35, 34, 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, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],                                                              9, 4)))) = [PAGE IDS]
    WHERE   [System_Type_Id] IN ( 35, 34, 99 )            AND [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, CONVERT(VARBINARY(MAX), REVERSE(SUBSTRING([hex_value],                                                              15, 4)))) = [PAGE IDS]
    WHERE   [System_Type_Id] IN ( 35, 34, 99 )            AND [Link ID] = 0
 
    UPDATE  @ColumnNameAndData
    SET     [hex_value] = 0xFFFE + SUBSTRING([hex_value], 9, 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), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(hex_Value)))) --SMALL INTEGER
                         WHEN system_type_id = 56
                         THEN CONVERT(VARCHAR(MAX), CONVERT(INT, 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, CONVERT(VARBINARY(8000), REVERSE(hex_Value))), 100) --SMALL DATETIME
                         WHEN system_type_id = 108
                         THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(38, 20), CONVERT(VARBINARY, 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, 20), CONVERT(VARBINARY, CONVERT(VARBINARY(1), xprec)                              + CONVERT(VARBINARY(1), xscale))                              + CONVERT(VARBINARY(1), 0) + hex_Value)) --- DECIMAL
                         WHEN system_type_id IN ( 60, 122 )                         THEN CONVERT(VARCHAR(MAX), CONVERT(MONEY, CONVERT(VARBINARY(8000), REVERSE(hex_Value))), 2) --MONEY,SMALLMONEY
                         WHEN system_type_id = 104
                         THEN CONVERT(VARCHAR(MAX), CONVERT (BIT, CONVERT(BINARY(1), 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,                                                    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)')  --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,                                                    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)') --TIMESTAMP
                         WHEN system_type_id = 98
                         THEN ( CASE WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,                                                              1)) = 56
                                     THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(SUBSTRING(hex_Value,                                                              3,                                                              LEN(hex_Value))))))  -- INTEGER
                                     WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,                                                              1)) = 108
                                     THEN CONVERT(VARCHAR(MAX), CONVERT(NUMERIC(38,                                                              20), CONVERT(VARBINARY(1), SUBSTRING(hex_Value,                                                              3, 1))                                          + CONVERT(VARBINARY(1), SUBSTRING(hex_Value,                                                              4, 1))                                          + CONVERT(VARBINARY(1), 0)                                          + SUBSTRING(hex_Value, 5,                                                      LEN(hex_Value)))) --- NUMERIC
                                     WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,                                                              1)) = 167
                                     THEN LTRIM(RTRIM(CONVERT(VARCHAR(MAX), SUBSTRING(hex_Value,                                                              9,                                                              LEN(hex_Value))))) --VARCHAR,CHAR
                                     WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,                                                              1)) = 36
                                     THEN CONVERT(VARCHAR(MAX), CONVERT(UNIQUEIDENTIFIER, SUBSTRING(( hex_Value ),                                                              3, 20))) --UNIQUEIDENTIFIER
                                     WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,                                                              1)) = 61
                                     THEN CONVERT(VARCHAR(MAX), CONVERT(DATETIME, CONVERT(VARBINARY(8000), REVERSE(SUBSTRING(hex_Value,                                                              3,                                                              LEN(hex_Value))))), 100) --DATETIME
                                     WHEN CONVERT(INT, SUBSTRING(hex_Value, 1,                                                              1)) = 165
                                     THEN '0x'
                                          + SUBSTRING(( 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)'),                                                      11, 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('')
                           ), 1, 1, '') 
--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

 

恢复你的数据

--恢复数据,不加时间段条件  参数:数据库名,表名--EXAMPLE #1 : FOR ALL DELETED RECORDSEXEC Recover_Deleted_Data_Proc 'test','dbo.aa'GO--恢复数据,加时间段条件--EXAMPLE #2 : FOR ANY SPECIFIC DATE RANGEEXEC 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存储结构之堆表、行溢出

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

4
 

  

 

步骤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 AStatus Bit B +[Fixed Length Data] +1, [Null Bitmap length] )

  • Total no of variable columns = Substring (RowLog content 0, Status Bit AStatus Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 )

  • Column Offset Array= Substring (RowLog content 0, Status Bit AStatus 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 ,NCHARWHEN 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), CONVERT(TINYINT, CONVERT(BINARY(1), REVERSE (hex_Value)))) 
--SMALL INTEGER
 WHEN system_type_id = 52 THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (hex_Value)))) 
-- INTEGER
 WHEN system_type_id = 56 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(hex_Value)))) 
-- BIG INTEGER
 WHEN system_type_id = 127 THEN CONVERT(VARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(8), REVERSE(hex_Value)))) 
--DATETIME
 WHEN system_type_id = 61 Then CONVERT(VARCHAR(Max),CONVERT(DATETIME,Convert(VARBINARY(max),REVERSE (hex_Value))),100) 
--SMALL DATETIMEWHEN system_type_id =58 Then CONVERT(VARCHAR(Max),CONVERT(SMALLDATETIME,CONVERT(VARBINARY(MAX),REVERSE(hex_Value))),100) --SMALL DATETIME
 --- NUMERIC
 WHEN system_type_id = 108 THEN CONVERT(VARCHAR(MAX), CAST(CONVERT(NUMERIC(18,14), CONVERT(VARBINARY,CONVERT(VARBINARY,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,Convert(VARBINARY,xprec)+CONVERT(VARBINARY,xscale))+CONVERT(VARBINARY(1),0) + hex_Value) as FLOAT)) 
-- BIT
 WHEN system_type_id = 104 THEN CONVERT(VARCHAR(MAX),CONVERT (BIT,CONVERT(BINARY(1), 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))),53,LEN(hex_Value)))) 
--REALWhen  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),23,23)),8) 
--BINARY,VARBINARYWHEN 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),CONVERT(UNIQUEIDENTIFIER,hex_Value))

 

 

步骤7:

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

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


我的测试

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

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

 

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

Msg 9420, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 651XML parsing: line 1, character 2, illegal xml character

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

 

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

--Recover the deleted data without date rangeEXEC Recover_Deleted_Data_Proc 'test','dbo.Test_Table'GO--Recover the deleted data it with date rangeEXEC 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-04-23','2014-10-23'--DROP TABLE testdaterecoverCREATE TABLE testdaterecover(dd DATE,cc DATETIME)GOINSERT INTO [dbo].[testdaterecover]
        ( [dd], [cc] )VALUES  ( GETDATE(), -- dd - date
          GETDATE()  -- cc - datetime          )SELECT CONVERT(VARCHAR(MAX),CONVERT(DATETIME, CONVERT(VARBINARY(8000),[cc])),120) FROM [dbo].[testdaterecover]DELETE [dbo].[testdaterecover]THEN CONVERT(VARCHAR(MAX), CONVERT(DATETIME, CONVERT(VARBINARY(8000), REVERSE(hex_Value))), 121) --DATETIME

 

Leave a Reply