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.
Amit Bhatt
| #
Hi Deepak,
Thanks for such a nice article.
You missed one thing to add in code:
@article = ‘all’,
Hence the script will be like this:
EXEC sp_addsubscription
@publication = ‘mypublication’,
@article = ‘ALL’,
@subscriber = ‘Subscriberservername’,
@destination_db = ‘mydestinationdbname’,
@reserved=’Internal’
Error 18486 | Platformblog
| #
[...] SQL-Articles » Troubleshooting Login failed Error 18456This is one of the infamous error message (and number) that most of the DBAs …. 18486. Login failed for user ‘%.*ls’ because the account is currently locked out. [...]
Setting and Changing Collation – SQL Server 2008 « Blog
| #
[...] to sql-articles.comRead more: http://sql-articles.com/articles/dba/how-to-change-server-collation-in-sql-server-2008/#ixzz1pu2S8XW… Like this:LikeBe the first to like this [...]
VidhyaSagar
| #
Naveen,
I’ll check this out and get back to you.
balakiran
| #
Thanks man, Very simple & easy to understand !!!!