6

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

Simhadri
  • 183
  • 1
  • 1
  • 4
  • 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. –  Sep 24 '10 at 21:07
  • @Simhadri: As mentioned in [my answer](http://stackoverflow.com/questions/3790883/how-to-rollback-a-deleted-table-data/3790927#3790927), you need a 3rd party tool to work directly with the log. – Joe Stefanelli Sep 24 '10 at 21:09

7 Answers7

12

Get it from backups!

thursdaysgeek
  • 326
  • 1
  • 3
  • 10
9

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

Milena Petrovic
  • 381
  • 2
  • 5
4

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.

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

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.

Remus Rusanu
  • 8,283
  • 1
  • 21
  • 23
1

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.

Tim
  • 226
  • 1
  • 3
0

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.

Dan J
  • 101
  • 1
0

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

use mydatabase
go
SELECT 
 [Current LSN],    
 [Transaction ID],
     Operation,
     Context,
     AllocUnitName

FROM 
    fn_dblog(NULL, NULL) 
WHERE 
    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.

Jason Clark
  • 103
  • 5