I had a good friend recently ask me if there was any way to undelete a table that had accidentally been dropped from a database.
My answer was basically, no there isn’t.
Or to be more specific, the answer to that question is equivalent to the answer to this one: “How prepared is the db or the system for disaster recovery?”
In theory there are plenty of options, but they all hinge on how prepared the system was for an event in advance: This could cover a large number of areas including backups, snapshots, mirrors and replicated dbs. Not to mention hardware redundancy solutions that may have been put in place.
Silly question?
But it’s not a silly question. Not at all. There are a number of Undelete programs available for file systems. They all operate under the disclaimer count yourself lucky to get any data back.
But it’s not a silly question. Not at all. There are a number of Undelete programs available for file systems. They all operate under the disclaimer count yourself lucky to get any data back.
So the natural question is: Is there anything fundamentally blocking someone from trying to write an undelete program for tables? With the obvious disclaimers that any data you might get back is dirty as a pickup.
I decided to find out. I created a table with a number of rows and deleted it. The pages of data that used to contain the table are still there, but it’s marked as unused and available. That’s promising, the (dirty) data is still there, it just doesn’t have any structure. Then I looked at the IAM (index allocation map) page. As far as I know, this is the page the db looks at to find out the most fundamental information about the structure and location of an index. This particular page gets zeroed out.
So yes, as I expected, with SQL Server, there is something fundamentally blocking a user or program from performing undelete on a table.
Best Practices
So looks like best practices prevail. Keep data backed up if you need it. Even if it’s a quick and dirty database. A quick and dirty backup takes 5 extra clicks than saving query text to file with “Save As…”.
Good post.
Interestingly Paul Randal recently blogged about how to find out who deleted a table by reviewing the contents of the transaction log
Finding out who dropped a table using the transaction log
Comment by John Sansom — June 30, 2009 @ 5:52 am
Most of these situations come from modifying the data by running adhoc queries and ppl forget that they have an option of using a begin tran without running the commit tran.
or even better, do a select before updating the data.
1) Begin tran and Run the modification.
2) Validate the data with nolock.
3) If everything is right, commit tran
Comment by Sankar — June 30, 2009 @ 4:16 pm
Hi Sankar, there's lots of ways for data to accidentally get deleted. Your method is another way of being prepared. But nothing is foolproof. My friend who asked me the question actually did run the statement in a transaction, except that the transaction was accidentally committed even when everything was not all right.
But this post is not about how to be careful. It's much too short for that. This post is about whether it's possible to recover data after you've discovered you weren't careful enough.
Comment by Michael J. Swart — July 1, 2009 @ 9:14 am
Nice article, Michael.
You may also be interested in my post on Recovering Deleted Data in SQL Server, or the subsequent post on Preventing Accidental Data Modification or Deletion in SQL Server.
Comment by Aaron Alton — July 2, 2009 @ 5:33 am
Good point Aaron,
Your own post made me consider the transaction logs. Maybe there is something in there that can help recover data. The existence of the products you mention certainly make it seem so.
It's almost unfair that transaction logs are only maintained when the recovery model is set to full *and* a backup has been performed (with some exceptions).
Preventing Accidental Data Modification or Deletion in SQL Server is a must-read.
Comment by Michael J. Swart — July 2, 2009 @ 6:32 am
Hey! Was that my question? ha ha
Comment by Christin — July 4, 2009 @ 5:20 am