CODEWITHSUNDEEP
sqlsql-server-2008-r2replication
psyche
psyche

Reputation: 75

Syncing of Two Databases

I have problems syncing two databases using replication.

Yes, I've done the part where it can copy SERVER A's data to SERVER B.

But my problem is, it deletes the existing data of SERVER B that is not found in SERVER A.

Can anyone help?

Upvotes: 1

Views: 102

Answers (1)

Brandon Williams
Brandon Williams

Reputation: 3755

This is by design. Each type of replication in SQL Server typically begins with an initial synchronization of the published objects, which is known as the snapshot. The snapshot is a copy of all of the published objects and data specified by a publication (the source). This is covered in Types of Replication.

By default, the article property Action if name is in use (@pre_creation_cmd) is set to Drop existing object and create a new one. This is why your tables are being dropped and recreated in SERVER B.

You will need to manually merge the existing databases into one and initialize from that source.

Alternatively, you can set the article property Action if name is in use to Keep existing object unchanged, Delete data, or Truncate all data in the existing object. You can find this option in the Publication Properties -> Articles page. You will need to experiment with each option to see which one meets your needs.

Upvotes: 1

Related Questions