Recover SQL data from a dropped table without backups

If you’ve accidentally executed a DROP statement and you have a full database backup, given that no changes occurred after the table was dropped, you can simply recover the data by overwriting the original database with the backup. However, if that’s not the case, either because the database objects and data have been changed after the DROP SQL statement was executed or because you don’t have a valid database backup, there is still a way to recover all of your lost data

Namely, each DROP operation is logged to the SQL Server transaction log. This means that the transaction log stores the information required to roll back the transaction and, given that the database is in FULL recovery mode, supports point-in-time recovery from a DROP operation

Since DROP is logged to the transaction log, it can be rolled back if it’s encapsulated within an explicit, active transaction. For instance, the following SQL query:

DROP TABLE Customer

SELECT *
FROM Customer

will result in the Msg 208, Level 16, State 1 error message as the Customer table has been dropped.

However, the following SQL code:

BEGIN TRAN 
DROP TABLE Customer

ROLLBACK 
SELECT *
FROM Customer

will return all of the rows in the Customer table as the DROP operation will be rolled back

However, if the transaction containing the DROP operation is no longer active, for instance since it has been committed, the dropped table cannot be rolled back, and conversely the data it stored will be lost as well. Does that mean that you can’t recover the data lost due to a DROP operation if no full database backups are available?

Fortunately, no. This is where ApexSQL Recover comes into play

ApexSQL Recover is a recovery tool for SQL Server databases which recovers deleted, dropped or truncated data. It recovers objects and data lost due to drop operations and restores both deleted and online BLOBs as files making it ideal for SharePoint recoveries

To recover data lost due to a DROP operation without backups:

1. Start ApexSQL Recover

2. Select the Recover lost data due to a DROP table operation option in the main ribbon

   

3. In the Select the database to recover step:
   • Specify the SQL instance where the database you want to recover the data to resides from the Server drop-down box
   • Specify the authentication method for that SQL instance (and a valid set of credentials if you chose SQL Server authentication)
   • Specify the name of the database which contained the dropped tables in the Database drop-down box

   and click Next

4. In the Are additional data sources available? step, select the No additional transaction logs are available option

   

5. In the Select a recovery action step, select the Save recovery script to file option and specify the path where a T-SQL script that will insert all the lost data when executed will be saved

   

6. In the final step of the recovery wizard, choose whether to recover only data, only table structure, or both table structure and data

   

7. Click Finish to finalize the process

And just like that, you will get your lost data back without having a single SQL database backup. ApexSQL Recover will show the results of the successful recovery and allow inspection or even changes to be made on the recovery script which only needs to be executed from within the tool itself or SQL Server Management Studio (or similar tool) to complete the recovery.