Blog Post

transactional replication:initialize with backup


I spend this weekend playing with transaction replication and try to set it up so that I don't have to synchronize it from snapshot.

The following article was helpful up to a certain point  I spend a lot of time trying to figure out how I could follow the example and make it works.  I got stuck at point #5.  I have never set up replication via stored procedure and wasn't sure what I need to do, and our friend BOL list a hug list or parameters for sp_addsubscription.

It took a couple of tries and a lot of frustration because I was trying to do a pull from the subscriber where subscriber and publisher is on the same server.  Maybe I just want to give myself a hard time by making things appear too complicate. 🙂

sp_addsubscription  @publication =  'publicationname'
     ,  @article =  'all'
     ,  @subscriber =  'servername'
     ,  @destination_db =  'dbname'
     ,  @sync_type =  'initialize with backup'
     ,  @status =  'Active'
     ,  @subscription_type =  'push'
     ,  @update_mode =  'read only'
     ,  @backupdevicetype =  'disk'
     ,  @backupdevicename =   N'\\servername\SQLBackups\test.bak'

I hope this script would help save you time if you are trying to initialize from a backup for the time time.  It took me a long time and sure would remember it in my sleep now that it has burned in time with this little exercise.

One additional note:  It should take less than 72 hr to complete from the initial set up of your new publication, backup, restore, and adding subscription or you should be prepare to have additional headache.

If you have any question, please let me know, and I will try my best to answer your question.