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
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.
NoteSpecifying a value of 1 for @force may leave replication-related publishing objects in the database.
(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.
(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
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.
NoteSpecifying a value of 1 for @force may leave replication-related publishing objects in the database.
(Optional) If this database has no other publications, execute sp_replicationdboption (Transact-SQL) to disable publication of the current database using merge replication.
(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
