Adding article to existing publication without generating a full snapshot

Written by Sugeshkumar Rajendran. Posted in Scripts

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
VN:F [1.9.13_1145]
Rating: 0.0/5 (0 votes cast)
VN:F [1.9.13_1145]
Rating: 0 (from 0 votes)

Tags: , , ,

Trackback from your site.

Leave a comment

*

Recent Comments

DotNetShoutout

|

SQL-Articles ยป Script to get data file usage and autogrowth details…

Thank you for submitting this cool story – Trackback from DotNetShoutout…

VidhyaSagar

|

Saeed,
I don’t think so you can stop it in SMO, instead you need to turn off password policy for that login

VidhyaSagar

|

Thanks Ashish, I’ve updated the script.

Ashish

|

I was just searching some trace related articles and gone through your article. Found one incorrect information,
to disable the trace, the command should be
dbcc traceoff (….)
you might have by mistake mentioned it as traceon for disabling as well.