How to fix a stored procedure that can recover deleted data-CodePudding

I have lately stumbled upon a blog post that talks about a stored procedure called Recover_Deleted_Data_Proc.sql that can apparently recover your deleted data from the .log file.

There is nothing new under the sun, we are going to use fn_dblog.

STEPS TO REPRODUCE

We are first going to create the table:

--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
)

And we then insert data into it:

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

We are now going to verify if the data are there:

--Verify the data
SELECT * FROM Test_Table

At this point we need to create the stored procedure. I couldn't paste it here because it's too long but you can download it from the same blog post there is a link to a Box file.

If the query gives you troubles like this:

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)

Is because you have to comment out from line 701 to line 708.

Cool, let's now delete the data from that table:

--Delete the data
DELETE FROM Test_Table

And confirm that the data were deleted:

--Verify the data
SELECT * FROM Test_Table

And here is the last step: we need to try to recover the data using the freshly installed stored procedure.

The author instruct us to use one of these two commands (don't forget to change 'test' with the name of your database):

--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'

But the problem is that both returns this error:

(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.

If I right click on the stored procedure and I click "Modify", I don't see anything particularly fishy at Line 485.

Any idea why this stored procedure is not working?

What is the conversion mentioned?

CodePudding user response：

The code is 10 years old and was written with the assumption that a [PAGE ID] would only ever be expressed as a pair of integers, e.g. 0001:00000138 - however, as you have learned, sometimes that is expressed differently, like 0x41-->01 ; 0001:00000138.

You can fix that problem by adding this inside the cursor:

IF @ConsolidatedPageID LIKE '0x%-->%;%' 
BEGIN
  SET @ConsolidatedPageID = LTRIM(SUBSTRING(@ConsolidatedPageID, 
       CHARINDEX(';', @ConsolidatedPageID)   1, 8000));
END

But then your next problem is when you saved the procedure from the box file it probably changed '†' to some wacky ? character. When I fixed that (using N'†' of course, since Unicode characters should always have N), I still got these error messages:

Msg 537, Level 16, State 3, Procedure Recover_Deleted_Data_Proc, Line 525
Invalid length parameter passed to the LEFT or SUBSTRING function.
Msg 9420, Level 16, State 1, Procedure Recover_Deleted_Data_Proc, Line 651
XML parsing: line 1, character 2, illegal xml character

After 15 minutes of trying to reverse engineer this spaghetti, I gave up. If you need to recover data you deleted, restore a backup. If you don't have a backup, well, that's why we take backups. The fragile scripts people try to create to compensate for not taking backups are exactly why log recovery vendors charge the big bucks.

As an aside, the compatibility level error message is a red herring, totally misleading as the logic is currently written, and completely irrelevant to the problem. But it can be solved if, right before this:

IF ISNULL(@Compatibility_Level,0)<=80
BEGIN
  RAISERROR('The compatibility level should ... blah blah',16,1)
  RETURN
END

You add this:

IF DB_ID(@Database_Name) IS NULL
BEGIN
  RAISERROR(N'Database %s does not exist.',11,1,@Database_name);
  RETURN;
END

Or simply not calling those two example calls at the end of the script, since they depend on you having a database called test, which clearly you do not.