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".
