Maintaining consistency when using temp backup tables

Question

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

Answer 1

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

or

2.- Find the difference in the data like this

SELECT * FROM faultyTable 
EXCEPT 
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.

Answer 2

I usually do

BEGIN TRANSACTION

-- SQL CODE

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

COMMIT

otherwise

ROLLBACK

Answer 3

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.