How do I initialize transactional replication from a backup at a URL?

Question

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:

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

And restored from it on the target server:

RESTORE DATABASE [MyDatabase] 
  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 
GO

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)?

Answer 1

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.