November 14, 2013 at 8:36 am
So yesterday in my test environment I did the following to set up transactional replication without using the snapshot agent.
1. Create a publication
2. Flip Publication to “Allow initialization from backup files” is set to True
3. Take a backup of the publishing database
4. Restore the database on each subscriber
5. Run the exec sp_addsubscription making sure that I added @sync_type = 'initialize with backup'
This ran without a hitch. My question is this. Did it even touch the backup file that I designated in the @backupdevicename?
I have seen blog posts that say that @backupdevicename should be a drive on the publisher, but I think I messed up and gave it the subscriber drive instead. That said, there was a bit of latency and the subscribers went critical for about 2 minutes then the replication was fine. I tested and DML was flowing.
This was done in my test environment but I wanted to make sure that when I go live with this on the weekend all my ducks are in a row.
Books online says this:
[ @backupdevicename= ] 'backupdevicename'
Specifies the name of the device used when initializing a Subscriber from a backup. backupdevicename is nvarchar(1000), with a default of NULL.
So the question is - should that be on the publisher or subscriber. Microsoft documentation was vague and the blog posts that I have read have differing opinions.
My testing shows that as long as the databases are in sync that it didn't really matter.
November 14, 2013 at 2:04 pm
Tried the whole process again using the right directory. Worked like a charm.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply