Recover lost data from .LDF file

Question

Background: Using Business Contacts Manager 2010 with SQL 2008. Master database is hosted through SQL and each computer connects. When the connection is stopped, each computer switches to local copy of the database. When connection is available again, the changes are synced into the master database.

Issue: When syncing the data from local to master, an error occured and all records after the error didn't trasfer the changes and erased the changes from the local database. Is there any way to retrieve the data that was changed only on the local machine before the sync operation occured?

A transaction log LDF file of the local database copy is the only thing that may have that data in it but didn't have any luck retrieving the data so far. No local backups were made prior to the incident. The only way I believe this can be done is through the LDF file unless there is a way to roll back the local copy to a previous date even without a backup log file being created.

---

Notes: The last backup of the local machine database copy was from October 2011. The master database from the server was backed up much more recently but the information that I'm trying to retrieve is in the "SQL Serve Database Transactions Log File" aka the .LDF file.

So if I restore the local database to the backup from October 2011 could I redo all changes since and up until when the data was lost using the .LDF file? I don't think any recovery mode was ever set or changed from the default settings. Also, the database is not very large, only 500MB, and the data that was lost was only from a notes field and a call-date field for around 100 contacts so maybe its not worth all that work.

Answer 1

You need to restore the last full backup, then all transaction logs in sequence, issuing a STOP AT for the final transaction log restore at a time prior to the loss of data. See this reference.

Answer 2

I'm a bit late to the party, but here we go:

If the local database is in SIMPLE recovery, you're basically cooked.

If the local database is in BULK_LOGGED or FULL, you have a chance if there's an existing full backup from before the data loss.

The first thing you need to do is take a transaction log backup to back up all the transactions that have not yet been backed up. DO NOT RESTORE A FULL BACKUP OVER THE EXISTING DATABASE UNTIL YOU DO THIS -- this is your only chance of retrieving the data.

After you've taken the transaction log backup, restore the full backup, and then the transaction log backup you just took with STOPAT to get back to the point in time just before the deletion. Hopefully the transaction log backup covers the entire LSN range since the full backup...

Restore a Transaction Log Backup (MSDN)