恢复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