Tuesday, December 16, 2008

Recovering Deleted Data in SQL Server


  • Somebody just executed an Update/Delete and forgot the WHERE clause.
  • Somebody just dropped a table that shouldn’t have been dropped.
  • I didn’t think to validate input into my applications, and some adventurous hacker decided to “optimize” my database.

And to a lesser extent:

Note: If you are experiencing database consistency issues (physical data corruption), this is NOT the best place to look for answers. Not that I wouldn’t love to help you, but there are much better data corruption resources on the web. For instance:

In Recovery (Paul Randall’s Blog) – Certainly the most knowledgeable SQL Server professional I’ve ever read, with respect to disaster recovery.

The MSDN Disaster Recovery and Availability Forum – Lots of excellent folks, including Paul, contribute here.

Sooner or later, every SQL Server professional (or every database administrator, in the least) will need to deal with data that has been deleted, incorrectly updated, or otherwise abused. Every so often (read: at least once a week), somebody posts a question on the MDSN Forums asking for help with this very topic. There are a number of ways to deal with these issues (I’ll refer to them hereafter as “deleted data issues” for brevity). The most common options are detailed below. Please read through EACH option before deciding upon a particular recovery strategy, as some are better suited to certain situations than others.

ROLLBACK the transaction

The simplest way to “undo” a deleted data issue is to roll back the transaction. This only works if the offending user explicitly opened up a transaction prior to executing the update or delete statement. Which leads me to my next point: ALWAYS perform ad-hoc data updates inside an explicit transaction and its natural precursor: NEVER perform an untested data modification on a production system. A data modification which can be reversed in this manner often resembles the following:

-- Explicitly open up a transaction

-- Issue an update
UPDATE Person.Contact
SET FirstName = 'Aaron'

-- Uh-oh
SELECT SinkingFeeling
FROM ThePitOfMyStomach
WHERE Cause = 'Unqualified Update'

-- ROLLBACK saves the day!

-- Check to make sure that the transaction was properly rolled back
SELECT FirstName
FROM Person.Contact

-- WHEW!
WHERE Cause = 'Unqualified Update'

Restore the last good backup under a new name, and use it to copy the affected rows back into your production database

If you have a backup which contains the data as it existed PRIOR to the erroneous modification, you can restore this database under a new name, and use three-part names to copy the deleted or updated information back across. For instance, deleted rows would be “restored” in this manner:

INSERT          ProductionDB.dbo.MyTable
FROM RestoredDB.dbo.MyTable AS r
LEFT OUTER JOIN ProductionDB.dbo.MyTable AS p
ON r.KeyField = p.KeyField AND r.OtherKeyField = p.OtherKeyField
AND p.KeyField IS NULL

**This is a left-anti-semi-join, and it’s darn handy. I wrote a Wiki Article on the topic in case you’re interested.

Improperly updated rows would be “fixed” like this:

UPDATE          ProductionDB.dbo.MyTable
SET MyField = r.MyTable
FROM RestoredDB.dbo.MyTable AS r
JOIN ProductionDB.dbo.MyTable AS p
ON r.KeyField = p.KeyField AND r.OtherKeyField = p.OtherKeyField

If you don’t have a backup, but you are maintaining another copy of your database via log shipping (with a suitable restore delay), you may be able to use your shipped database to do the same thing.

Restore the last good backup over your production database

IF possible. If you were fortunate enough to be doing this update or delete after hours (or when the database was not in use), and you backed up your database (or took a Database Snapshot) immediately prior to mucking the data up, you can restore the backup/snapshot to quickly set the data back to it’s original state. This WILL wipe out all activity since the backup was taken (unless you’re employing more advanced backup strategies, such as filegroup backups), so please USE THIS WITH CAUTION. Trust me, try as you might, you won’t save your job or your reputation by recovering the lost rows and wiping out a day’s transactions in the process.

Backup and restore the transaction log using the STOPAT clause

If your database is set to use the Full Recovery Model, you may have a backup and restore option, even if you didn’t take a backup prior to the data modification. In order to do this:

  1. Backup the tail of the transaction log using normal BACKUP LOG syntax, and specifying WITH NORECOVERY. For further detail see Tail-Log Backups on MSDN.
  2. Restore your most recent full backup, making sure to specify NORECOVERY.
  3. Restore the most recent differential backup, also specifying NORECOVERY.
  4. Restore each transaction log backup taken since the last full or differential backup. For the final log backup, use the STOPAT clause to stop the restore immediately prior to the data modification. Before you access the database, you need to perform recovery, so you can either do it as part of this restore, or as a separate operation. For more information, have a look at Recovering to a Specific Point in Time on MSDN.

This will, of course, wipe out any activity which took place after the time/LSN/mark specified in the STOPAT clause.

Use a log explorer, or similar recovery tool to generate undo scripts

A number of vendors offer tools which allow you to explore the transaction log and generate undo scripts to roll back data modifications. I have had occasion to use any of these utilities myself, however the most commonly recommended utilities are RedGate’s SQL Log Rescue and ApexSQL’s ApexSQL Log. These aren’t plugs – as I said, I haven’t used either of them, so please don’t complain if you do and they don’t work out for you.


I have endeavored in the article above to present a comprehensive listing of the methods available to recover from the effects of deleted or incorrectly updated data. If you feel that I’ve missed anything, please leave a comment and I’ll promptly revise the article.

No comments: