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
Tags: Replication, scripts, sugesh, TSQL
Trackback from your site.
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
| #
HI Naveed,
Could you please check the article wrote by deepak on fulltext? http://sql-articles.com/articles/dba/sql-server-2005-full-text-search/
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.