How to: Disable Publishing and Distribution (Replication Transact-SQL Programming)
Publishing and distributing can be disabled programmatically using replication stored procedures.
To disable publishing and distribution
Stop all replication-related jobs. For a list of job names, see the "Agent Security Under SQL Server Agent" section of Replication Agent Security Model.
At each Subscriber on the subscription database, execute sp_removedbreplication (Transact-SQL) to remove replication objects from the database. This stored procedure will not remove replication jobs at the distributor.
At the Publisher on the publication database, execute sp_removedbreplication (Transact-SQL) to remove replication objects from the database.
If the Publisher uses a remote Distributor, execute sp_dropdistributor (Transact-SQL).
At the Distributor, execute sp_dropdistpublisher (Transact-SQL). This stored procedure should be run once for each Publisher registered at the Distributor.
At the Distributor, execute sp_dropdistributiondb (Transact-SQL) to delete the distribution database. This stored procedure should be run once for each distribution database at the Distributor. This also removes any Queue Reader Agent jobs associated with the distribution database.
At the Distributor, execute sp_dropdistributor (Transact-SQL) to remove the Distributor designation from the server.
Note
If all replication publishing and distribution objects are not dropped before executing sp_dropdistpublisher (Transact-SQL) and sp_dropdistributor (Transact-SQL), these procedures will return an error. To drop all replication-related objects when a Publisher or Distributor is dropped, the @no_checks parameter must be set to 1. If a Publisher or Distributor is offline or unreachable, the @ignore_distributor parameter can be set to 1 so that they can be dropped; however, any publishing and distributing objects left behind must be removed manually.
Example
This example script removes replication objects from the subscription database.
-- Remove replication objects from the subscription database on MYSUB.
DECLARE @subscriptionDB AS sysname
SET @subscriptionDB = N'AdventureWorksReplica'
-- Remove replication objects from a subscription database (if necessary).
USE master
EXEC sp_removedbreplication @subscriptionDB
GO
This example script disables publishing and distribution on a server that is a Publisher and Distributor and drops the distribution database.
-- This script uses sqlcmd scripting variables. They are in the form
-- $(MyVariable). For information about how to use scripting variables
-- on the command line and in SQL Server Management Studio, see the
-- "Executing Replication Scripts" section in the topic
-- "Programming Replication Using System Stored Procedures".
-- Disable publishing and distribution.
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
DECLARE @publicationDB as sysname;
SET @distributionDB = N'distribution';
SET @publisher = $(DistPubServer);
SET @publicationDB = N'AdventureWorks';
-- Disable the publication database.
USE [AdventureWorks]
EXEC sp_removedbreplication @publicationDB;
-- Remove the registration of the local Publisher at the Distributor.
USE master
EXEC sp_dropdistpublisher @publisher;
-- Delete the distribution database.
EXEC sp_dropdistributiondb @distributionDB;
-- Remove the local server as a Distributor.
EXEC sp_dropdistributor;
GO
See Also
Tasks
How to: Disable Publishing and Distribution (RMO Programming)
Concepts
Programming Replication Using System Stored Procedures