مشاركة عبر

  • مقالة

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.

Note

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.

      Note

      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.

      Note

      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.

Example

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'AdventureWorks'; 
SET @publication = N'AdvWorksProductTran'; 

-- Remove a transactional publication.
USE [AdventureWorks]
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'AdventureWorks'

-- Remove the merge publication.
USE [AdventureWorks]
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