Find solution
Can I recover lost data from an .mdf or .ldf file?

Recover lost data from .LDF file

  • 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:

    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 http://msdn.microsoft.com/en-us/library/ms179451.aspx.

Matt at Database Administrators Visit the source

Was this solution helpful to you?

Other answers

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... http://msdn.microsoft.com/en-us/library/ms177446.aspx (MSDN)

Jon Seigel

Just to explain why do you need a full database backup and then the full chain of transaction log (LDF) files up to the point of the incident. Unlike INSERT and DELETE operations, which are fully logged in the LDF files, UPDATE operations are logged minimally - only the changes that are made are logged, but the old and new values are not. When logging UPDATE operations, SQL Server doesn’t log complete before and after row states but only the incremental change that occurred to the row. For example, if a word “log” was updated to word “blog” SQL Server will, in general case, only log an addition of letter “b” at index 0. This is enough for its purpose of ensuring ACID but not enough to easily show before and after states of the row. So, in order to understand what changed really occurred, you have to reconstruct the context in which the change occurred from the rest of transaction log and/or backup and online database data

Ivan Stankovic

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.