Back up and restore a single table with foreign keys using SQL Server

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.

16 Comments

  1. www.dealsclassified.online

    Rhyous » Blog Archive » Back up and restore a single table with foreign keys using SQL Server

  2. conversational tone

    Rhyous » Blog Archive » Back up and restore a single table with foreign keys using SQL Server

  3. made my day says:

    made my day

    Rhyous » Blog Archive » Back up and restore a single table with foreign keys using SQL Server

  4. please click Praca Niemcy

    Rhyous » Blog Archive » Back up and restore a single table with foreign keys using SQL Server

  5. […] This system is composed of air pockets that offer additional support for try the voucher code here (official site) heel and arch of the foot. Additionally, it comprises a coating of polyurethane on the interior of […]

  6. nhsdiscounts.org.uk

    Rhyous » Blog Archive » Back up and restore a single table with foreign keys using SQL Server

  7. from reddit says:

    from reddit

    Rhyous » Blog Archive » Back up and restore a single table with foreign keys using SQL Server

  8. furniture and saving

    Rhyous » Blog Archive » Back up and restore a single table with foreign keys using SQL Server

  9. Www.greenstone.org noted

    Rhyous » Blog Archive » Back up and restore a single table with foreign keys using SQL Server

  10. sellfast.in says:

    sellfast.in

    Rhyous » Blog Archive » Back up and restore a single table with foreign keys using SQL Server

  11. juandeville7.skyrock.com

    Rhyous » Blog Archive » Back up and restore a single table with foreign keys using SQL Server

  12. how to open registry editor windows 7 64 bit

    Back up and restore a single table with foreign keys using SQL Server | Rhyous

  13. www.port-a.de

    Back up and restore a single table with foreign keys using SQL Server | Rhyous

  14. booksoc.Tk says:

    booksoc.Tk

    Back up and restore a single table with foreign keys using SQL Server | Rhyous

  15. Natasha Mary says:

    Thank for useful information

Leave a Reply

How to post code in comments?