March 20, 2006 at 3:03 am
i just forgot to set a where clause on a delete statement and deleted loads of data. it wasnt a transaction so i cant rollback. how do i recover the data??
March 20, 2006 at 3:12 am
Fintan
Back up your transaction log (assuming you are in Full Recovery mode). Then restore from your last full backup with no recovery. Restore all transaction log backups since the last full backup (except the one you just took) with no recovery. Restore the last transaction log backup with stopat = just before you did the deletion. Look up the syntax of the restore command in Books Online for details of how to do all this.
John
March 20, 2006 at 3:28 am
how do you know what recovery mode you are in?
March 20, 2006 at 3:40 am
In Enterprise Manager, right-click on the database and choose Properties. Click on the Options tab and you will see the Recovery Model - it will be Full, Bulk-Logged or Simple.
By the way, if you have a test server I would recommend restoring your database to that rather than over your live one.
John
March 20, 2006 at 3:45 am
If you have the latest backup of data, then you can restore over into Current Database,
Regards,
Amit Gupta./
March 20, 2006 at 6:24 am
i took the easy way out and simply done a dts on the table from production to the table in test server that i deleted all data from. not the most elegant solution but worked for now. thanks for your help all
March 20, 2006 at 7:32 am
Oh. An important fact you didn't tell us.
I am sure you would have gotten better suggestions if you had told us this happened on a TEST system and not on the production system.
All the responses you got were based on the thought that you lost the data from a production system and didn't have the information anywhere else.
-SQLBill
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply