Steps to clean up orphaned replication settings in SQL Server

By:   |   Comments (8)   |   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.

restore the database on top of the existing replication as shown below
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.

General Check from SSMS

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Kun Lee 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



Comments For This Article




Friday, May 31, 2024 - 1:02:24 PM - Jeff Back To Top (92279)
I have a DB that is setup to replicate only 2 tables. But it looks like the replication was deleted but sys.tables.is_replicated == 1. How do I fix this?

Tuesday, August 30, 2022 - 5:41:34 PM - mike Back To Top (90429)

use the procedure in the distribution database, it will cleanup everything related to a publication:

exec [sys].[sp_MSdrop_publication] @publisher='xx-xx-xx',@publisher_db='xxx',@publication='xxx',@alt_snapshot_folder='g:\',@cleanup_orphans=1

before running this you must fill in the right parameter value that are suitable for your publication.

Sunday, July 12, 2015 - 2:49:22 PM - Gene Browning Back To Top (38184)

Thank you very much.  Very helpful tips.


Tuesday, March 31, 2015 - 9:25:27 AM - Kun Lee Back To Top (36777)

Hi David,

 

Here is something you can read about the :connect. Basically, if you use the SQLCMD mode from SSMS, you can use that commend to connect any server that you need to connect. which allows you to connect many different servers within one scripts.

https://msdn.microsoft.com/en-us/library/ms174187.aspx

 

And TobyC,

I don't know how I missed your post. My appologies. I also have been really busy lately so haven't been able to catch up with any articles. In theory, you can use backup and restore of distribution database as long as you follow the right order and also assume you keep the same configurations as well as same server name. The point is to stop all agents (log reader, distribution agent and snapshot agent (which is givne but just in case) and there is some gotchas that it is not easy to explain in simple reply like this.

My appologies again.

 

Thank you,

Kun


Tuesday, March 31, 2015 - 5:34:32 AM - David Gray Back To Top (36772)

Hi, 

What does ":CONNECT" mean?  I can't find any documentation on this, is this script designed to run in somethign other that SSMS?  


Thursday, September 12, 2013 - 7:41:27 PM - TobyC Back To Top (26776)

Great resource about how to get stuff cleaned up.  I have a slightly different problem.  We have a server that houses multiple replicated databases, they are the merge kind (take snapshot, go to the field, come back and synch).  We had a new vendor install their software and it decided it wanted to remove the other replication publications completely without asking.  I have a backup of our distribution database "pre-disaster", is there a way to restore the settings of the replication publications without killing the new apps publication?  

You mentioned a future article --> Stay tuned for a future tip about auto generating replication scripts based on existing replication settings -above, will these scripts allow me to attach the backup as another database and script it to recreate it in the live distibutuion?

Thanks in advance

 



Wednesday, July 11, 2012 - 1:08:04 PM - Kun Lee Back To Top (18450)

Great point and Thanks Fran!


Wednesday, June 27, 2012 - 11:36:01 AM - Fran Back To Top (18225)

This article should be very good to clean up the replication settings. If it is not cleaned up well in SQL, this can cause the SQL Server upgrade failure. Thanks for the posting!















get free sql tips
agree to terms