Adding article to existing publication without generating a full snapshot

This SP can be used to add articles to the existing publication without starting snapshot for all the tables.
When Snapshot is started it will create snapshot only for the newly added article to the publication

CREATE PROCEDURE sp_addarticles(@pubname VARCHAR(100),
@article VARCHAR(100),
@subscriber VARCHAR(100),
@sub_db VARCHAR(100),
@dest_article VARCHAR(100) = NULL)
AS
BEGIN
DECLARE @immsync INT
DECLARE @allowanyon INT

SELECT @immsync=immediate_sync, @allowanyon=allow_anyonmous FROM syspublications

sp_addarticle @publication=@pubname
@article=@article
@source_table=@article
@force_invalidate_snapshot=1
GO

IF @allowanyon = 1
BEGIN
EXEC sp_changepublication
@publication = @pubname,
@property = N'allow_anonymous',
@value = 'false'
GO
END

IF @immsync = 1
BEGIN
EXEC sp_changepublication
@publication = @pubname,
@property = N'immediate_sync',
@value = 'false'
GO
END

EXEC sp_addsubscription
@publication = @pubname
@article = @article,
@subscriber = @subscriber
@destination_db = @sub_db
@reserved='Internal'
END

Leave a Reply

Leave a comment

Your email address will not be published. Required fields are marked *

*


x

Related Posts

Dropping a Subscriber from AG Publisher
Today one of my colleague reached out to me for an help to drop a subscriber from an publisher which is participating in Always On. We seen same...
Performance View on INNER JOIN and OUTER JOIN
Hope you guys are getting ready for Diwali Festival :-). Before you guys start with the festival event I thought of writing an article on perform...
Synonyms - SQL Server
I was working in a project where we are using database mirroring as high availability solution. In the same instance we have multiple databases i...
powered by RelatedPosts