1

I unintentionally deleted 4 rows from a table. I tried to fetch back the deleted data using a stored procedure (got from internet). However, I was able to see my data in the result of stored procedure. Then I updated the original schema of the table and remove the primary key before restoring the deleted data and again tried to run the stored procedure and found that fn_dblog is no more showing my deleted rows of that table.

All other deleted data from other tables are present in fn_dblog. How can I now fetch my deleted rows without using any external tool? I am using SQL Server 2012.

Edited: I have attached a snapshot as well of ApexSQL. The last 4 rows are my data but i am unable to recover, as the redo script says:

00000088:0000011B:0002: ROW DATA NOT AVAILABLE FOR DELETE ON [UNKNOWN].[UNKNOWN(1394104007)]

I also asked at stack overflow the same question. No responses.

enter image description here

1
  • do you backup your transaction logs? If so you could use fn_dump_dblog to search the tlog backups.
    – Bob Klimes
    Commented Jun 9, 2015 at 14:23

1 Answer 1

2

From Thomas LaRock at http://logicalread.solarwinds.com/sql-server-dbcc-log-command-tl01/#.VXbj_DYpDVk

"The fn_dblog() function (formerly known as the DBCC command) is one of several undocumented functions for SQL Server; it allows you to view the transaction log records in the active part of the transaction log file for the current database."

The fact that you can no longer find your data suggests that the desired part of the log is no longer in the active part of the transaction log file.

One way to get these 4 rows back would be to restore a copy of the database to a temporary database name (e.g. DbName_Disaster) at a point in time that contained these 4 rows. Then insert the 4 rows back into your production database and drop the DbName_Disaster database.

Or you could look for some database recovery tool that fits your need. The prices and features vary.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.