DROP TABLE Recovery
Every now and then questions arise, that not only data has been deleted, but that a whole table has been dropped. While I still strongly believe that this is mostly an issue of an inappropriate permission concept, I confess that this even happened to me once. For convenience in reporting I use Microsoft Access ADP projects. Obviously I’ve turned off the setting to be asked before an object is dropped at some point in time before. Now that day the fingers were quicker than the brain and/or the eye, and the table went into data nirvana without a sound. Fortunately it was a lookup table that could easily be recreated, but from that point on I began to understand how tables (or database objects in general) can “accidentally” be dropped. And I’ve turned on the Access setting to be alerted before any object is dropped.
A DROP TABLE operation is recorded in the log in a similar way as a TRUNCATE TABLE operation, so the success of your recovery attempt depends on the intermediate database activity since the DROP statement was fired as the dropped data is always recovered from the online database file(s). The table’s schema is recovered from either the transaction log or the database file, depending on which source you use. Suppose we issue the following statement:
DROP TABLE dbo.Nums
If you have sufficient permissions on dbo.Nums, the query will be executed just fine and the table is gone. To bring it back to life, you use the “Recovery Wizard”:
On the second screen of the wizard, you specify which data source you want to use for the recovery operation.
In this case here, where a table is dropped, you as the DBA certainly won’t have wait very long for the calls from upset users that “something is wrong”, and since you run your databases in full recovery mode anyway, you will choose the “Relevant data is available” option here as you are likely to recover from the online transaction log.
As you can see in this screen, you have also the option to choose to recover from a previously taken log backup or from a detached log. To add a previously taken backup to the Log data source files, click the “Add Backup” button, select the desired file in the file dialog and click ok. Your list of data sources to choose from has changed now and you will be able to select the log backup
DROP < object > Recovery
At this point we must make a slight, but fine distinction. ApexSQL Log in its current version 2005.02 is able to recovery any dropped object when you use it on an SQL Server 2000 instance. When you use it with SQL Server 2005 however, things are a bit different. Currently the software supports the recovery of tables. Enhanced recovery support for objects like stored procedures, views, functions and other will be implemented starting Q1/2007.