SQL Server script to remove replication?

Question

I have been asked to write a SQL script that can be run, which will stop replication and remove the subscriber/subscription.

Is this at all possible? Or do you have to use the GUI?

Answer 1

Which version of SQL Server you are using.

Try

sp_removedbreplication 'DB_PROD' 
go

Related Read - How to cleanup Replication Bits - http://blogs.msdn.com/b/repltalk/archive/2010/11/17/how-to-cleanup-replication-bits.aspx

Google as well provides MSDN article in results

How to: Disable Publishing and Distribution (Replication Transact-SQL Programming) - http://msdn.microsoft.com/en-us/library/ms147921.aspx

Answer 2

If you're look to completely remove replication, including all the "bits". Or your server only has one publication and this is the one you're trying to remove.

I would recommend:

1. Connect to the Publisher or Distributor you want to disable in Microsoft SQL Server Management Studio, and then expand the server node.
2. Right-click the Replication folder, and then click Disable Publishing and Distribution.
3. Follow the steps in the Disable Publishing and Distribution Wizard, and instead of processing select the option to generate scripts.

This has the advantage of not only completely cleaning things up. But also removing the distribution database, which if you've ever dealt with before is known to become littered with remnants of replications past.

Full documentation can be found here.