How To Restore A Replicated Database

Question

I am replicating our live database using transaction replication. This is basically to have a backup copy of our database in the case of server failure.

The replicated database is read only, so I backed it up, restored it, and tested it.

I noticed straight away that the identity columns didn't work.

For instance, the original schema was like this:

CREATE TABLE [dbo].[Inspection](
[InspectionID] [int] IDENTITY(1,1) NOT NULL,
[CarID] [int] NOT NULL,

Then, on the replicated copy, it looks like this:

CREATE TABLE [dbo].[Inspection](
[InspectionID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[CarID] [int] NOT NULL,

That means the identity columns are disabled, right?

Is there a way to get around this and make this database an independent copy in its own right?

Also, I noticed that the foreign keys are not propgated. I am aware that there are settings for this, but will propogating the foreign keys be a problem now that the identity columns are disabled (which are the primary keys)?

Answer 1

If you want a standby copy of your data for HA/DR then is much better to use log shipping or database mirroring. Since these technologies create a physical database copy, you avoid exactly the problem(s) you mention. Also, schema changes 'replicate' much easier.

If you insist on transactional replication, seed your replicas with a backup instead of a snapshot. See How to: Initialize a Transactional Subscription from a Backup for a step-by-step guide.

Answer 2

NOT FOR REPLICATION on an IDENTITY column simply means that the identity column value is not incremented on the subscriber when a replication agent performs an insert operation. In plain English: When a change is propagated from the publisher to the subscriber, the IDENTITY value, at the subscriber, does not get increased on the subscriber. The value that the publisher gives to the subscriber is simply accepted. This is functionally equivalent to executing SET IDENTITY_INSERT ON; prior to your INSERT operation.

What all of this means is that the IDENTITY property for that table is NOT disabled, unless it is a replication agent inserting data into the table. This also means that propagating your foreign keys shouldn't be a problem.

Despite this, if you still want to remove this NOT FOR REPLICATION option for the identity field, execute the following:

ALTER TABLE [dbo].[Inspection]
    ALTER COLUMN [InspectionID] DROP NOT FOR REPLICATION;

Afterwards, you may want to reseed the identity. A brute force method of doing this is the following:

DBCC CHECKIDENT('dbo.Inspection', RESEED, 0);
GO

DBCC CHECKIDENT('dbo.Inspection', RESEED);
GO

I hope this helps in some way,

Matt