Rolling Back a Single Transaction in SQL?

[hoialmen]

Is there a way that you can roll back a single transaction in SQL Server? I had a user accidentally delete a record and I would like to just roll back that transaction if possible. I found the Syntax for a Transaction Rollback on Books Online but you need to specify a Transaction Name and I am not quite sure how to find that. Any help will be greatly appreciated.

Thanks!

Nate

 

[tlbroadbent]

Transaction Rollback is only applicable before the transaction has completed and been committed. In SQL Server, there is no way to restore a deleted record or undo an update unless you've kept a history table and write your own code to perform the restore.

You can purchase third party tools that will read the SQL Transaction log and recover from that. I use Lumigent's Log Explorer and have found it very useful for this type of recovery. It can recover individual records or transactions. It can even recover deleted tables in some circumstances.

Log Explorer does require that transactions logs be created. You can't use Simple Recovery model in SQL 2000 or Truncate Log on Checkpoint in SQL 7. If you do, there will be no log records to restore.

You can download a fully operational demo version of Log Explorer from

http://www.lumigent.com.

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[hoialmen]

Thanks, Terry. I'll check it out.

Nate