Remove replication completely 15 – SQL Server Blog Forum

Advertisements
Remove replication completely
--https://jonathancrozier.com/blog/sql-server-replication-how-to-completely-remove-replication

/*
--on sub
-- Remove replication objects from the subscription database on MYSUB.
DECLARE @subscriptionDB AS sysname
SET @subscriptionDB = N'Muthu_Replica'

-- Remove replication objects from a subscription database (if necessary).
USE master
EXEC sp_removedbreplication @subscriptionDB
GO
*/

--on pub

 --Manually remove subscriber
 --Manually remove publication

-- Declare and set variables.
DECLARE @distributionDB SYSNAME;
DECLARE @publisher      SYSNAME;
DECLARE @publicationDB  SYSNAME;
 
SET @distributionDB = N'distribution';   -- CHANGE THIS TO THE DISTRIBUTION DATABASE NAME.
SET @publisher      = N'node1';   -- CHANGE THIS TO THE PUBLISHER NAME.
SET @publicationDB  = N'muthu'; -- CHANGE THIS TO THE PUBLICATION DATABASE NAME.
 
-- Remove all replication objects from the publication database.
USE muthu -- CHANGE THIS TO THE PUBLICATION DATABASE NAME.
EXEC sp_removedbreplication @publicationDB;
 
-- Remove the publisher registration at the distributor.
USE master
EXEC sp_dropdistpublisher @publisher;
 
-- Delete the distribution database.
EXEC sp_dropdistributiondb @distributionDB;
 
-- Uninstall the local server as a distributor.
EXEC sp_dropdistributor;
use muthu
select 'Drop view ['+name+']',* from sys.objects where name like 'sync%'

--If above is not working use this method 
 use [master]
exec sp_dropdistributor @no_checks = 1
Muthukkumaran
