你的位置:首页 > 数据库

[数据库]恢复SQL Server被误删除的数据(再扩展)


恢复SQL Server被误删除的数据(再扩展)

 

大家对本人之前的文章《恢复SQL Server被误删除的数据》 反应非常热烈,但是文章里的存储过程不能实现对备份出来的日志备份里所删数据的恢复

这个是一个缺陷,本人决定对这个存储过程扩展一下,支持对log backup文件里的delete语句进行恢复

 

实验步骤

 

1、首先先准备好测试表和测试语句

USE [sss]GO--建表CREATE TABLE testdelete  (   id INT IDENTITY(1, 1)       NOT NULL       PRIMARY KEY ,   NAME VARCHAR(200) ,   dt DATETIME  )--插入数据INSERT [dbo].[testdelete]    ( [NAME], [dt] )VALUES ( 'aa', -- NAME - varchar(200)     '2015-07-04 07:06:40' -- dt - datetime     )SELECT * FROM  [dbo].[testdelete]--删除数据DELETE FROM [dbo].[testdelete]

 

2、删除数据之后对数据库进行日志备份

DECLARE @CurrentTime VARCHAR(50) ,  @FileName VARCHAR(200)SET @CurrentTime = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120),                      '-', '_'), ' ', '_'), ':', '') SET @FileName = 'c:\sss_logBackup_' + @CurrentTime + '.bak'BACKUP LOG [sss]TO DISK=@FileName WITH FORMAT 

 

4、建立存储过程

