Copy SQL Server Databases

Question

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.

Are the source and target servers within the same AD domain or forest? If not, is there a trust between the forests? — mfinni, Jun 16 '14 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. — Black Dynamite, Jun 16 '14 at 17:03
Good god, why does this have close votes? It's a fine question. — Falcon Momot, Jun 18 '14 at 06:28

score 3 · Accepted Answer · answered Jun 17 '14 at 22:12

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!

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. — Mark Henderson, Jun 17 '14 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! — Chris McKeown, Jun 17 '14 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. — Katherine Villyard, Jun 17 '14 at 22:36
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! — Chris McKeown, Jun 17 '14 at 22:38
@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. — Black Dynamite, Jun 18 '14 at 03:38

Copy SQL Server Databases

1 Answers1