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.
Incidents
table, then the matchingIncidentSubject
rows would also have been deleted, and would also need to be restored. Did some info inIncidents
get deleted/changed? Then you might just want to update the activeIncidents
table from the restored one.