Hi,

I replicated a database to a new server as explained below.

ServerA - publisher  (the current database)

ServerB - subscriber (the new databse)

First i took a database backup from ServerA and restored in the ServerB. Then i applied snapshot replication using PULL subscription.

However it failed saying "Cannot drop table <tablename> because it is referenced by the view <viewname>". So i went to publication's properties and checked for articles options. There, by default it has selected "If table schema exists in the subscriber, drop the dable". So i changed it to option "Delete data in the subscriber table" and started the process

Now another error raised as 'Cannot truncate the table <tablename> because it is referenced by the view <viewname>". So selected another option in the publication which says "Don't change the destination table"

Now the error is "Can not insert duplicate key row in object <tablename> with unique index <primary key>" and failed the replication

Then in the subscriber, i created an empty database and replicated without restoring a backup. Then it worked and all tables has been transfered with data. But the primary keys and foreign keys are not available in the destination database.

Does anyone have any idea on this?

 

Thanks 

---

Dinesh Karunarathna
Advanced Data Technologies (pvt) Ltd

You can mannually add the primary key now to the replicated table.

I suggest not to maintain the foreign key relationships against the primary key as you have snapshot replication and it will drop and recreate the data periodically

 

 

---

G.R.Preethiviraj Kulasingham MCITP: DBA
http://preethiviraj.blogspot.com/
Plan Your Work and Work Your Plan!

thanks for the reply. Actually the problem was with the schema binding. There were two views with scemabinding enabled. Then it will not allow neither to drop nor truncate the tables reffernced by those views. Once the scemabinding is disabled  all the problems solved and replication is working.

Thanks, 

 

---

Dinesh Karunarathna
Advanced Data Technologies (pvt) Ltd

Great!

---

G.R.Preethiviraj Kulasingham MCITP: DBA
http://preethiviraj.blogspot.com/
Plan Your Work and Work Your Plan!