Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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.
Note
Specifying 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.
Note
Specifying 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.
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
See Also
Tasks
How to: Delete a Publication (RMO Programming)
Concepts
Programming Replication Using System Stored Procedures
Other Resources
How to: Delete a Publication (SQL Server Management Studio)
Removing Replication