How to Replace a Single Table with Identity Primary Key Column from Database Backup

foreign keyidentitysql serversql-server-2012

I need to restore a table from a backup. My understanding is the only way to get a single table back into the database is to copy it into a temporary table. Then drop the old table and rename the new one to the old name. My problem comes with this particular step due to the Primary Key column being an Identity column. The table also has multiple foreign key constraints. How can I copy one table from my backup database into the original database and still maintain the Primary key identity column and all foreign constraints?

Here is my current scripted attempt:

CREATE TABLE dbo.Incidents_tmp
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[IncidentAction_ID] [int] NULL,
[IncidentCompromise_ID] [int] NULL,
[IncidentDamage_ID] [int] NULL,
[IncidentReportingEntity_ID] [int] NULL,
[IncidentStatus_ID] [int] NULL,
[IncidentType_ID] [int] NULL,
[IncidentID] [varchar](20) NULL,
[Version] [timestamp] NOT NULL,
 CONSTRAINT [PK_Incidents_tmp] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET IDENTITY_INSERT dbo.Incidents_tmp ON
GO

IF EXISTS ( SELECT * 
        FROM DB_old.dbo.Incidents )
INSERT INTO DB_new.dbo.Incidents_tmp 
        ( 
        ID ,
        IncidentAction_ID ,
        IncidentCompromise_ID ,
        IncidentDamage_ID ,
        IncidentReportingEntity_ID ,
        IncidentStatus_ID ,
        IncidentType_ID ,
        IncidentID 
        )
    SELECT 
    ID ,
    IncidentAction_ID ,
    IncidentCompromise_ID ,
    IncidentDamage_ID ,
    IncidentReportingEntity_ID ,
    IncidentStatus_ID ,
    IncidentType_ID ,
    IncidentID ,
    FROM DB_old.dbo.Incidents TABLOCKX
GO

SET IDENTITY_INSERT DB_new.dbo.Incidents_tmp OFF
GO

ALTER TABLE dbo.IncidentSubject DROP CONSTRAINT 
FK_IncidentSubject_Incidents;
ALTER TABLE dbo.IncidentsReportLog DROP CONSTRAINT 
FK_IncidentsReportLog_Incidents;
ALTER TABLE dbo.Incidents DROP CONSTRAINT PK_Incidents;

DROP TABLE DB_new.dbo.Incidents
GO

Exec sp_rename 'Incidents_tmp', 'Incidents'

Exec sp_rename 'PK_incidents_tmp', 'PK_Incidents'


ALTER TABLE [dbo].[IncidentSubject]  WITH CHECK ADD  CONSTRAINT 
[FK_IncidentSubject_Incidents] FOREIGN KEY([Incident_ID])
REFERENCES [dbo].[Incidents] ([ID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[IncidentSubject] CHECK CONSTRAINT 
[FK_IncidentSubject_Incidents]
GO

When I try adding the constraints back in I get an error stating:

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_IncidentSubject_Incidents". The conflict occurred in database "DB_new", table "dbo.Incidents", column 'ID'.

EDIT:
Some additional info. The user of this application deleted 175 incidents by accident. Unfortunately she didn't tell us until two weeks later and then it wasn't even routed to us until almost a month later. In users have been accessing the application and making changes so a complete database restore is not an option. The user is requesting just the deleted incidents be restored. I looked into the database and noticed an incidents table with the missing data in the current database and the 175 records existing in the backed up database. Hope that provides some better context for why I'm trying to restore just a single table of incidents. It starting to look like additional tables need to be restored.

Best Answer

Based on the information you have provided in your question, I think you will need to restore a good backup (with the missing rows for Incidents) into a test database. You'll need to insert into the real current Incidents table the missing rows from the backup version of the Incidents table (making sure you properly use the SET IDENTITY_INSERT command). Using SET IDENTITY_INSERT ON allows explicit values to be inserted into the identity column of a table thus allowing you to insert the deleted rows. Make sure you issue the SET IDENTITY_INSERT OFF after you have completed your inserts. You'll need to do the exact same comparison and insert for the children tables for each of the parent rows you are restoring since the children were automatically deleted when the parent rows were deleted (due to cascade delete FK's).

After you have addressed all children tables, you should be able to add the FK's back again with CHECK.

You might want to prototype this on a test server.

--Demo IDENTITY_INSERT
--Create test table
IF OBJECT_ID('dbo.TestTable') IS NOT NULL
    DROP TABLE dbo.TestTable
GO

CREATE TABLE [dbo].[TestTable] (
    TestId INT identity(1, 1) NOT NULL
    ,TestColumn VARCHAR(5)
    ) ON [PRIMARY]
GO

--insert a few test rows
INSERT INTO TestTable (TestColumn)
VALUES ('one'),('Two')

--select the two rows - the id values will be 1 and 2
SELECT * FROM TestTable

--delete the two rows
DELETE FROM testtable

--set identity_insert on to allow you to explicitly insert id values
SET IDENTITY_INSERT dbo.TestTable ON

--insert id 1 and 2
INSERT INTO TestTable (TestId,TestColumn)
VALUES (1,'one'),(2,'Two')

--set identity_insert off
SET IDENTITY_INSERT dbo.TestTable OFF

--select the current rows - should be id 1 and 2
SELECT * FROM TestTable
Related Question