Primarykey violation in restored database

Question

Hi,

I set up the transactional replication(not updatable subscriber) between sql server 2005 database. The tables in published database have identity columns. The tables were replicated without problem. But when I back up and restore the subscriber database, I could not insert row in a table which contains identity increment field and it says, primary key violation occurs. If I tried after deletion and recreation of this identity property or resetting the identity seed value to rowcount of the table, it allows to insert. I need guidance to backing up and restoring the subscriber database under transactional replication to avoid problems like this.

Thanks, P.Gopalakrishnan.

Answer 1

In a transactional replication scenario, you would not normally be making any data changes to the subscriber database(s) (you can still create extra indexes). [It can be done using Updatable Subscriptions for Transactional Replication]

Why do you want to backup and restore the subscriber database?

For an overview of SQL Server Replication see here.

Answer 2

My publisher is down. So, I like to use subscriber database as publisher in another server.