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 GO
EXEC sp_dropsubscription @publication = N’‘, @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:
Run the following SQLb script at the Subscriber:
USE
GO
EXEC sp_droppullsubscription @publisher = N’‘, @publisher_db = N’‘, @publication = N’
Run the following script at Publisher:
USE GO
EXEC sp_dropsubscription @publication=N’‘, @subscriber = N’‘, @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 GO
EXEC sp_dropsubscription @publication = N’‘, @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:
Run the following script at the Subscriber:
USE
GO
EXEC sp_droppullsubscription @publisher = N’‘, @publisher_db = N’‘, @publication = N’
Run the following script at Publisher:
USE GO
EXEC sp_dropsubscription @publication =N’‘, @subscriber = N’‘, @article = N’all’, @destination_db = N’
Drop a merge subscription

To drop a push subscription, run the following script at Publisher:
USE GO
EXEC sp_dropmergesubscription @publication = N’‘, @subscriber = N’‘, @subscriber_db = N’‘, @subscription_type = N’push’
To drop a pull subscription, follow these steps:
Run the following script at the Subscriber:
USE
GO
EXEC sp_dropmergepullsubscription @publication = N’‘, @publisher = N’‘, @publisher_db = N’
Run the following script at Publisher:
USE GO
EXEC sp_dropmergesubscription @subscription_type = N’pull’, @publication = N’‘, @subscriber = N’‘, @subscriber_db = N’
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 GO
EXEC sp_droppublication @publication = N’

USE master
GO
exec sp_replicationdboption @dbname = N’‘, @optname = N’publish’, @value = N’false’
To drop a transactional publication, run the following script at Publisher:
USE GO
EXEC sp_droppublication @publication = N’

USE master
GO
EXEC sp_replicationdboption @dbname = N’‘, @optname = N’publish’, @value = N’false’
To drop a merge publication, run the following script at Publisher:
USE GO
EXEC sp_dropmergepublication @publication = N’

USE master
GO
EXEC sp_replicationdboption @dbname = N’‘, @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’‘, @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 ‘
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:


Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts

Microsoft

Verify Service Status Remotely Using Local Account – PowerShell Script

I have modified one of my previously published script – Stop, Start, Disable Service Remotely–PowerShell Script (http://portal.sivarajan.com/2011/05/stop-start-disable-service.html) to use Local account (instead of a domain account) to verify the status of the service.   Input Read more…

Microsoft

Collect Computer Information From Active Directory– PowerShell Script

This PowerShell script can be used to collect computer information from Active Directory.   I am searching only Windows XP and Windows 7 machines.  You can update these values  by modifying $OS1 and $OS2 variables. $OS1 Read more…

Microsoft

PowerShell – Tips, Tricks and Useful Commands

Tip #4 –   #4                       Tip #3 – Comparison Operators Published date – May12, 2013 -eq   -ne   -gt   -ge   -lt Read more…