Forcefully remove replication publisher, subscriber and distributor in SQL Server

Forcefully remove replication publisher, subscriber and distributor in SQL Server

I still remember one of my earlier post where I have mentioned how to remove replication and how to remove objects from replication. This is perfect solution for general use but sometime it happens that distributor database/server went crashed or distributor become unavailable or suspected and there is no chance to have that server/database back, we have to forcefully remove replication as regular or general practice wouldn’t work in that case.

When we remove publisher and/or subscriber, it updates distributor database with the latest information but in case we don’t have access of distributor database even we want to remove publisher/subscriber, we have to use “@ignore_distributor” option.

Here is the script to remove subscriber forcefully.

[sourcecode language=”sql”]

–Select your publication database

USE Adventureworks2012

GO

DECLARE @publication AS sysname;

DECLARE @subscriber AS sysname;

–enter your publication name

SET @publication = N’AdventureWorksPub’;

–enter subscriber name

SET @subscriber = N’AdventureWorksSub’;

USE [AdventureWorks2012]

EXEC sp_dropsubscription

@publication = @publication,

@article = N’all’,

@subscriber = @subscriber

,@ignore_distributor=1;

GO

[/sourcecode]

Here is the script to remove publisher forcefully.

[sourcecode language=”sql”]

DECLARE @publicationDB AS sysname;

DECLARE @publication AS sysname;

–set your publication database here

SET @publicationDB = N’AdventureWorks2012′;

–set your publication name here

SET @publication = N’AdventureWorksPub’;

— Remove a transactional publication.

USE [AdventureWorks2012]

EXEC sp_droppublication

@publication = @publication

,@ignore_distributor=1;

— Remove replication objects from the database.

USE [master]

EXEC sp_replicationdboption

@dbname = @publicationDB,

@optname = N’publish’,

@value = N’false’;

GO

[/sourcecode]

If Distributor database available and you wanted to remove it forcefully, have a look at following script:

[sourcecode language=”sql”]

–execute following command on distributor server

USE master

GO

EXEC sp_dropdistributor @no_checks = 1, @ignore_distributor = 1

GO

[/sourcecode]

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.