Recover deleted records 1

[lanm]

I ran a delete statement, and deleted all rows.

Can I restore all these deleted records?

We updated the table with a backup from 3.am this morning, which is okay for now.

Thanks!

 

[fredericofonseca]

not unless you have a transation log.

And even like that it's not very linear, and should be done on another database server. e.g. copy the whole DB to another machine and rollback ALL transations until you reach the point before your delete.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[lanm]

How would I do it with the transaction log?

 

[SQLBill]

Normally, once you realized there was a 'goof', you would immediately back up the current transaction log. Then you would do a restore. First restore the full backup using the WITH NORECOVERY option, then restore each transaction log using the WITH NORECOVERY option, until you got to the last log and then use the WITH STOPAT and RECOVERY options.

Refer to the BOL.

-SQLBill

Posting advice: FAQ481-4875

 

[DrSql]

If you dont have the trasaction log backup you might be able to recover to your last full backup. Restore the last backup on a new db, and copy the table that you deleted, if there are any constariants or triggers it would be a tougher task.

Dr.Sql
Good Luck.

 

[PRPhx]

The lesson here: Before you perform ANY major operation, esp. something like a delete, BACKUP your database!!!!! That way, IF something goes wrong, you can restore from the backup. I've learned my lesson well. Not a pretty site when users can't get info they need and they come calling.

 

[lanm]

PRPhx,

Yup, I totally agree. I tried to do the best way of the Select first, then the delete, but it doesn't help when I type in the wrong table name.

Now, after the sudden-almost heart attack, I backup the table temporarily so the data is always recoverable.

We weren't running the transaction log, but now were are, so we can do a time in point backup. The restore from that morning, LUCKILY, was fine, and no data was lost.

 

[SQLBill]

Now, do a FULL backup (right now). Then start doing Transaction Log backups. I do a FULL backup each day, a Differential backup 12 hours after the full backup and transaction log backups every 4 hours throughout the day.

If your data isn't super important, or you don't have many transactions, you might be able to do a full backup once a week, a differential once a day, and transaction log backups at some interval (6 hours?) each day.

A differential backup lets you do a restore without having to use EVERY transaction log backup.

See the BOL for more information.

-SQLBill

Posting advice: FAQ481-4875

 

[lanm]

Thanks SQLBill!

I think the DBA is doing that now.