You are currently offline, waiting for your internet to reconnect

How to manually remove a replication in SQL Server 2000 or in SQL Server 2005

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

This article was previously published under Q324401
SUMMARY
This article describes how to remove a replication from a computer that is running Microsoft SQL Server 2000 or Microsoft SQL Server 2005. To remove a replication, you must drop the subscriptions, the publications, and the distributor that is configured for the replication. You can remove the replication by running the Transact-SQL script that is generated by SQL Server Enterprise Manager or SQL Server Management Studio. However, if you cannot generate the Transact-SQL script to remove the replication, you can manually remove the replication by using system stored procedures and other Transact-SQL statements. This article contains information about system stored procedures that can be used in this process.

Note For additional information about the system stored procedures that are mentioned in this article, see SQL Server Books Online.

back to the top

Manually remove a replication

You can manually remove a replication by using system stored procedures and other Transact-SQL statements. To completely remove a replication, follow these steps:
  1. Drop all subscriptions that are configured for the replication.
  2. Drop all publications that are configured for the replication.
  3. Drop the distributor that is configured for the replication.
Note The system stored procedures for each type of replication are listed later in this article. Use the appropriate stored procedures, depending on the type of replication that you want to remove.

back to the top

Drop the subscriptions

To drop the subscriptions from an instance of SQL Server, you can use the following stored procedures and the appropriate parameters:
  • sp_dropsubscription: You can use the sp_dropsubscription system stored procedure to drop subscriptions to a particular article, publication, or set of subscriptions on Publisher. You must run the stored procedure at the Publisher server on the publication database.
  • sp_droppullsubscription: You can use the sp_droppullsubscription system stored procedure to drop a subscription at the current database of the Subscriber. You must run the stored procedure at the Subscriber on the pull subscription database.
  • sp_dropmergesubscription: You can use the sp_dropmergesubscription system stored procedure to drop a subscription to a merge publication and to the Merge Agent that is associated with the merge publication. You must run the stored procedure at the Publisher server on the publication database.
  • sp_dropmergepullsubscription: You can use the sp_dropmergepullsubscription system stored procedure to drop a merge pull subscription. You must run the stored procedure at the Subscriber on the pull subscription database.

Drop snapshot subscriptions

To drop a push subscription to all the articles for a snapshot publication, run the following script at Publisher:
USE <Publication database name>GOEXEC sp_dropsubscription @publication = N'<Publication name>', @article = N'all', @subscriber = N'all', @destination_db = N'all'
To drop a pull snapshot subscription to all the articles for a snapshot publication, follow these steps:
  1. Run the following SQLb script at the Subscriber:
    USE <Subscription database name>GOEXEC sp_droppullsubscription @publisher = N'<Publisher server name>', @publisher_db = N'<Publication database name>', @publication = N'<Publication name>'
  2. Run the following script at Publisher:
    USE <Publication database name>GOEXEC sp_dropsubscription @publication=N'<Publication name>', @subscriber = N'<Subscriber server name>', @article = N'all', @destination_db = N'all'

Drop a transactional subscription

To drop a push subscription to all articles for a transactional publication, run the following script at Publisher:
USE <Publication database name>GOEXEC sp_dropsubscription @publication = N'<Publication name>', @article = N'all', @subscriber = N'all', @destination_db = N'all'
To drop a pull subscription to all articles for a transactional publication, follow these steps:
  1. Run the following script at the Subscriber:
    USE <Subscription database name>GOEXEC sp_droppullsubscription @publisher = N'<Publisher server name>', @publisher_db = N'<Publisher database name>', @publication = N'<Publication name>'
  2. Run the following script at Publisher:
    USE <Publication database name>GOEXEC sp_dropsubscription @publication =N'<Publication name>', @subscriber = N'<Subscriber server name>', @article = N'all', @destination_db = N'<Destination database name>'

Drop a merge subscription

To drop a push subscription, run the following script at Publisher:
USE <Publication database name>GOEXEC sp_dropmergesubscription @publication = N'<Publication name>', @subscriber = N'<Publisher server name>', @subscriber_db = N'<Subscription database name>', @subscription_type = N'push'
To drop a pull subscription, follow these steps:
  1. Run the following script at the Subscriber:
    USE <Subscription database name>GOEXEC sp_dropmergepullsubscription @publication = N'<Publication name>', @publisher = N'<Publisher server name>', @publisher_db = N'<Publisher database name>'
  2. Run the following script at Publisher:
    USE <Publication database name>GOEXEC sp_dropmergesubscription @subscription_type = N'pull', @publication = N'<Publication name>', @subscriber = N'<Subscriber server name>', @subscriber_db = N'<Subscription database name>'
