Drop Article and Re-add without Reinitializing

Sometimes one article in a healthy publication needs to be dropped and re-added - for example after a column change that broke the filter or trigger. Done carelessly this forces a full reinitialization of every subscriber; done carefully it only takes a mini-snapshot. This script walks through the whole sequence: a query listing the subscriptions you are about to touch, sp_dropsubscription and sp_droparticle with @force_invalidate_snapshot = 1, sp_addsubscription to put the article back, and the manual steps at the end (add any new columns at the subscriber, run the snapshot agent for the mini-snapshot, then start the distribution agent). The warning in the middle is important: do not run any procedure with @force_reinit_subscription set to 0 while doing this.

--Get list of subscriptions:
SELECT DISTINCT
	Pub.name AS Publication,
	--Art.name AS Article,
	Sub.srvname AS Subscriber,
	Sub.dest_db AS DestinationDatabase
FROM
	syssubscriptions AS Sub,
	syspublications AS Pub,
	sysextendedarticlesview AS Art
WHERE
	Art.pubid = Pub.pubid
	AND	Sub.artid = Art.artid

/*
--Drop the subscription
EXEC sp_dropsubscription
	@publication='publication_name',
    @article='article_name',
    @subscriber='subscriber_server',
    @destination_db='destination_db_name',
    @ignore_distributor=0

--Drop the article:
EXEC sp_droparticle
	@publication='publication_name',
	@article='article_name',
    @ignore_distributor=0,
    @force_invalidate_snapshot=1
*/


/*
	------------------------------------------------------------------------------------
	--Voodoo to re-add publication article and its columns
	--This can be obtained by scriptng the publications.
	------------------------------------------------------------------------------------
	--**BE SURE** **NOT TO** run any procedures that support @force_reinit_subscription
	--	with @force_reinit_subscription set to 0. Thats Zero! (0) 0 OK?!
	------------------------------------------------------------------------------------
*/

/*
--Re-add the subscription to the article.
EXEC sp_addsubscription
	@publication = 'publication_name',
    @article = 'article_name',
    @subscriber =  'subscriber_server',
    @destination_db =  'destination_db_name',
    @sync_type =  'automatic ',
    @subscription_type = 'push',
    @update_mode =  'read only'
*/

/*
--You will now need to manually add any new columns to the
--	destination table at the subscriber, re-run the snapshot agent
--	which will run a mini-snapshot... then kick off the distributor.
*/