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.