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

  • 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. – James T Sep 30 '10 at 18:04

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.)

  • 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.

  • +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. – James T 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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