How to rollback a deleted table data?

I have deleted one table accidentally how can I rollback that. Please suggest that.


Get it from backups!


As Joe Stefanelli said, ApexSQL tools can help. There are actually two tools - if the whole table was dropped - ApexSQL Recover. If only the table records are deleted, both ApexSQL Log and ApexSQL Recover can help.

The limitation of both tools while in trial is that they recover only 1 out of 10 transactions. The good news is that ApexSQL Log shows all the records in the grid even in trial, so you can see the records that can be recovered. The limitation is that you cannot create the undo script for all of them until you license the tool

The tools can bring back the data even if you don't have a full or differential database backup as they read the data (MDF) file (ApexSQL Recover) and transaction log (LDF and trn - online, detached, backups - ApexSQL Log)

4 techniques for recovering lost tables due to DROP Table operation

Disclaimer: I work for ApexSQL as a Support Engineer


If you don't have a good backup, you could give a 3rd party tool like ApexSQL Log a try. I don't know what the limitations on the free trial version are, but it might be worth a shot.


If you have backups then you follow the procedure described in How to retrieve a specific table or rows from database backups or transaction log backups in SQL Server:

  • Back up the current transaction log
    Back up your current transaction log with the NO_TRUNCATE option.

  • Restore a partial or full database backup
    SQL Server 2000 or SQL Server 2005 If your backup strategy includes filegroup backups, you can perform a partial database restore to a different location and restore only the part of the database that contains the table that you want to retrieve. See the following references for more information about how to restore a database to a different name and location: How to restore files to a new location (Transact-SQL)

  • Restore transaction log backups by using the STOPAT option
    Restore the transaction log backups to the partial or full database restore and stop before the point in time when the table or rows were deleted. Use the STOPAT option of RESTORE LOG command to stop the transaction log restore and to recover the database before the time when the table was deleted. See How to restore to a point in time

  • Retrieve data
    After you restore the database, you can copy the table or rows back to your original database by using either INSERT, Bcp (Bulk Copy Utility) or SELECT INTO. ...

  • DBCC CHECKTABLE
    Run the DBCC CHECKTABLE Transact-SQL reference on the new table to verify the integrity of the data.

I ommitted the details, you can follow the link to the KB article for a full coverage of the topic.