如何修复Recover_Deleted_Data_Proc存储过程恢复删除数据时报错问题

近期看到一篇博文提到名为Recover_Deleted_Data_Proc.sql的存储过程，可通过.log文件恢复SQL Server中已删除的数据，该方案底层依赖fn_dblog实现，按步骤操作后出现类型转换报错，具体内容及分析如下：

复现步骤 #

• 步骤1：创建测试表

--Create Table
CREATE TABLE [Test_Table]
(
    [Col_image] image,
    [Col_text] text,
    [Col_uniqueidentifier] uniqueidentifier,
    [Col_tinyint] tinyint,
    [Col_smallint] smallint,
    [Col_int] int,
    [Col_smalldatetime] smalldatetime,
    [Col_real] real,
    [Col_money] money,
    [Col_datetime] datetime,
    [Col_float] float,
    [Col_Int_sql_variant] sql_variant,
    [Col_numeric_sql_variant] sql_variant,
    [Col_varchar_sql_variant] sql_variant,
    [Col_uniqueidentifier_sql_variant] sql_variant,
    [Col_Date_sql_variant] sql_variant,
    [Col_varbinary_sql_variant] sql_variant,
    [Col_ntext] ntext,
    [Col_bit] bit,
    [Col_decimal] decimal(18,4),
    [Col_numeric] numeric(18,4),
    [Col_smallmoney] smallmoney,
    [Col_bigint] bigint,
    [Col_varbinary] varbinary(Max),
    [Col_varchar] varchar(Max),
    [Col_binary] binary(8),
    [Col_char] char,
    [Col_timestamp] timestamp,
    [Col_nvarchar] nvarchar(Max),
    [Col_nchar] nchar,
    [Col_xml] xml,
    [Col_sysname] sysname
)

• 步骤2：向测试表插入数据

--Insert data into it
INSERT INTO [Test_Table]
           ([Col_image]
           ,[Col_text]
           ,[Col_uniqueidentifier]
           ,[Col_tinyint]
           ,[Col_smallint]
           ,[Col_int]
           ,[Col_smalldatetime]
           ,[Col_real]
           ,[Col_money]
           ,[Col_datetime]
           ,[Col_float]
           ,[Col_Int_sql_variant]
           ,[Col_numeric_sql_variant]
           ,[Col_varchar_sql_variant]
           ,[Col_uniqueidentifier_sql_variant]
           ,[Col_Date_sql_variant]
           ,[Col_varbinary_sql_variant]
           ,[Col_ntext]
           ,[Col_bit]
           ,[Col_decimal]
           ,[Col_numeric]
           ,[Col_smallmoney]
           ,[Col_bigint]
           ,[Col_varbinary]
           ,[Col_varchar]
           ,[Col_binary]
           ,[Col_char]
           ,[Col_nvarchar]
           ,[Col_nchar]
           ,[Col_xml]
           ,[Col_sysname])
     VALUES
           (CONVERT(IMAGE,REPLICATE('A',4000))
           ,REPLICATE('B',8000)
           ,NEWID()
           ,10
           ,20
           ,3000
           ,GETDATE()
           ,4000
           ,5000
           ,getdate()+15
           ,66666.6666
           ,777777
           ,88888.8888
           ,REPLICATE('C',8000)
           ,newid()
           ,getdate()+30
           ,CONVERT(VARBINARY(8000),REPLICATE('D',8000))
           ,REPLICATE('E',4000)
           ,1
           ,99999.9999
           ,10101.1111
           ,1100
           ,123456
           ,CONVERT(VARBINARY(MAX),REPLICATE('F',8000))
           ,REPLICATE('G',8000)
           ,0x4646464
           ,'H'
           ,REPLICATE('I',4000)
           ,'J'
           ,CONVERT(XML,REPLICATE('K',4000))
           ,REPLICATE('L',100)
           )
 
GO

• 步骤3：验证测试数据存在

--Verify the data
SELECT * FROM Test_Table

• 步骤4：下载并创建存储过程
  若执行创建语句时出现如下兼容性报错：

Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 22 [Batch Start Line 700]   The compatibility level should be equal to or greater SQL SERVER 2005 (90)

Msg 50000, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 22 [Batch Start Line 705]   The compatibility level should be equal to or greater SQL SERVER 2005 (90)

将存储过程代码701行至708行注释即可解决。

• 步骤5：删除测试表数据并验证

--Delete the data
DELETE FROM Test_Table
--Verify the data
SELECT * FROM Test_Table

• 步骤6：调用存储过程恢复数据
  两种调用语句如下（需将test替换为实际数据库名）：

--Recover the deleted data without date range
EXEC Recover_Deleted_Data_Proc 'test', 'dbo.Test_Table'

或

--Recover the deleted data it with date range
EXEC Recover_Deleted_Data_Proc 'test', 'dbo.Test_Table', '2012-06-01', '2012-06-30'

两种调用方式均返回如下报错：

(8 rows affected)

(2 rows affected)

(64 rows affected)

(2 rows affected)

(1 row affected)

(1 row affected)

(1 row affected)

(1 row affected)

(1 row affected)

(1 row affected)

Msg 245, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 485 [Batch Start Line 112]   
Conversion failed when converting the varchar value '0x41-->01 ; 0001' to data type int.

问题原因分析 #

转换报错的具体含义 #

该错误是存储过程解析事务日志内容时的类型匹配失败：fn_dblog返回的日志内容为十六进制字符串格式，存储过程在解析特殊类型字段的日志值时，错误地将拼接了类型标识、偏移位的混合字符串（示例中的0x41-->01 ; 0001，前半段为字段原始十六进制值，后半段为日志记录的属性标识）直接尝试转为int类型，触发了转换失败。

存储过程无法正常运行的核心原因 #

1. 版本适配性不足：该开源存储过程仅适配SQL Server 2005~2012的部分版本，更高版本SQL Server的fn_dblog返回的日志结构发生了变化，存储过程的解析逻辑没有对应适配。
2. 特殊类型未兼容：测试表中包含image、sql_variant、xml等特殊数据类型，该存储过程仅对int、varchar、datetime等常规基础类型做了解析适配，未覆盖特殊类型的日志解析逻辑。
3. 兼容性校验被强制跳过：存储过程原生的兼容性校验是为了保证运行环境匹配，强行注释校验逻辑后，高版本SQL Server的日志结构差异会直接触发解析逻辑异常。
4. 恢复模式不满足要求：如果数据库为简单恢复模式，事务日志会被自动截断，fn_dblog无法读取到完整的删除操作日志，也会导致解析过程中出现异常值引发转换错误。

内容的提问来源于stack exchange，提问作者Francesco Mantovani