ReplTip – Subscribe to “all” restriction using “Initialize with backup”

ReplTip – Subscribe to “all” restriction using “Initialize with backup”

Chris Skorlinski
Microsoft SQL Server Escalation Services

I ran into a couple of challenges using the Backup\Restore method to setup a SQL Server Replication Subscriber.  In particular, using this method you are unable to subscribe or un-subscribe to an individual article.  Using Backup\Restore you mush subscribe to ALL articles in the Publication.

@sync_type='initialize with backup' … @article=’Customer’
Msg 21407, Level 16, State 1, Procedure sp_MSaddautonosyncsubscription, Line 360
Cannot create the subscription. If you specify a value of "initialize with backup" for the @sync_type parameter, you must subscribe to all articles in the publication by specifying a value of "all" for the @article parameter.

The work around, @sync_type = N'replication support only', provides the most flexibility.  Still using database Backup\Restore processes to setup a subscriber, but you now have ability for individual subscribers to select just those articles for which they need to subscribe.  Subscribers execute multiple sp_addsubscription, one for each article to subscribe.

exec sp_addsubscription …  @sync_type = N'replication support only'…@article =’<individual article>’

While this offers the most flexibility, careful planning is required when using @sync_type = N'replication support only'.  You must  ensure the data is in perfect match between Publisher and Subscriber when replication is configured.  If there are in-flight data changes you could end up with missing data and Distribution Agent failing days, weeks, or months later with "Row not found".

Comments (0)

Skip to main content