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

  • Why would you want to rollback a table that was accidentally deleted? As you are suggesting, that it was in a transaction, a transaction with actions in between was successful gets committed... in short - get the backups... you did make backups right? – t0mm13b Sep 24 '10 at 20:54
  • 6
    Sounds like OP may have learned a hard lesson... – JNK Sep 24 '10 at 20:55
  • @JNK: yup yup! Consider this as rite of passage - good job it aint a 'rm -rf' on the root of unix system :P – t0mm13b Sep 24 '10 at 21:01
  • We do have backup, im done with that already.Is there anyway can we work with database log to rollback recent deletion. – Simhadri Sep 24 '10 at 21:07
  • @Simhadri: As mentioned in my answer, you need a 3rd party tool to work directly with the log. – Joe Stefanelli Sep 24 '10 at 21:09

Get it from backups!

  • Is there any query to rollback that. – Simhadri Sep 24 '10 at 20:54
  • Nope. Without backups, you're toast. If it was within a transaction and you haven't committed, you have hope. Otherwise, a backup is your only option. – thursdaysgeek Sep 24 '10 at 20:57

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.

  • +1 for third-party tool suggestion. I guess what it comes down to is, how important was this table? – djacobson Sep 24 '10 at 21:04

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

    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.


A few people have mentioned 3rd party tools. The only one I have personally had any luck with for transaction log playback is Toad by Quest. It's not very cheap but if it's important it's a life saver. We had some poorly written web apps that were attacked by SQL injections and ruined some old DBs. The attack wasnt really a problem but we wanted to see what code they were executing and so being able to read back the T-LOG was amazingly convenient.


We do have backup, im done with that already.Is there anyway can we work with database log to rollback recent deletion. – Simhadri

You may need to explore transaction-log replaying strategies.


Another helpful method for recovering deleted tables data in SQL Server is to use fn_dblog() function.

use mydatabase
 [Current LSN],    
 [Transaction ID],

    fn_dblog(NULL, NULL) 
    Operation = 'LOP_DELETE_ROWS'

however the method of recovering data by using fn_dblog() function is too long, but it can be helpful for viewing the deleted records of a table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.