Monday, December 7, 2009

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

--SQL Script begin
--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

Identify SQL Table Backups
Subscribe to Jeff Stevenson's Technology Blog - Get an email when new posts appear

No comments: