Steps to clean up orphaned replication settings in SQL Server

By: Kun Lee   |   Comments (7)   |   Related: > Replication

Problem

As with many companies, we are trying to make our Test environment as close to our Production environment as possible. Our production environment is very replication intensive and we want to have our Test environment mimic Production as much as possible. Typically, when I restore the production replicated database onto the Test environment, I cleanup all the publications to make sure all traces are moved and then I reconfigure replication for the Test environment.  As Books Online mentions; "Replication supports restoring replicated databases to the same server and database from which the backup was created. If you restore a backup of a replicated database to another server or database, replication settings cannot be preserved. In this case, you must recreate all publications and subscriptions after backups are restored."

However, if you just restore the database on top of an existing replicated database as shown below, you get orphan replication configurations. You can cleanup many processes to drop the Log Reader and Distribution agent jobs, but even after that if you query the "Distribution.dbo.MSpublications" table you often still see that records exist.

Solution

In this article, I am going to use "Transactional Replication" as an example, but I also used this process for Peer-to-Peer replications as well, and you can use the same concept for Merge Replication.

Before I start, I typically run the below script to see if I can get any publications to drop cleanly.

Please note that I used SQLCMD mode for all my scripts, so if you are running them one at a time, please make sure to turn on the SQLCMD mode. If you don't know how to do, please see this tip Using the SSMS Query Editor in SQLCMD Mode.

-- Connect Subscriber
:connect TestSubSQL1
use [master]
exec sp_helpreplicationdboption @dbname = N'MyReplDB'
go
use [MyReplDB]
exec sp_subscription_cleanup @publisher = N'TestPubSQL1', @publisher_db = N'MyReplDB', 
@publication = N'MyReplPub'
go
-- Connect Publisher Server
:connect TestPubSQL1
-- Drop Subscription
use [MyReplDB]
exec sp_dropsubscription @publication = N'MyReplPub', @subscriber = N'all', 
@destination_db = N'MyReplDB', @article = N'all'
go
-- Drop publication
exec sp_droppublication @publication = N'MyReplPub'
-- Disable replication db option
exec sp_replicationdboption @dbname = N'MyReplDB', @optname = N'publish', @value = N'false'
GO

General Check from SSMS

First, you should check on both the publisher and subscriber to make sure there are no publications or subscriptions.  If you still see any subscriptions or publications, go ahead and drop them. If you are using SSMS, you may get lucky and SSMS may drop them completely.

Check Replication Jobs (Log Reader, Snapshot and Distribution agent Jobs)

If you check the SQL Agent jobs, you often see that jobs still exist. You can try the below command to see if it drops them. I am connected to Distributor when I run this command.

-- Connect Distributor

:CONNECT TestDistSQL1
go
exec Distribution.dbo.sp_MSremove_published_jobs @server = 'TestPubSQL1', 
@database = 'MyReplDB'
go

When I ran this for my setup, it didn't work well, so I ended up deleting the jobs manually.

As you may know, depending on the types of replication, you may find the jobs on different servers.

• Push Replication:- distributor job is on Distributor with all other agents
• Pull Replication - distributor job is on Subscriber and the rest of the jobs are on Distributor

Check the Record in MSpublications Table on Distributor

Now, if you run below script, you may still see replication records even after all the cleanup.

:CONNECT TestDistSQL1
go
select * from Distribution.dbo.MSpublications

Now, this is an easy part, but very important as well. Other than trying to hack through the system tables to remove all traces of replication, you just need to recreate the publication and run the above clean up script again to drop replication and let SQL Server do the complete clean up.  The easiest way to recreate the publication is to have scripted out the replication setup and to just rerun that script.

That is very typical process for anytime you cleanup replication manually, you will want to recreate the publication with the exact same name and drop it to remove all settings.

Once that is done, you should have cleaned up all of your replication settings.

You can take a look at the scripts in this tip SQL Server Replication Scripts to get Replication Configuration Information to gather more information about replication as well.

Next Steps

• After you create your publications and subscriptions you should script out the settings, so you have them if you ever need to recreate either the publication or subscription
• You could also auto generate the clean up script from the distribution database. Stay tuned for a future tip about auto generating replication scripts based on existing replication settings
• Read more about replication in these tips

About the author

Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

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

View all my tips