SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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 http://msdn.microsoft.com/en-us/library/ms147834.aspx.  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.


Posted by scforeman on 19 July 2010

This is very helpful but I have a question. You initially said you were trying to setup a pull transactional subscription, yet this script builds a push subscription. Did you ever get a pull to work?

Posted by Sopheap Suy on 16 September 2010


I was trying to get replication to work either way (push or pull) as long as I don't have to initialize from a snapshot. I would try it over the weekend and let you know if the pull also work.


Posted by Sopheap Suy on 19 September 2010


Pull subscription with 'initialize with backup' doesn't work.  There are a 2 additional steps that you have to set up at subscriber   sp_addpullsubscription and sp_addpullsubscription_agent, and I still can see warning that subscription is uninitialized.

I think it probably will never works.  I also saw a few reference out there on the web as well mentioning about this issue too.


Leave a Comment

Please register or log in to leave a comment.