Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving SQL Server DB's


Moving SQL Server DB's

Author
Message
DNA_DBA
DNA_DBA
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2965 Visits: 2735
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.
Like2SQL
Like2SQL
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
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.
Thanks!
sql-lover
sql-lover
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 1930
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.

thanks!


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.
WorkasDBA
WorkasDBA
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 122
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search