Rename Subscriber Database
A seven-step checklist for renaming a subscriber database and updating every replication table that references it.
Renaming a subscriber database is not as simple as ALTER DATABASE - three replication tables hold the old name and the distribution agent job does too. This script walks through the whole sequence: stop the distribution agent, rename the database, update syssubscriptions in the published database, update MSsubscriptions and MSdistribution_agents in the distribution database, edit the database name on the distribution agent job, then start the agent again.
--------------------------------------------------------------------------------------------
--(1) Stop the disctibution agent.
--------------------------------------------------------------------------------------------
--(2) Rename the subscriber database.
--------------------------------------------------------------------------------------------
--(3) Upate syssubscriptions table in the Publication database:
/*
use <PUBLISHED_DATABASE_NAME>
update syssubscriptions
set dest_db = '<NEW_DATABASE_NAME>' where dest_db = '<OLD_DATABASE_NAME>'
*/
--------------------------------------------------------------------------------------------
--(4) Update MSsubscriptions table in the distribution database:
/*
use distribution
update MSsubscriptions
set subscriber_db = '<NEW_DATABASE_NAME>' where subscriber_db = '<OLD_DATABASE_NAME>'
*/
--------------------------------------------------------------------------------------------
--(5) Update MSdistribution_agents in the distribution database:
/*
use distribution
update MSdistribution_agents
set subscriber_db = '<NEW_DATABASE_NAME>' where subscriber_db = '<OLD_DATABASE_NAME>'
*/
--------------------------------------------------------------------------------------------
--(6) Edit the database name on the distribution agent job.
--------------------------------------------------------------------------------------------
--(7) Start the agent