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
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.