Add article in Replication without initializing
By this way you are only creating a snapshot only for newly added article and no need to reinitialize all subscription.
Step1 : Create New Table on Publisher.
USE Adventureworks2012 CREATE TABLE ReplicationTest ( ID INT PRIMARY KEY, Test VARCHAR(20) )
Step2 : Add this table in Article.
First check Article exist or not in a replication.
--Check Exisitng Articles. SELECT sub.srvname AS SubscriptionServer, pub.name AS Publication, art.name AS ArticleName, art.dest_table AS DestinationArticle, art.dest_owner AS OwnerName FROM sysextendedarticlesview art INNER JOIN syspublications pub ON ( art.pubid = pub.pubid ) INNER JOIN syssubscriptions sub ON ( sub.artid = art.artid ) WHERE art.name = 'ReplicationTest' GO
Run this to add “ReplicationTest” table to your existing article.
--Script to add table in replication. EXEC Sp_addarticle @publication = N'AdventureWorks2012_Pub', @article = N'ReplicationTest', @source_owner = N'DBO', @source_object = N'ReplicationTest', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x00000000080350DF, @identityrangemanagementoption = N'manual', @destination_table = N'ReplicationTest', @destination_owner = N'DBO', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_DBOReplicationTest]', @del_cmd = N'CALL [sp_MSdel_DBOReplicationTest]', @upd_cmd = N'SCALL [sp_MSupd_DBOReplicationTest]', @force_invalidate_snapshot = 1 GO
Now check article added successfully.
--Now Check again for newly added article. SELECT sub.srvname AS SubscriptionServer, pub.name AS Publication, art.name AS ArticleName, art.dest_table AS DestinationArticle, art.dest_owner AS OwnerName FROM sysextendedarticlesview art INNER JOIN syspublications pub ON ( art.pubid = pub.pubid ) INNER JOIN syssubscriptions sub ON ( sub.artid = art.artid ) WHERE art.name = 'ReplicationTest' GO
Step3 : Refresh Subscription.
USE Adventureworks2012 go Sp_refreshsubscriptions 'Adventureworks2012_pub' -- Supply Publication Name
Step4 : Go to replication monitor screen –> Select Agent Tab
–>Select Snapshot Agent in Agent Type –>Right click on Agent and Start Again.
Step5 : Now Go to replication monitor screen –> Select Agent Subscription watch list
–> Double click on your subscription and snapshot applying only for newly added article.
Regards,
Nirav Gajjar