Today I needed to backup a single database table, then test a change to the data, then if the change failed, restore the original data.
Below is how I did this.
What I used
I used SQL Server Management Studio to do all of the following steps.
I performed the steps on a database running on a SQL 2012 database server.
Part 1 – Backup the table
SELECT * INTO MyTable_Bak FROM MyTable;
Note: This will work usually, however, it won’t work if you have a calculated column. If you have a calculated column, create the table first, then specify the columns you are inserting. I didn’t have a calculated column, so I didn’t take time to figure this out.
Part 2 – Restoring the table
Step 1 – Finding the Foreign Key Constraints
SELECT Name, Object_Name(parent_object_id) as [Table] FROM sys.foreign_keys WHERE referenced_object_id = object_id('MyTable')
The results were like this:
Name Table FKDDED6AECAD1D93C0 MyOtherTable1 FK166B6670AD1D93C0 MyOtherTable2
Step 2 – Get the Drop and Create for each Foreign Key
In SQL Management Studio Express, I went to each table in the above list, and did the following:
- Locate the foreign key under Database | MyDb | Tables | dbo.MyTable | Keys.
- Right-click on the Foreign Key and choose Script Key as | Drop and Create to | Clipboard.
- Paste this into the query window.
- Delete the USING MyDb statement and separate the DROP statement from the two ALTER TABLE statements.
- Repeat for the next foreign key constraint, grouping the DROP statements and the ALTER TABLE statements together.
Step 3 – Run the DROP statements
Run the two DROP statements created above.
ALTER TABLE [dbo].[MyOtherTable1] DROP CONSTRAINT [FKDDED6AECAD1D93C0] GO ALTER TABLE [dbo].[MyOtherTable2] DROP CONSTRAINT [FK166B6670AD1D93C0] GO
Step 4 – Restore the table
I used this query to restore the table from the backup.
SELECT * FROM MyTable SET IDENTITY_INSERT dbo.MyTable ON; TRUNCATE TABLE MyTable ; INSERT INTO MyTable (Id, Col1, Col2, Col3) -- Specify all columns here SELECT (Id, Col1, Col2, Col3) -- Specify all columns again here FROM MyTable_Bak
Step 5 – Restore the foriegn key constraints
Run the ALTER TABLE scripts you grouped together from Step 2.
ALTER TABLE [dbo].[MyOtherTable2] WITH CHECK ADD CONSTRAINT [FKDDED6AECAD1D93C0] FOREIGN KEY([MyTableId]) REFERENCES [dbo].[MyTable] ([Id]) GO ALTER TABLE [dbo].[MyOtherTable2] CHECK CONSTRAINT [FKDDED6AECAD1D93C0] GO ALTER TABLE [dbo].[MyOtherTable2] WITH CHECK ADD CONSTRAINT [FK166B6670AD1D93C0] FOREIGN KEY([MyTableId]) REFERENCES [dbo].[MyTable] ([Id]) GO ALTER TABLE [dbo].[MyOtherTable2] CHECK CONSTRAINT [FK166B6670AD1D93C0] GO
Your table is restored.
- This is a simple process for a table with only a few foriegn key constraints but could be difficult if you have dozens of foreign key constraints.
- Also, this process might not work perfectly with calculated columns without changes to the above process.
- It should be easier to do this.
If you know of a better way that doesn’t require $oftware, let me know.