Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All
Expand Minimize

How to: Delete a Publication (Replication Transact-SQL Programming)

Publications can be deleted programmatically using replication stored procedures. The stored procedures that you use depend on the type of publication being deleted.

NoteNote

Deleting a publication does not remove published objects from the publication database or the corresponding objects from the subscription database. Use the DROP <object> command to manually remove these objects if necessary.

To delete a snapshot or transactional publication

  1. Do one of the following:

    • To delete a single publication, execute sp_droppublication at the Publisher on the publication database.

    • To delete all publications in and remove all replication objects from a published database, execute sp_removedbreplication at the Publisher. Specify a value of tran for @type. (Optional) If the Distributor cannot be accessed or if the status of the database is suspect or offline, specify a value of 1 for @force. (Optional) Specify the name of the database for @dbname if sp_removedbreplication is not executed on the publication database.

      NoteNote

      Specifying a value of 1 for @force may leave replication-related publishing objects in the database.

  2. (Optional) If this database has no other publications, execute sp_replicationdboption (Transact-SQL) to disable publication of the current database using snapshot or transactional replication.

  3. (Optional) At the Subscriber on the subscription database, execute sp_subscription_cleanup to remove any remaining replication metadata in the subscription database.

To delete a merge publication

  1. Do one of the following:

    • To delete a single publication, execute sp_dropmergepublication (Transact-SQL) at the Publisher on the publication database.

    • To delete all publications in and remove all replication objects from a published database, execute sp_removedbreplication at the Publisher. Specify a value of merge for @type. (Optional) If the Distributor cannot be accessed or if the status of the database is suspect or offline, specify a value of 1 for @force. (Optional) Specify the name of the database for @dbname if sp_removedbreplication is not executed on the publication database.

      NoteNote

      Specifying a value of 1 for @force may leave replication-related publishing objects in the database.

  2. (Optional) If this database has no other publications, execute sp_replicationdboption (Transact-SQL) to disable publication of the current database using merge replication.

  3. (Optional) At the Subscriber on the subscription database, execute sp_mergesubscription_cleanup (Transact-SQL) to remove any remaining replication metadata in the subscription database.

This example shows how to remove a transactional publication and disable transactional publishing for a database. This example assumes that all subscriptions were previously removed. For more information, see How to: Delete a Pull Subscription (Replication Transact-SQL Programming) or How to: Delete a Push Subscription (Replication Transact-SQL Programming).

DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
SET @publicationDB = N'AdventureWorks2008R2'; 
SET @publication = N'AdvWorksProductTran'; 

-- Remove a transactional publication.
USE [AdventureWorks2008R2]
EXEC sp_droppublication @publication = @publication;

-- Remove replication objects from the database.
USE [master]
EXEC sp_replicationdboption 
  @dbname = @publicationDB, 
  @optname = N'publish', 
  @value = N'false';
GO


This example shows how to remove a merge publication and disable merge publishing for a database. This example assumes that all subscriptions were previously removed. For more information, see How to: Delete a Pull Subscription (Replication Transact-SQL Programming) or How to: Delete a Push Subscription (Replication Transact-SQL Programming).

DECLARE @publication AS sysname
DECLARE @publicationDB    AS sysname
SET @publication = N'AdvWorksSalesOrdersMerge' 
SET @publicationDB = N'AdventureWorks2008R2'

-- Remove the merge publication.
USE [AdventureWorks2008R2]
EXEC sp_dropmergepublication @publication = @publication;

-- Remove replication objects from the database.
USE master
EXEC sp_replicationdboption 
  @dbname = @publicationDB, 
  @optname = N'merge publish', 
  @value = N'false'
GO


Community Additions

ADD
Show:
© 2015 Microsoft