Move SQL Server Merge Replication Subscriber to new Server without Reconfiguring Replication

By:   |   Comments (5)   |   Related: > Replication


We had a requirement to move the Subscriber for a Merge publication to a new SQL Server. We could have dropped the old subscriber and created a new subscriber, but in this tip we look at another option.


In this tip we will look at how this can be done by restoring a full backup of the existing subscriber onto the new server and altering a few replication tables without having to drop the old subscription and create a new subscription.


We have two instances SERV1 and SERV2 with publication database db1 on SERV1 and subscriber database db1 on SERV2. Now we need to move the subscriber from SERV2 to SERV3 without dropping the existing subscription and creating a new subscription on the new server. Here are some of the details for the merge publication.

  • Publication name : MergeReplication1
  • Publication database: db1
  • Subscriber database : db1
  • Subscription type: push

Steps to move Merge Replication to a new SQL Server

Stop synchronizing the publication by using Replication Monitor.

Create a full SQL Server database backup on existing Subscriber

Take a full backup of the subscriber database db1 on SERV2.

NAME = N'db1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

Copy SQL Server backup and restore on new Subscriber

Copy the backup file from SERV2 to SERV3 and restore it with KEEP_REPLICATION.


Update replication tables on SQL Server Publisher database

Now we need to alter the server name in a few replication tables. Go to the Publisher database and change the server name in sysmergesubscriptions with the appropriate values. You can get the pubid from sysmergepublications.

-- run in the Publication data

SELECT * FROM sysmergepublications WHERE name = 'MergeReplication1'

UPDATE sysmergesubscriptions 
SET subscriber_server = 'SERV3' 
WHERE subscriber_server = 'SERV2' 
AND DB_NAME = 'db1'
AND pubid = '4936E2B1-4B35-4658-890A-AE7F05C90AEC'

Update the SQL Server replication tables on new Subscriber database

Repeat the above step in the new subscriber database on server SERV3.

Update SQL Server replication tables on Distribution database

Change the server name in MSmerge_agents and MSmerge_subscriptions in the distribution database.

SELECT * FROM MSmerge_agents

UPDATE MSmerge_agents 
SET subscriber_name = 'SERV3' 
WHERE subscriber_db = 'db1' 
AND publication = 'MergeReplication1'
AND subscriber_name = 'SERV2'

SELECT * FROM MSmerge_subscriptions 

UPDATE MSmerge_subscriptions 
SET subscriber = 'SERV3' 
WHERE subscriber = 'SERV2'
AND publisher_db = 'db1'

Modify the SQL Server Agent Job

Now go to merge agent job properties then steps, double click on run agent step and replace -Subscriber [SERV2] with -Subscriber [SERV3].

Re-enable Synchronizing for the SQL Server Publication

Now start synchronizing again from replication monitor and verify that changes are being replicated.

Next Steps
  • Please test in a QA or development environment before doing in production. This way you will know the steps to take when you run on your production servers.
  • Check out more Replication Tips.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ranga Babu Ranga Babu is a SQL Server DBA with experience on performance tuning and high availability.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, December 3, 2020 - 4:56:25 AM - Alvaro Back To Top (87875)
Hi Ranga,

Good article. Today I used it for solving an issue with wrong subscriber_server name. The subscriptions were created with FQN and we received an error when the snapshot was being applied.


Tuesday, January 17, 2017 - 9:56:05 AM - Ranga Back To Top (45371)

Hi Harry,


We had done this couple of times on our QA and production servers.


Tuesday, January 17, 2017 - 8:11:27 AM - Cittyroller Back To Top (45368)

 Hi Ranga,

thank you for your article.

Maybe i need this approach in the next days to troubleshoot replication problems faster (because my subscriber at the moment has a poor connection...).

You had done this only one time or it is a "good" testet way to move a subscription to another server?

Kind regards




Wednesday, May 11, 2016 - 3:12:45 AM - Ranga Back To Top (41453)

Thank you Greg.


Tuesday, May 10, 2016 - 3:44:18 PM - Greg Robidoux Back To Top (41449)

Hi Ranga,

Welcome to the Team.


get free sql tips
agree to terms