SQL Server 2008: Reverting accidental delete/update without recent backup

Tags: sql server, restore, backup

Recently I ran into a problem after accidentally clearing an important column in one of my DB tables. Ooops… I was in a real hurry and haven’t thought about making a backup firstSmutek Just a simple query, but also with simple, nasty error within... First thought – I’m doomed! I’m pretty sure many of you had the same situation onceUśmiech

I had only a week old backup – much has changed since, so it’s not an option to revert the whole week.  So I started digging for a solution… And found one!Uśmiech My DB is running in Full Recovery mode, so maybe there’s a chance (your DB has to run in this mode too for this solution to work):

  1. Create a Transaction Log-only backup now (after the accident)
  2. Restore the old backup (week old in my situation) using RESTORE WITH NORECOVERY option. This option can be selected in Restore Wizard –> Options screen (or specified in restoration script). This ensures that DB will be restored from backup and won’t allow outside connections until we’re finished the whole restoration process.
  3. Restore the Transaction Log backup (created in 1.) to a point in time before the accidental update/delete happened. You can choose the desired point in time in Restore Wizard, or (if you prefer the scripted way of doing things) use the WITH STOPAT <date> option. This time, choose RESTORE WITH RECOVERY option, to make DB fully operational after restoration process.

You may also need to check WITH REPLACE option in 2. and 3. if your DB shouts with errors.

And everything returns back to normal, phew…Uśmiech HTH!

Cheers

blog comments powered by Disqus