Tag Archives: Publisher

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.


--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

Here is the script to remove publisher forcefully.


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

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


--execute following command on distributor server

USE master

GO

EXEC sp_dropdistributor @no_checks = 1, @ignore_distributor = 1

GO

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.