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