What's the best thing to do when you accidentally delete things from a database that you shouldn't, restore from a backup?
-
1In 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...)– WrikkenSep 30, 2010 at 18:03
-
4panic? works for me.– RaySep 30, 2010 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 TSep 30, 2010 at 18:04
3 Answers
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.– SamSep 30, 2010 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.
-
-
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)– Davy8Sep 30, 2010 at 18:08
-
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.