1

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.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Rhyous
  • 6,510
  • 2
  • 44
  • 50

2 Answers2

0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rhyous
  • 6,510
  • 2
  • 44
  • 50
0

You can use @Rhyous answer, or it is possible to disable/enable constraints.

One possible way to do it is:

  • Disable all constraints or only specific constraints
  • Restore table(s)
  • Enable all constraints or only specific constraints

Note: Take care to not insert incorrect values in constrained columns, or you will not be able to enable constraint on tables with incorrect data. You can use this script below that will help you to identify which constraint are disabled or enabled:

SELECT (CASE 
    WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
    ELSE 'DISABLED'
    END) AS STATUS,
    OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
    OBJECT_NAME(FKEYID) AS TABLE_NAME,
    COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
    OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
    COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME 
FROM SYSFOREIGNKEYS 
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO

You can find below a link to another post that will be useful : How can foreign key constraints be temporarily disabled using T-SQL?

Xavier Grimaldi
  • 1