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

backuprecoveryrestoresql serversql-server-2005

We are using SQL 2005 Express Edition. We want to read the data from the LDF file in order to restore some deleted records.

We tried using a trial version of ApexSQL which helped us a lot. Instead of going for third party software, we are trying to figure out how to read the log file ourselves.

How can we read and recover data from the LDF file?

Best Answer

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.

Related Question