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

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.

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.

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>

GO

EXEC 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:

2.  USE <Subscription database name>

3.  GO

EXEC sp_droppullsubscription @publisher = N'<Publisher server name>', @publisher_db = N'<Publication database name>', @publication = N'<Publication name>'

4.      Run the following script at Publisher:

5.  USE <Publication database name>

6.  GO

EXEC 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>

GO

EXEC 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:

2.  USE <Subscription database name>

3.  GO

EXEC sp_droppullsubscription @publisher = N'<Publisher server name>', @publisher_db = N'<Publisher database name>', @publication = N'<Publication name>'

4.      Run the following script at Publisher:

5.  USE <Publication database name>

6.  GO

EXEC 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>

GO

EXEC 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:

2.  USE <Subscription database name>

3.  GO

EXEC sp_dropmergepullsubscription @publication = N'<Publication name>', @publisher = N'<Publisher server name>', @publisher_db = N'<Publisher database name>'

4.      Run the following script at Publisher:

5.  USE <Publication database name>

6.  GO

EXEC sp_dropmergesubscription @subscription_type = N'pull', @publication = N'<Publication name>', @subscriber = N'<Subscriber server name>', @subscriber_db = N'<Subscription database name>'

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>

GO

EXEC sp_droppublication @publication = N'<Publication name>'

USE master

GO

exec 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>

GO

EXEC sp_droppublication @publication = N'<Publication name>'

USE master

GO

EXEC 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>

GO

EXEC sp_dropmergepublication @publication = N'<Publication name>'

USE master

GO

EXEC sp_replicationdboption @dbname = N'<Publication database name>', @optname = N'merge publish', @value = N'false'

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 master

GO

EXEC sp_dropsubscriber @subscriber = N'<Subscriber server name>', @reserved = N'drop_subscriptions'

To remove the distributor, run the following script at the distributor:

USE master

GO

EXEC sp_dropdistributor @no_checks = 1

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

Source:
http://support.microsoft.com/kb/324401