SQL Server Performance Forum – Threads Archive

How to copy the initial replication snapshot?

All-
I need to replicate a 36GB database between two datacenters, and I’m curious if there is a way to transport the initial snapshot of the db some other way than letting SQL Server sync it. The problem is that the VPN connection between the two datacenters is rather slow, so the initial 36GB snapshot is going to take at least 40hrs to copy. If I can get move the snapshot db separately outside of SQL Server (via FTP, let’s say), I can cut that time in half. The VPN connection is fast enough to support the replication once the snapshot gets there, but the initial snapshot is going to take forever. Any ideas? Thanks,
Z

Why not take copy of database on a HDD and restore on the secondary site, then let replication take the action. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Satya-
Actually that’s what I’ve already done (copy the db file via an alternate route), but the DBA I’m working with on this project said that wouldn’t work, that the replication engine needed to be one to make the initial snapshot of the database. So you are saying that if I setup replication, the publisher and subscriber will be able to work out what data needs to be synced? Thanks again!
Zack

Hi Zack, When you create the subscription you can specify that you don’t want the synchronisation to take place. You have to be careful though to do the steps in the right order, what I’ve done before to help me is create the publication
create a subscriber on the same server and allow it to fully synchronise once the subscriber is up to date, then stop all replication jobs create a subscriber on the remote server telling it not synchronise
restore a backup of the subscriber from the source server to the destination server
start all of the jobs That has worked for me in the past Cheers
Twan

]]>