恢复SQL Server被误删除的数据（再扩展）

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]
GO


CREATE 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, DEFA