Quick SQL Table Restore

A while back I discussed a method to do quick SQL table backups.  I usually create backups of tables prior to taking an action that has the potential to create the need to restore that table's data.  It's just a good idea, is easier than taking a full backup and gives you a readily available source of the original data should something go wrong with the changes you make.

Not that it has ever happened to me...but occasionally the need may arise to restore this data to the table you just butchered.

If you used the script in quick SQL table backups you ended up with a table backup named something like PRODDTA.F0101_200912071424 with PRODDTA being the schema, F0101 the table name and 200912071424 representing the date and time as YYYYMMDDHHMM.

We can use INSERT INTO to restore the data from this backup table to the original table but it requires us to truncate the original table, deleting all existing records.  The INSERT command appends records and any unique constraints in place on the original table will be observed, resulting in a "Violation of PRIMARY KEY constraint" error if you attempt to restore the data without clearing the original table.

Truncating any table is not a task lightly undertaken and the pucker factor can be pretty high.  Relax though, we do have a copy of the data in a table backup right?


To clear the original table we use the TRUNCATE command in the form:

TRUNCATE TABLE databasename.schemaname.originaltablename

--SQL Script begin
TRUNCATE TABLE JDE_PRODUCTION.PRODDTA.F0101
--SQL Script end


With the original table suitably cleared we can move forward with putting the backup table's data back into the original using this form:

INSERT INTO databasename.schemaname.originaltablename SELECT * from databasename.schemaname.backuptablename

--SQL Script begin
INSERT INTO JDE_PRODUCTION.PRODDTA.F0101 SELECT * from JDE_PRODUCTION.PRODDTA.F0101_200912071424
--SQL Script end


That takes care of getting the data back into the table but we have one last step to complete the recovery - rebuilding indexes and updating statistics.  While the data being restored to the original table is the exact same as what existed before, the storage engine needs to be re-taught what data is where by rebuilding the B-tree for the original table's indexes.

Since rebuilding indexes also accomplishes the goal of updating statistics we are going to execute the index rebuild only in this form:

ALTER INDEX ALL ON databasename.schemaname.originaltablename
            REBUILD

--SQL Script begin
ALTER INDEX ALL ON JDE_PRODUCTION.PRODDTA.F0101
REBUILD
--SQL Script end


That completes our quick SQL table restore.   You're back up and running with minimal interruption.


Related postings:

Quick SQL Table Backup
http://jeffstevenson.karamazovgroup.com/2009/06/quick-sql-table-backup.html

Identify SQL Table Backups
http://jeffstevenson.karamazovgroup.com/2009/12/identify-sql-table-backups.html