Sql-server – fn_dblog not showing up deleted table data

sql serversql-server-2012transaction-log

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

Best Answer

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.

Related Solutions

Sql-server – How to recover data from the LDF file

You will need a previous Full Backup and any log backups taken since the last Full backup

1) Take a tail of the log backup

BACKUP LOG OldDB TO DISK='C:\OldDB_Tail.trn' WITH NO_TRUNCATE

2) Find Transaction for Deleted Records (Operation will be LOP_DELETE_ROWS for DELETEs and LOP_SET_BITS & LOP_MODIFY_ROW for a TRUNCATE TABLE)

SELECT * FROM fn_dblog(NULL,NULL) WHERE AllocUnitName = 'dbo.YourTableName'

3) Restore Previous Full Back and log backups plus the tail to a new database WITH NORECOVERY and STOPAT='Just Before Begin Time for Transaction'

RESTORE DATABASE NewDB
FROM DISK='C:\Previous_Full_Backup_Of_OldDB.bak'
WITH NORECOVERY,
STOPAT='2012/01/11 11:35AM',
MOVE 'OldDB' TO 'C:\DATABASES\NewDB.mdf',
MOVE 'OldDB_Log' TO 'C:\DATABASES\NewDB.ldf'

RESTORE LOG NewDB
FROM DISK='C:\Previous_TranLog_Backup_Of_OldDB.trn'
WITH NORECOVERY,
STOPAT='2012/01/11 11:35AM'

RESTORE LOG NewDB
FROM DISK='C:\OldDB_Tail.trn'
WITH NORECOVERY,
STOPAT='2012/01/11 11:35AM'

-- This recovers the restored database and allows access
RESTORE DATABASE NewDB WITH RECOVERY

This blog post covers in more detail finding the time to STOPAT using fn_dblog. This blog post has a go at recovering data directly from the log but this might be very time consuming compared to a point in time recovery.

Also this blog post by Robert L Davis has an example on combining STOP and STANDBY to allow you to query the state of the databases at different points in time.

Sql-server – Is it possible to recover day’s worth of queries from sql server log file

Your only real chance is using a 3rd-party log analyzer tool, and even that may not work. SIMPLE recovery truncates the log when a CHECKPOINT process occurs, which is highly likely to have happened at this point.

Also, if losing the entire set of changes between backups is unacceptable, either back up more frequently in SIMPLE (if your database is small), or switch to FULL and take log backups as well to get point-in-time recovery ability. Corrupt backups notwithstanding.

Best Answer

Related Solutions

Sql-server – How to recover data from the LDF file

Sql-server – Is it possible to recover day’s worth of queries from sql server log file

Related Question