Transactional Replication: How to Initialize a Subscription from a Backup

Today, I had a chance to initialize my subscription in transactional replication from backup. The database is quite big (120GB) and I thought it I would be better to use this method to start the replication process.

Unfortunately, this is not possible from the replication wizard and you have to change the publication and subscription definition manually.

1. Create a publication script and update manually the sp_addpublication command by changing the @allow_initialize_from_backup parameter to “true.” In my case it was set to “automatic” as I used the wizard to generate the script. I think that it is useful to set the other parameter: @immediate_sync to “true,” too.

2. Run the script on the publisher server and then create a backup of the database.

3. Restore the database on the subscriber.

4. Go back to the publisher and create a subscription script, but don’t run it. Look into it and add these parameters to the sp_addsubscription procedure:

@sync_type = “initialize with backup”

@backupdevicetype = “disk”  (you could add “tape” or “logical” apart from “disk”)

@backupdevicename = “path to the backup”

When you use the logical backup device, then put the name of this device.

I know that most of you know that a replication can be started using that technique. I wrote this blog post so you would not forget about that “feature” in the future.