恢复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]
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 ,
[