Clarification on MSDN Initialize a Transactional Subscription from a Backup


Chris Skorlinski
Microsoft SQL Escalation Services

I hope this blog posting will provide some clarification into the MSDN article providing steps on how to initialize a transactional subscription from a backup.

https://msdn.microsoft.com/en-us/library/ms147834.aspx

First I want to clarify the @backupdevicename parameter. The article reads like the “DISK = “should be included in the parameter, it should not.

For a physical device, specify a complete path and file name, such as DISK = ‘C:\Program Files\Microsoft SQL
Server\MSSQL13.MSSQLSERVER\BACKUP\Mybackup.dat’
or TAPE = ‘\\.\TAPE0’

Example:

@backupdevicename = ‘C:\Program Files\Microsoft SQLServer\MSSQL13.MSSQLSERVER\BACKUP\Mybackup.dat’

or

@backupdevicename = ‘\\.\TAPE0’

I also wanted to clear up some confusion over PULL subscriptions. For PULL subscription you must execute step 5 (sp_addsubscription) on the publisher in addition to the sp_addpullsubscription and sp_addpulsubscription_agents on the subscriber. The sp_addsubscription command, executed on the publisher, needs access to the Published database backup file to determine the starting point for new transactions. Simple keep a copy on the publisher or accessible via network when running sp_addsubscription.

If the publisher database backup files are split backup files such as Mybackup_1.dat and Mybackup_2.dat, I recommend taking one transaction log backup and applying that to the subscriber prior to creating the subscription. If the last backup applied to the subscriber was a transaction log backup, like \BACKUP\Mybackup.trn, then point the @backupdevicename parameter to that backup file when adding the subscriber.

 

 

 

 


Comments (0)

Skip to main content