I created a publication on my server. I had to use this special option so that I could initialize replication from a backup:

exec sp_addpublication 
    @allow_initialize_from_backup = N'true', 

Then, I took a backup of my database:

    TO URL = 'https://fakestorageaccount.blob.core.windows.net/mycontainer/MyDatabase.bak' 
    WITH CREDENTIAL = 'mycredential', COMPRESSION, STATS = 5;

And restored from it on the target server:

  FROM URL = 'https://fakestorageaccount.blob.core.windows.net/mycontainer/MyDatabase.bak'
  WITH CREDENTIAL = 'mycredential'
    ,MOVE 'MyDatabase' to 'SomePath\Data\MyDatabase.mdf'
    ,MOVE 'MyDatabase_log' to 'SomePath\Log\MyDatabase.ldf'
    ,STATS = 5 

Now I'm ready to create the subscriber. The problem I'm having is that I don't know what options to provide to the sp_addsubscription procedure.

I can make it through the process using a regular hard drive instead of a URL. I could see data being replicated from source to target. I back up to disk, copy the backup to the target server, restore, and then initialize from backup using this command:

exec sp_addsubscription 
    @publication = N'MyPub', 
    @subscriber = N'MyTargetServer', 
    @destination_db = N'MyDatabase', 
    @subscription_type = N'Push', 
    @sync_type = N'initialize with backup', 
    @article = N'all', 
    @update_mode = N'read only', 
    @subscriber_type = 0,
    @backupdevicetype = 'disk',
    @backupdevicename = 'D:\Backups\MyDatabase.bak'

How do I tweak these options in order to initialize replication from a URL (which in my case is an Azure blob storage container that both servers have the credentials for)?


This is a very good question but at the moment, initializing a subscription from a backup stored in Windows Azure Blob storage is not currently supported. I believe this would be a very cool feature for replication and I'm sure the folks at Microsoft would agree. I suggest proposing this idea on UserVoice.

However, since you've already restored the backup, and the schema and data are already in place at the subscriber, try executing sp_addsubscription with the @sync_type of 'replication support only' and see if that meets your needs.

More details for initializing a subscription manually can be found in How to: Initialize a Subscription Manually.

  • I wonder why anyone would initialize with a backup when there is an option to initialize without a backup. Anyway, I took your advice and posted on UserVoice (still pending so you may not be able to see it yet, but here's the link anyway).
    – Rainbolt
    Mar 6 '18 at 23:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.