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.

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.

share|improve this question
1  
Are you using full or simple recovery mode? –  JNK Apr 24 '12 at 20:47
add comment

3 Answers

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.

share|improve this answer
1  
+1 for the general case but it sounds like the OP in this case doesn't have database backups or log backups (and we're not even sure if he is using the right recovery model). –  Aaron Bertrand Apr 24 '12 at 22:45
    
Thanks Aaron. I'm hoping he meant a trn backup of an LDF which would hopefully lead us to a bak sitting around somewhere :) –  Eric Higgins Apr 24 '12 at 23:09
    
Let's hope. I'm thinking it's unlikely. –  Aaron Bertrand Apr 24 '12 at 23:22
add comment

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)

share|improve this answer
add comment

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

share|improve this answer
add comment

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.