2

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.

  • The error seems to indicate that you now have children rows without parent rows (due to restoring back to a previous version of the Incidents table - how do you plan to address that? – Scott Hodgin Apr 25 '17 at 17:11
  • That's an excellent question. This is my first attempt at doing this. It appears there are multiple tables that also need to get updated. I'm assuming once I get all the data back into the tables that are missing rows, I should be able to apply the constraints back in? – Geoff Dawdy Apr 25 '17 at 17:28
  • You didn't really explain WHY you are having to restore the table back in time, but you will not be able to enable (and check) the FK's on the children tables until you delete from the children tables those rows that don't have a parent row on the Incidents table. Is this as simple as merely being able to delete the rows from the children tables where no parent can be found or is something more complex involved? On a side note, you 'might' also have to run DBCC CHECKIDENT with RESEED. – Scott Hodgin Apr 25 '17 at 17:37
  • Can you provide more info on what's wrong? If someone deleted some of the rows in the original Incidents table, then the matching IncidentSubject rows would also have been deleted, and would also need to be restored. Did some info in Incidents get deleted/changed? Then you might just want to update the active Incidents table from the restored one. – RDFozz Apr 25 '17 at 17:47
  • 1
    how can I incert just the incidents I need if I'm working with an Identity column? – Geoff Dawdy Apr 25 '17 at 18:23
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.