Skip to main content

Add new articles in existing publications without Reinitialize All Subscriptions - SQL Server Replication

The last time we saw a script to add tables, stored procedures, functions and indexed views in a publication and hope you may like that post. Let us continue here one more addition to post and see how can we add a new article in existing publication without reinitialize all subscriptions in transaction replication. Following are the steps which drive to finish this post,
Step  1 :
First add articles through the scripts provided in an earlier post, Here for samples we will add tables in existing publication and run on publisher database.
USE PublisherDB
EXEC sp_addarticle 
 @publication = N'PublicationName', 
 @article = N'TableName', 
 @source_owner = N'SchemaName', 
 @source_object = N'TableName', 
 @type = N'logbased', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x00000000080350DF, 
 @identityrangemanagementoption = N'manual', 
 @destination_table = N'TableName', 
 @destination_owner = N'SchemaName', 
 @status = 24, 
 @vertical_partition = N'false', 
 @ins_cmd = N'CALL [sp_MSins_SchemaNameTableName]', 
 @del_cmd = N'CALL [sp_MSdel_SchemaNameTableName]', 
 @upd_cmd = N'SCALL [sp_MSupd_SchemaNameTableName]',
 @force_invalidate_snapshot = 1
Step 2 :
After running above script in publisher database, run following script in the publisher database too.
USE PublisherDB
EXEC sp_refreshsubscriptions '<Publication Name>'
Step 3 :
Final completion both above steps we will just start snapshot agent for that publication from Replication Monitor.
Go to Replication Monitor
Select publication,
Move to Agents tab,
Right click on snapshot agent and start agent. 

You will see number of added articles in last action message there after completion of snapshot agent. This is just I want to share with you and maybe help you a lot. Thanks for reading this post and may you like to.