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:

1. Locate the foreign key under Database | MyDb | Tables | dbo.MyTable | Keys.
2. Right-click on the Foreign Key and choose Script Key as | Drop and Create to | Clipboard.
3. Paste this into the query window.
4. Delete the USING MyDb statement and separate the DROP statement from the two ALTER TABLE statements.
5. 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

Conclusion

Your table is restored.

Observations

1. 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.
2. Also, this process might not work perfectly with calculated columns without changes to the above process.
3. It should be easier to do this.

If you know of a better way that doesn’t require $oftware, let me know.