Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Is the database using the full recovery model or just simple? –  MartinC Jan 12 '12 at 16:46
    
Yes, we use Full Recovery Model –  goofyui Jan 12 '12 at 16:52
    
We are able to recover data using ApexSQL Trial Version. Trial version allows only to view the data. We dont have budget to buy a Full Edition and so trying to figure it out by ourself to recover the data from .ldf file –  goofyui Jan 12 '12 at 16:57

1 Answer 1

up vote 10 down vote accepted

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.

share|improve this answer
    
What about Truncate Records ? I assume some of them are Truncated also ..!! –  goofyui Jan 12 '12 at 17:57
    
1) How to take .trn from my old database. I got .ldf, .mdf !! I dont have transaction log file. –  goofyui Jan 12 '12 at 18:42
    
The idea here is to recover the database to the point before the data was lost and then you will be able to make a copy of the lost data. The BACKUP LOG command is what creates the trn file from the active portion of the transaction log (ldf) –  MartinC Jan 12 '12 at 19:02
    
I find difficult to understand your point completely. Can you please share the syntax for proceeding .. like keeping it as OldDB where accident happened and NewDB to which the OldDB log file is going to be restored .. –  goofyui Jan 12 '12 at 19:14
    
I've added some more detail and links to blogs for finding the point in time to recover to and another blog on using the fn_dblog's output to regenerate deleted rows. –  MartinC Jan 12 '12 at 19:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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