Applies To: SQL Server
Drops a distribution database. Drops the physical files used by the database if they are not used by another database. This stored procedure is executed at the Distributor on any database.
sp_dropdistributiondb [ @database= ] 'database'
[ @database=] 'database'
Is the database to drop. database is sysname, with no default.
0 (success) or 1 (failure)
sp_dropdistributiondb is used in all types of replication.
This stored procedure must be executed before dropping the Distributor by executing sp_dropdistributor.
sp_dropdistributiondb also removes a Queue Reader Agent job for the distribution database, if one exists.
To disable distribution, the distribution database must be online. If a database snapshot exists for the distribution database, it must be dropped before disabling distribution. A database snapshot is a read-only offline copy of a database, and is not related to a replication snapshot. For more information, see Database Snapshots (SQL Server).
-- 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'AdventureWorks2012'; -- Disable the publication database. USE [AdventureWorks2012] 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
Only members of the sysadmin fixed server role can execute sp_dropdistributiondb.