sql 2005 rollback transaction

Question

I just did a delete from table

How do I roll back or undelete the rows??

Answer 1

restore from a backup.

Answer 2

if you called begin transaction before you did it, and have not yet called commit, then you can call rollback, otherwise... you're gonna have to restore

Answer 3

if you prefaced the delete with a begin tran you just use rollback tran

if not, start looking for your backups.

Answer 4

rollback;

But only if you were in a transaction..

Answer 5

I did this once on a database with no backups and was able to recover the data using a utility that replayed all the transaction log entries. Fortunately in my situation I had never truncated the transaction log.

Answer 6

This will do the trick, assuming you have the money: http://www.lumigent.com/products/log_explorer.html

Apart from that, backups, like everyone else said

Answer 7

Assuming you have a recent full backup of your database, backup your transaction log now, and restore it to a point in time prior to your delete. If there's live data that's been changed since then, you'll probably want to restore it to a different DB, and manually insert the deleted records.

From memory, so syntax may be a bit off - but you get the idea:

BACKUP LOG DBName
   TO DISK='C:\DBName.TRN'
GO

RESTORE DATABASE DBName2
   FROM DISK='C:\DBName.BAK'
   WITH 
       MOVE 'DBName.MDF' TO 'C:\DBName2.MDF',
       MOVE 'DBName.LDF' TO 'C:\DBName2.LDF',
       NORECOVERY
GO

RESTORE LOG DBName2
   FROM DISK='C:\DBName.TRN'
   WITH
       RECOVERY,
       STOPAT = '09/26/2008 17:00'
GO