Categories
Scripts

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 Reply

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

*