back to the top

Drop the publications

After you remove all the subscriptions that subscribe to a publication, you can remove the publication. After you remove the publications at the publication database, you must set the replication database option for the publication database to false. To remove a publication, you can use the following system stored procedures:
  • sp_droppublication: You can use the sp_droppublication system stored procedure to drop a publication and the articles that are associated with the publication. You must run the stored procedure at Publisher on the publication database.
  • sp_dropmergepublication: You can use the sp_dropmergepublication system stored procedure to drop a merge publication and the Snapshot Agent that is associated with the merge publication. The articles that are associated with the publication are also dropped. You must run the stored procedure at Publisher on the publication database.
  • sp_replicationdboption: You can use the sp_replicationdboption system stored procedure to set a replication database option for the current database. You must run the stored procedure at the Publisher server.
To drop a snapshot publication, run the following script at Publisher:
USE <Publication database name>GOEXEC sp_droppublication @publication = N'<Publication name>'USE masterGOexec sp_replicationdboption @dbname = N'<Publication database name>', @optname = N'publish', @value = N'false'
To drop a transactional publication, run the following script at Publisher:
USE <Publication database name>GOEXEC sp_droppublication @publication = N'<Publication name>'USE masterGOEXEC sp_replicationdboption @dbname = N'<Publication database name>', @optname = N'publish', @value = N'false'
To drop a merge publication, run the following script at Publisher:
USE <Publication database name>GOEXEC sp_dropmergepublication @publication = N'<Publication name>'USE masterGOEXEC sp_replicationdboption @dbname = N'<Publication database name>', @optname = N'merge publish', @value = N'false'
back to the top

Drop the distributor

After you drop all the subscriptions and the publications, you can drop the relevant Distributor. However, before you drop the distributor, you must drop the subscriber designation from Publisher. To do so, use the following stored procedures:
  • sp_dropsubscriber: You can use the sp_dropsubscriber system stored procedure to drop the subscriber designation from a registered server. The stored procedure removes the registry entry for the subscriber. The stored procedure is run at Publisher on the publication database.
  • sp_dropdistributor: You can use the sp_dropdistributor system stored procedure to remove the distributor. The stored procedure is run at the distributor.
To drop the subscriber designation from Publisher, run the following script at Publisher:
USE masterGOEXEC sp_dropsubscriber @subscriber = N'<Subscriber server name>', @reserved = N'drop_subscriptions'
To remove the distributor, run the following script at the distributor:
USE masterGOEXEC sp_dropdistributor @no_checks = 1
back to the top

Use stored procedures

You can also use the following stored procedures when you remove the replication:
  • sp_removedbreplication: You can use the sp_removedbreplication system stored procedure to remove all the replication objects from a database without updating the data at the distributor. You must run the stored procedure at Publisher on the publication database or at the Subscriber on the subscription database. The following is the syntax for this stored procedure:
    sp_removedbreplication '<Database name>'
  • sp_droparticle: You can use the sp_droparticle system stored procedure to drop an article from a snapshot publication or from the transactional publication. You cannot remove an article if one or more subscriptions to the published article still exist. You must run the stored procedure at Publisher on the publication database. The following is the syntax for this stored procedure:
    sp_droparticle @publication = N'<Publication name>', @article = N'<Article name>', @force_invalidate_snapshot = 1
back to the top
REFERENCES
For more information, see the following topics in SQL Server Books Online:
  • How to Disable Publishing and Distribution (Transact-SQL)
  • How to Delete Publications and Articles (Transact-SQL)
  • How to Delete a Push Subscription (Transact-SQL)
  • How to Delete a Pull Subscription (Transact-SQL)
  • How to Delete an Anonymous Subscription (Transact-SQL)
  • How to delete publications and articles (Enterprise Manager)
  • How to delete a push subscription (Enterprise Manager)
  • How to delete a pull or anonymous subscription (Enterprise Manager)
back to the top
Properties

Article ID: 324401 - Last Review: 11/23/2005 17:08:10 - Revision: 3.3

Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Workgroup Edition

  • kbhowtomaster kbreplication kbstoredproc KB324401
Feedback
V = 1; var varClickTracking = 1; var varCustomerTracking = 1; var Route = "76500"; var Ctrl = ""; document.write("