大家對本人之前的文章《恢復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