Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

Moving SQL Server DB's Expand / Collapse
Posted Sunday, August 3, 2014 1:19 AM



Group: General Forum Members
Last Login: Yesterday @ 6:54 AM
Points: 2,952, Visits: 2,659
Do a test transfer of a large file in advance to gauge how long the 100gb databases will take to transfer across the network.

If the speed is slow or you can't have much of an outage, you could transfer a full backup across to the new server a few days in advance and then transfer daily differentials and finally transaction logs backups to break up the process into smaller files.
Post #1599001
Posted Wednesday, August 6, 2014 11:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 18, 2016 2:50 PM
Points: 60, Visits: 223
Thanks for the responses guys. So far we have moved 8 of the smaller databases via simple backup and restore with no issues. However needed further guidance on the 3 VLDB's which are publishers in a replicated setup. Any advice on duplicating the replication will be appreciated. Best way to do this, gotchas etc. All 3 are publishers, 1 of them is using snapshot replication and the other 2 are using merge.
Post #1600326
Posted Friday, August 8, 2014 5:06 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 1:40 PM
Points: 641, Visits: 1,907
Like2SQL (7/31/2014)
We have a SQL Server located in India; We need to move all the DB's on that server to a server residing locally for various legal reasons. The Server in India is a 2008 running 24 databases. 3 of these DB's are over 100GB and all 3 of them are being replicated from a third server in India. I am looking for high-level steps I will need to perform this so as to make sure I don't miss anything. Please advise, especially on problems I can expect to run into.


I did something similar when I was working for HP few years ago. I moved the backup file(s) over the network using robocopy from a server in China to one in USA. If I recall well, robocopy has a retry flag or command so it also helps during the process.

The whole process took almost a week though and I split the backup on several files. Using a single backup file won't work over a WAN.

Also, the speed and reliability of the WAN link is key. Otherwise, it may be better using old fashion methods like sending a backup tape via FedEx or similar services.
Post #1601354
Posted Tuesday, August 12, 2014 1:53 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, March 30, 2016 4:57 AM
Points: 67, Visits: 121
Assuming its not a complicated database with any kind of encryption
1. Back up the database. Use native or third party compression tool
2. Transmit and apply the back up , any related login scripts, linked server, jobs et al.
3. Test the restored back up functionality et al
4. Daily or hourly restore of differential back up . (Log shipping can be set up also if you can make changes to ur server).
5. On the day of cut over,
a. Stop the Prod server.
b. Make sure there is no untransmitted records on the publisher to distributor/ distributor to subscriber
c. Stop all the replication jobs - Publisher/Distributor/Subscriber
e. Transmit and apply the final compressed backup on the ToBe server. Apply any changed logins/ et al.
f. Restore MSDB for all jobs
g. DNS changes.
h. Start all the replication jobs

The pain will be if replication errors out, the snaphot will take longer for the 100GB.

Post #1602454
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse