6

I am looking to "move" databases to different servers with minimal disruption to data and service. These databases vary in size from 5GB to 140 GB.

I've seen and even used some of the various data transfer tools of SQL Server but I'm unsure of what the best practice is out of (Detach/Reattach, Restore from Backup, Ship Transaction Logs, Mirror...).

My biggest fear is that these databases have lots of stored procs, user permissions and various indexes and I don't want to lose them and end up disrupting service.

My latest hare-brained idea is to set up a mirror, and then initiate a manual failover. However, I'd rather ask before proceeding with something I've never done before.

TL;DR What are some of the best practices way of moving a SQL Server database that minimizes the threat of service disruption.

Improve this question
3
  • Are the source and target servers within the same AD domain or forest? If not, is there a trust between the forests? Commented Jun 16, 2014 at 15:35
  • @mfinni Yes, the servers are on the same AD domain. They will be separated months from now, but not for a while. Commented Jun 16, 2014 at 17:03
  • 1
    Good god, why does this have close votes? It's a fine question. Commented Jun 18, 2014 at 6:28

1 Answer 1

Reset to default
3

In my experience, detach/attach is the fastest method. The bottleneck would probably be how quickly you could copy the files across the network.

Assuming the two databases have identical Windows accounts (if you're using SQL accounts you may have to update SIDs), you could probably use something like this script that I have laying around from before I started rewriting everything in PowerShell. :) It's intended to be run on the source server, and uses a file with a list of databases to move.

@ECHO ON set newipmdf=\\newserver\g$ set newipldf=\\newserver\e$ set controlfile=control.txt set oldserver=oldserver\instance set oldmdfpath=d:\instance set newmdfpath=g:\instance set copymdfpath="m:\instance" set newserver=newserver\instance set oldlogpath=e:\instance set newlogpath=e:\instance set copylogpath="l:\instance" set movedmdfpath=%oldmdfpath%\moved set movedldfpath=%oldlogpath%\moved mkdir %movedmdfpath% mkdir %movedldfpath% net use m: %newipmdf% net use l: %newipldf% SETLOCAL DISABLEDELAYEDEXPANSION FOR /F %%L IN (%controlfile%%) DO ( SET "line=%%L" SETLOCAL ENABLEDELAYEDEXPANSION ECHO !line! sqlcmd -E -S!oldserver! -Q"EXEC master.dbo.sp_detach_db @dbname = N'!line!'" copy "!oldmdfpath!\!line!.mdf" !copymdfpath! copy "!oldlogpath!\!line!_log.ldf" !copylogpath! sqlcmd -E -S!newserver! -Q"CREATE DATABASE [!line!] ON ( FILENAME = '!newmdfpath!\!line!.mdf' ),( FILENAME = N'!newlogpath!\!line!_log.ldf' ) FOR ATTACH" move "!oldmdfpath!\!line!.mdf" !movedmdfpath! move "!oldlogpath!\!line!_log.ldf" !movedldfpath! ENDLOCAL ) ENDLOCAL net use m: /z net use l: /z

If you can't be down long enough to copy your 140GB file across the network, I've had good luck with the copy database wizard. I would still use detach/attach if possible, though.

Good luck!

Improve this answer
5
  • 1
    Wow, I love that script. I don't detach/move/attach very often, but when I do I'm going to do it with this script. Commented Jun 17, 2014 at 22:16
  • If the goal is minimal disruption, surely a detach/reattach isn't going to fulfil that? What is the disadvantage of (assuming the DB is in FULL recovery mode) restoring the latest full backup on the new server and then bringing the database up to date with the t-log backups, before finally cutting off access and taking/restoring a final t-log backup? Genuinely curious over here! Commented Jun 17, 2014 at 22:30
  • Sure, he could do log shipping and then bring it online, for example. It depends on the number of databases he's being asked to move. I was being asked to move several hundred. :D I was also assuming, perhaps incorrectly, that the OP had more than a couple. I could be wrong, of course. Commented Jun 17, 2014 at 22:36
  • 1
    Ah, I was assuming that he only had a handful of DBs. Having more than that would certainly move the goalposts somewhat - I must admit I didn't even consider that situation! Commented Jun 17, 2014 at 22:38
  • 1
    @KatherineVillyard Thanks Katherine. Long story short, we are going to hire someone to help us. But I will try this with my test database since I want to know how to do it myself. And we do have more than a couple dbs, we have 93 in all that have to be moved. Commented Jun 18, 2014 at 3:38

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.