Take the 2-minute tour ×

This is related to the accepted answer for What’s your #1 way to be careful with a live database?

Suppose you create a temp table for backup purpose and make your changes in the original. The changes break the system and you want to restore the backup. In the meantime some other records have also changed in the original table (it is a live db). Now if you restore the backup, the system will be in an inconsistent state.

Whats the best way to tackle this

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

I don't think that's desirable, I'd test harder before putting the table in production, but supposing it happened anyway, you'd have two options:

1.- Create an ON INSERT trigger which updates the temporary backup table with the rows inserted into the new table, massaging the data to fit into the old table


2.- Find the difference in the data like this

SELECT * FROM faultyTable 
SELECT * FROM backupTable

You'd have to adjust the columns to be selected to the common subset of course. And EXCEPT is called MINUS sometimes too.

After that you can insert the difference into the backed up table and restore the combination. This gets harder and harder the more relationships the table has... depending on the way used to restore the table you might drop the related data, thus you'd have to select it too.

share|improve this answer
add comment

I usually do



At the end, if everything is OK, do my SELECTs and whatnot



share|improve this answer
Unless you commit, how do you test the system in a "live" setting –  Midhat Oct 4 '08 at 18:20
add comment

Your database may provide this funcionality. For example in Oracle:

Export (Your options) consistent = y

Of course, doing a consistent backup in a production online environment will have a performance penalty for the system.

share|improve this answer
add comment

Your Answer


