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…”.
