chandan_jha18 (5/23/2013)
Bhaskar.Shetty (5/23/2013)
Another option other then suggested above would be, Initilizing the subscriber from backup, this saves a lot of time of applying snapshot to subscriber,The intial snapshot apply would also be problem where your database is huge with millions of rows and the connectivity between publisher and subscriber not that good.
I understood your suggestion but in case the connectivity is not so great, taking a fresh backup and copying it and restoring it would take almost an equal amount of time if not less. What's your take on that?
Thanks
Chandan
Taking a fresh backup from publisher and restoring it on subscriber should not be slow irrespective of network connectivity, as you are restoring the whole database from the backup set, and later add the subscriber using below code.
-- To Add Subscription from Backup Set --
EXEC sp_addsubscription
@publication ='myPublication', --> Name of the publication
@subscriber = 'myserver.myDomain.com', --> Fully qualified name or IP of subscriber server
@destination_db ='MySubscriberDB', --> Name of the database you just restored (doesn't have to be the same as the publisher)
@sync_type = 'initialize with backup', --> no need to change this
@backupdevicetype = 'disk', --> no need to change this
@backupdevicename = 'backupfile.bak' --> Pointer to the last backupfile that was restored, but from the folder on the on the
--> publishing server. If you restored trans logs also the last translog file is what you
--
This will only work if Initilize subscriber from backup set is set to true in publication property.