Remove an Article from Transactional Replication without dropping the Subscription

          While using Transactional Replication, there may come a time when you need to drop an article from a publication, a publication that may have one or more subscriptions. You would think that the process would be very similar to adding an article to the publication; open up the article and uncheck the box. Now, if there were no subscribers to the publication, this would not be an issue, but odds are that this publication has been used for some time and there are one or more subscribers to it. If this is the case, when you go to uncheck the article to remove it, you may receive an message stating that since there are subscriptions on this table, you will have to reinitialize this subscription and generate a new snapshot.

          So how can we get around re-initialization of the subscriber and new snapshot generation? We manually execute some of the replication stored procedures to remove the article and keep the snapshot from being invalidated.

First we must use sp_dropsubscription to remove the subscription to the individual article.

EXEC sys.sp_dropsubscription

    @publication = '<PublicationName>',
    @article = '<ArticleToDrop>',
    @subscriber = '<SubscribingServer>',
    @destination_db = '<DestinationDatabase>'
 
Next, we drop the article from the publication without invalidating the snapshot. We do that by executing sp_droparticle with the force_invalidate_snapshot set to 0.

EXEC sys.sp_droparticle @publication = '<PublicationName>',
    @article = '<ArticleToDrop>',
    @force_invalidate_snapshot = 0