How to add an article to an existing Transactional Subscription initialized through backup
Sakthivel Chidambaram | Microsoft SQL Server Support
There are situations where you initialize a Transactional subscription from a backup and later you need to add an article. If the publication was created using Snapshot Replication, you can re-run the Snapshot Agent and only the newly added article is BCP’ed out then BCP’ed in at the subscriber. However when setting up the Subscriber using backup/restore there is no Snapshot agent.
There are two options when a new tables needs to be published.
- Create a NEW publication for just that article. Use Publication Wizard or script and select default options to push a new snapshot to the subscriber. One LogReader will handle both publications, while separate Distribution Agents will move data in parallel to the subscriber.
- Use the steps below to add the NEW article to an existing Publication and manually sync the data before restarting Replication.
Here are the steps for second option:
1. Stop the log reader agent
2. Stop making changes to the new article in the Publisher (Note that table should be quiesced to all changes while the data is being copied out from the publisher and until the table is added to the publication with sp_addarticle)
3. Generate a script to create the table on the subscriber and copy the data out of the article from Publisher to Subscriber. A. For new or small tables you can use INSERT INTO… SELECT * FROM B. For tables with a lot of existing data, create a SSIS package that creates and then load the table on the remote server
4. Add the article to the publication using sp_addarticle
5. Start Log Reader Agent
6. Log Reader will pick up new rows update from transactions log for the added article.
Please note that if the backup already had the article and if it is still present in the subscriber, and provided there is no schema change to that table, you don’t need to re-create it again in the subscriber.
Also compare the row count of the table being added between publisher and subscriber before starting log reader agent to make sure that both publisher and subscriber have the same amount of data. Ensure the data matches reduces changes for Distribution Agents failing with “row not found”.
Related articles:
SQL BOL: How to: Initialize a Transactional Subscription from a Backup (Replication Transact-SQL Programming)
ReplTalk Blog: How to manually synchronize replication subscriptions by using backup or restore
ReplTalk Blog: How to: Initialize a Transactional Subscription from a Backup with Multiple Backup Files
ReplTalk Blog: Deep Dive on Initialize from Backup for Transactional Replication
posted by Chris Skorlinski
Hi Microsoft SQL Server Support
I added an article to an existing Transactional Subscription initialized through backup use this post，but i found the logreader always execute sp_repldone the last lsn after logreader started , so ,lost many transactions . why ?
I have tried adding a new article using this post, but stored procedure that are responsible to propogate commands to subscription are not getting created. SP_MSIns, SP_MSDel, SP_MSUpd etc.,. Please let me know how to solve this.
This is a sample script to add an article for replication with a transactional replication created with backup and restore:
:setvar PublicationDatabase DatabaseName
:setvar PublicationName DatabasePublication
:setvar TableName NewTable
use $(PublicationDatabase)
exec sp_addarticle @publication = N'$(PublicationName)', @article = N'$(TableName)', @source_owner = N'dbo', @source_object = N'$(TableName)', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @force_invalidate_snapshot = 1, @identityrangemanagementoption = N'manual', @destination_table = N'$(TableName)', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dbo$(TableName)', @del_cmd = N'CALL sp_MSdel_dbo$(TableName)', @upd_cmd = N'SCALL sp_MSupd_dbo$(TableName)'
exec sp_refreshsubscriptions @publication = N'$(PublicationName)'
Make sure to click in Query -> SQLCMD Mode to enable the :setvar parameters
Thanks!
I've done some testing on this, and believe it only works on 2008R2 and higher. Thoughts?
It's possible to add article but you need to change somes options on publication and update system table for subscribtion :
http://www.concatskills.com/…/replication-initialisation-par-sauvegarde
Hi there everyone, it’s my first pay a quick visit at this web site,
and article is in fact fruitful for me, keep up posting these types of posts.
Nice response in return of this query with solid arguments and telling everything regarding that.
May I simply say what a comfort to uncover somebody who truly understands what they’re talking about on the
internet. You actually understand how to bring a problem to light and make it important.
A lot more people really need to check this out and
understand this side of your story. I was surprised you’re not
more popular because you definitely possess the gift.
Excellent web site you’ve got here.. It’s hard to find quality writing like yours
these days. I truly appreciate individuals like you!
Take care!!