Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

What's the best thing to do when you accidentally delete things from a database that you shouldn't, restore from a backup?

share|improve this question

migrated from stackoverflow.com Sep 30 '10 at 18:05

This question came from our site for professional and enthusiast programmers.

1  
In depends on how much data is gone, how easy it is to recreate it, and if the backups are up to date (binlogs will save you one day...) –  Wrikken Sep 30 '10 at 18:03
4  
panic? works for me. –  Ray Sep 30 '10 at 18:03
    
Ray, panicing would NOT help >.> @Wrikken, It was only maybe 50 rows, and I think it might be pretty easy to extract them. –  Bubby4j Sep 30 '10 at 18:04

3 Answers 3

up vote 11 down vote accepted

Restore your last backup to a temporary database. This will allow you to pull out only the rows that were deleted, and then insert them back the live db. (As opposed to reverting the entire database to yesterday's state.)

share|improve this answer
    
Depending on relationships (cascade delete) and triggers, there may be other data you deleted outside of that table, so make sure to look at that. –  Sam Sep 30 '10 at 18:11

Restoring from a backup is the only option left as far as I know. You might not be able to recover deleted data once it is commited.

Thats why whenever you delete anything (even accidently, when you use DELETE keyword), always use transactions.

share|improve this answer
    
+1. When deleting,ROLLBACK is your friend. –  Brian Hooper Sep 30 '10 at 18:04
    
Blindly restoring can be dangerous. You'll lose everything that changed since the backup. The OP will need to evaluate whether that is acceptable. I think @Alex's answer is safer (assuming the OP can tell which data was removed when looking at it) –  Davy8 Sep 30 '10 at 18:08
    
More detail please? I have never herd of transactions or rollback. –  Bubby4j Sep 30 '10 at 18:09

For me, I would not do a restore. I would extract it from a backup and insert the deleted data again to the live database. In this case, you would never have a downtime and you will not lose the new data after the backup. The only thing you would need is another computer to do the extraction from.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.