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

PowerShell TTUC #13 – Scheduled Jobs

PowerShell Tips, Tricks and Useful Commands (TTUC) #13 – Scheduled Jobs PowerShell scripts can be run as a scheduled job using using Windows scheduler.  Create a batch file with the following syntax/commands:   Powershell.exe “c:scriptsmytestscript.ps1” Read more…

Microsoft

PowerShell TTUC #15 – File Name with Time Stamp

PoweShell TTUC (Tips, Tricks and Useful Commands) #16 – File Name with Time Stamp File can be created with date / time suffix using the following syntax / commands: New-item -type file -Name (“MyFile_$(Get-Date -f Read more…

Microsoft

F5 VPN Plug-in and NPuroamHost.dll Issue

By default, the F5 VPN plug-in (F5 Networks Firepass Host Plugin) doesn’t install from Internet Explorer 11 browser.  If you try the manual installation option, you will get only the NPuroamHost.dll file. Copying and pasting Read more…