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

Question

I have a table in a database:

• MyTable

Other have foreign key constraints to MyTable's Id column:

• MyOtherTable1
• MyOtherTable2

I need to backup MyTable as is.

The task I am going to do is a data conversion. While the data conversion works in QA, our change control requires that we have to have a rollback process in place. I don't plan to actually have to restore the table as is, but I have to prove that I can before change control lets me make the change.

The system is a live order system. I cannot restore the whole database because many orders will go through between the time the change is made and when I will know if I have to restore.

I already figured out how to backup the table.

SELECT * INTO MyTable_Bak FROM MyTable;

However, restoring the table is not working. I cannot do this:

DELETE FROM MyTable
SELECT * INTO MyTable FROM MyTable_Bak;

The above fails because of foreign key constraints.

I am not looking for a $oftware. I know Red Gate and other tools could do this.

The tools available to me are:

• SQL Management Studio
• Admin privileges to the database

Additional Requirements

• The data in every column most be identical after the restore. Id, dates, etc.
• The table cannot be dropped
• Rows in MyOtherTable1 or MyOtherTable2 cannot be changed or deleted.

Note: With this detailed of a question, I detected the smaller parts of this problem one by one as I wrote the question and solved each problem seperately. Do I keep the question and answer it myself or delete it? Since I found no similar question and answer, I will keep it.

Answer 1

Answer from here: http://www.rhyous.com/2017/03/14/back-up-and-restore-a-single-table-with-foreign-keys-using-sql-server/

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 Server 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]
ALTER TABLE [dbo].[MyOtherTable2] DROP CONSTRAINT [FK166B6670AD1D93C0]

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 foreign 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])

ALTER TABLE [dbo].[MyOtherTable2] CHECK CONSTRAINT [FKDDED6AECAD1D93C0]

ALTER TABLE [dbo].[MyOtherTable2]  WITH CHECK 
    ADD CONSTRAINT [FK166B6670AD1D93C0] 
    FOREIGN KEY([MyTableId]) REFERENCES [dbo].[MyTable] ([Id])

ALTER TABLE [dbo].[MyOtherTable2] CHECK CONSTRAINT [FK166B6670AD1D93C0]

Your table is restored.

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