-- Script Name: Recover_Deleted_Data_BylogBackup_Proc-- Script Type : Recovery Procedure -- Develop By: Steven Lam-- Date Created: 03 July 2015-- Version  : 1.0-- Notes : Included BLOB data types for recovery.& Compatibile with Default , CS collation , Arabic_CI_AS. USE [sss]GOCREATE PROCEDURE Recover_Deleted_Data_BylogBackup_Proc  @Database_Name NVARCHAR(MAX) ,  @SchemaName_n_TableName NVARCHAR(MAX) ,  @Backuppath NVARCHAR(2000),  @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  IF ( @Backuppath IS NULL     OR @Backuppath = ''    )    BEGIN      RAISERROR('The parameter @Backuppath can not be null!',16,1)      RETURN    END   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   fn_dump_dblog(NULL, NULL, N'DISK', 1, @Backuppath,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT)          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  fn_dump_dblog(NULL, NULL, N'DISK', 1,                         @Backuppath, DEFAULT,                         DEFAULT, DEFAULT, DEFAULT,                         DEFAULT, DEFAULT, DEFAULT,                         DEFAULT, DEFAULT, DEFAULT,                         DEFAULT, DEFAULT, DEFAULT,                         DEFAULT, DEFAULT, DEFAULT,                         DEFAULT, DEFAULT, DEFAULT,                         DEFAULT, DEFAULT, DEFAULT,                         DEFAULT, DEFAULT, DEFAULT,                         DEFAULT, DEFAULT, DEFAULT,                         DEFAULT, DEFAULT, DEFAULT,                         DEFAULT, DEFAULT, DEFAULT,                         DEFAULT, DEFAULT, DEFAULT,                         DEFAULT, DEFAULT, DEFAULT,                         DEFAULT, DEFAULT, DEFAULT,                         DEFAULT, DEFAULT, DEFAULT,                         DEFAULT, DEFAULT, DEFAULT,                         DEFAULT, DEFAULT, DEFAULT,                         DEFAULT, DEFAULT, DEFAULT,                         DEFAULT, DEFAULT, DEFAULT,                         DEFAULT, DEFAULT, DEFAULT,                         DEFAULT, DEFAULT)              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                             '')                            ), 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                               '')                               ), 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  fn_dump_dblog(NULL, NULL, N'DISK', 1, @Backuppath, DEFAULT,                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,                 DEFAULT, DEFAULT)      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  fn_dump_dblog(NULL, NULL, N'DISK', 1, @Backuppath,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT)          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  fn_dump_dblog(NULL, NULL, N'DISK', 1, @Backuppath, DEFAULT,                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,                 DEFAULT, DEFAULT)      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  fn_dump_dblog(NULL, NULL, N'DISK', 1, @Backuppath,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT, DEFAULT,                     DEFAULT, DEFAULT, DEFAULT)          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 '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                            '')                           ), 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                             '')                            ), 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 'xs:hexBinary(substring(sql:column("[RowLog Length]"),0))',                               'varbinary(Max)')))  FROM  @ModifiedRawData  WHERE  [LINK ID] = 0   UPDATE @ModifiedRawData  SET   [RowLog Contents 0] = CAST('' AS '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 'xs:hexBinary(sql:column("hex_Value"))',                               'VARBINARY(8000)')) = 0                   THEN '0x'                   ELSE ''                END ) + CAST('' AS 'xs:hexBinary(sql:column("hex_Value"))',                               'varchar(max)') -- BINARY,VARBINARY             WHEN system_type_id = 34             THEN ( CASE WHEN CHARINDEX(0x,                          CAST('' AS 'xs:hexBinary(sql:column("hex_Value"))',                               'VARBINARY(8000)')) = 0                   THEN '0x'                   ELSE ''                END ) + CAST('' AS '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(--             WHEN system_type_id = 189             THEN ( CASE WHEN CHARINDEX(0x,                          CAST('' AS 'xs:hexBinary(sql:column("hex_Value"))',                               'VARBINARY(8000)')) = 0                   THEN '0x'                   ELSE ''                END ) + CAST('' AS '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 'xs:hexBinary(sql:column("hex_Value"))',                               'VARBINARY(8000)')) = 0                               THEN '0x'                               ELSE ''                            END )                           + CAST('' AS '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               '')              ), 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

View Code

 

5、在C盘看到备份出来的日志备份文件

 

 

6、存储过程的两种调用方式

--存储过程依然保持着两种调用方式EXEC Recover_Deleted_Data_BylogBackup_Proc 'sss','dbo.testdelete',N'C:\sss_logBackup_2015_07_04_150756.BAK'GOEXEC Recover_Deleted_Data_BylogBackup_Proc 'sss','dbo.testdelete',N'C:\sss_logBackup_2015_07_04_150756.BAK','2012-06-01','2016-06-30'GO

 

可以看到被误删的那条数据.

 


为什麽要扩展这个存储过程

 

因为进行了日志备份之后,原先的delete语句的log record可能已经不存在于ldf文件里面,这时候要恢复被那条delete语句删除的数据就需要读取

log backup文件里面的log record来进行恢复

 

对比图

Recover_Deleted_Data_Proc

Recover_Deleted_Data_BylogBackup_Proc

 

因为很多公司为了数据安全,一般会对数据库半个小时或者15分钟做一次日志备份,目的是做日志传送或者及时把数据库备份拷走

考虑到这种情况,本人决定扩展一下这个存储过程,其实原理也很简单,就是把fn_dblog存储过程替换为fn_dump_dblog存储过程

 

在文章《恢复SQL Server被误删除的数据》发布之后,有很多人通过QQ咨询我,为什麽执行了你的存储过程之后会显示

There is no data in the log as per the search criteria

 

我发现原因基本上有两个:

(1)数据库从来没有做过完整备份

(2)因为数据库实例下有一个job定时对数据库做日志备份,误删除了数据之后(误执行delete语句之后),刚好那个job到达运行时间,对数据库进行了日志备份

 

对于第一种情况,可以参考本人写的这篇文章《您真的理解了SQLSERVER的日志链了吗?》,文章里面有解释

对于第二种情况,可以使用本篇文章的方法,回想一下delete语句的执行时间,然后指定日志备份的备份文件进行恢复

--指定日志备份的备份文件EXEC Recover_Deleted_Data_BylogBackup_Proc 'sss','dbo.testdelete',N'C:\sss_logBackup_2015_07_04_150756.BAK'GO

 

 

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