How can I remove all transactional publications from a Microsoft Sql Server database?

Question

My database has a lot of subscribers to a lot of tables using Sql Server replication.

When I try to delete a table or delete a column on my database that takes part in a subscription on a published database the database migration fails.

Those subscribers check if they publication is still live and if not they re-subscribe.

I need to remove all publications from the current database via T-SQL so when I automate my database migrations delete columns or delete tables without the migration failing and needing manual intervention.

If you've found the answer, can you select it as the correct answer, so this disappears from the unanswered queue please. — Dave C, Jul 15 '14 at 15:03

regisbsb · Accepted Answer · 2014-07-15T14:56:41.717

To delete all transactional publications from a server run the following script on the database you are connected:

declare @PublicationName varchar(max)
declare @ArticleName varchar(max)
declare @SubscriberServerName varchar(max)
declare @DestinationDb varchar(max)

IF OBJECT_ID('dbo.syspublications') is not null
BEGIN
      DECLARE db_cursor CURSOR FOR  
            select  
                   sp.name as PublicationName 
                   ,sa.name as TableName 
                  , UPPER(srv.srvname) as SubscriberServerName  
                  , dest_db as DestinationDb
                  from dbo.syspublications sp  
                  join dbo.sysarticles sa on sp.pubid = sa.pubid 
                  join dbo.syssubscriptions s on sa.artid = s.artid 
                  join master.dbo.sysservers srv on s.srvid = srv.srvid 

      OPEN db_cursor   
      FETCH NEXT FROM db_cursor INTO @PublicationName, @ArticleName, @SubscriberServerName, @DestinationDb

      WHILE @@FETCH_STATUS = 0   
      BEGIN   
            -- Dropping the transactional subscriptions
            exec sp_dropsubscription @publication = @PublicationName, @subscriber = @SubscriberServerName, @destination_db = @DestinationDb, @article = N'all'

            -- Dropping the transactional articles
            exec sp_dropsubscription @publication = @PublicationName, @article = @ArticleName, @subscriber = N'all', @destination_db = N'all'

            exec sp_droparticle @publication = @PublicationName, @article = @ArticleName, @force_invalidate_snapshot = 1

            -- Dropping the transactional publication
            exec sp_droppublication @publication = @PublicationName

            FETCH NEXT FROM db_cursor INTO @PublicationName, @ArticleName, @SubscriberServerName, @DestinationDb
      END   

      CLOSE db_cursor   
      DEALLOCATE db_cursor
END

score 0 · Answer 2 · answered Jul 16 '14 at 08:41

0

Run this procedure on database which you like to have clean for replication objects sp_removedbreplication http://technet.microsoft.com/en-us/library/ms188734%28v=sql.105%29.aspx

answered Jul 16 '14 at 08:41

grillazz

527
6
15

The problem it is when this stored procedure is executed at the Publisher on the publication database or at the Subscriber on the subscription database, it removes all replication objects from the database in which it is executed, but it does not remove objects from other databases, such as the distribution database. – regisbsb Jul 16 '14 at 09:24
This procedure should be used only if other methods of removing replication objects have failed. For more information about these methods, see Removing Replication: http://technet.microsoft.com/en-us/library/ms152757(v=sql.105).aspx – regisbsb Jul 16 '14 at 09:24

How can I remove all transactional publications from a Microsoft Sql Server database?

2 Answers2