Copy SQL Server Databases

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.

Solution 1:

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!