This is a script which can assist you in moving or copying a number of databases to a new server. We recently had to move move a lot of databases to new hardware and this came in very handy. The script uses the Detach and Attach method. You can move one database or you can pipeline a number of databases through the script. Database owners are maintained so you will need to migrate those logins prior to using the script. Also, the script does not reattach the databases on the source server. At some point I may update the script to accommodate reattaching the databases. For my current purpose, I did not need to do so. However, a switch is provided which informs the script to reattach the source database after it has been copied.

There is an assumption the script is run by a member of the ‘Domain Admins’ AD group. It shouldn’t be too difficult to change the script to use “shares” other than admin shares (i.e. Drives with a $ sign).

The code contains several references to functions which I have in an imported module. Over time I’ve developed a long list of my own helper functions and found it easier to have them in a module. The functions I reference are listed individually after the main source code.

Here is the mainline source for the script.

<#
.SYNOPSIS
	Copy SQL databases to another server.
.DESCRIPTION
	Uses the Detach and Attach method to copy or move a database or databases
	from one SQL Server to another. 
.PARAMETER Database
	Name of the database to be copied
.PARAMETER SourceServer
	Server and instance name of the source SQL Server
.PARAMETER DestinationServer 
	Server and Instance name of the destination SQL Server
.EXAMPLE
	$dblist | Migrate-Databases.ps1 -SourceServer 'SQLSERVER1' -DestinationServer 'SQLSERVER2\SQL2'
	Pipelines an array list of database names into the migration script with
	SQLSERVER1 as the source and SQLSERVER2\SQL2 as the destination.
.EXAMPLE 
	Migrate-Databases.ps1 -database 'MyDB' -SourceServer 'SQLSRV1' -DestinationServer 'SQLSRV2'
	Copies MyDB from SQLSRV1 to SQLSRV2
.NOTES
	Script uses administrative UNC shares names for all files to be copied and must be run by someone
	in the 'Domain Admins' group. Many of the functions called are part of an Imported 
	module containing many helper funtions. The 
	Author: 	John P. Wood
	Company:	ATOS, Inc\Lee County, FL BOCC
	Date:		June, 2013
	This code can be copied and used as long as proper attribution is given and
	these notes are kept intact.
#>
[CmdletBinding()]
Param(
	[Parameter(Mandatory=$true,Position=0,ValueFromPipeline=$true)]
	[String]$Database,
	[Parameter(Mandatory=$true,Position=1)]
	[string]$SourceServer,
	[Parameter(Mandatory=$true,Position=2)]
	[string]$DestinationServer,
	[Parameter(Mandatory=$false,Position=3)]
	[switch]$ReattachSource
)

Begin
{
	Set-StrictMode -Version Latest
	Try
	{
		$smosrc = New-SMOconnection -server $SourceServer
		$smodest = New-SMOconnection -server $DestinationServer
		$filedest = Get-UncPath -server $DestinationServer -path $smodest.DefaultFile
		$logdest = Get-UncPath -server $DestinationServer -path $smodest.DefaultLog
	}
	Catch
	{
		Throw
	}
}
Process
{
	Try
	{
		$db = $smosrc.Databases[$database]

		# Break the mirror
		If($db.IsMirroringEnabled -eq $true) {
			Write-Verbose "Breaking mirror for $($db.Name)"
			$db.ChangeMirroringState([Microsoft.SqlServer.Management.Smo.MirroringOption]::Off)
			$db.Alter()
			$db.Refresh()
		}

		# Get Data files
		$files = @(Get-DatabaseDataFiles -Database $db.Name -Server $SourceServer |
			ForEach-Object{$_.FileName})
		$files | Write-Verbose 

		# Get log files
		$logfiles = @($db.LogFiles | ForEach-Object{$_.Filename})
		$logfiles | Write-Verbose

		# get Owner
		$owner = $db.Owner
		$owner | Write-Verbose

		# Detach database
		$smosrc.KillAllProcesses($db.Name)
		$smosrc.DetachDatabase($db.Name,$false)

		# Copy files to destination
		$destfiles = @()
		$destlogs = @()

		$files | Get-UncPath -Server $SourceServer | Copy-Item -Destination $filedest
		$files | ForEach-Object {
			$destfiles += [System.IO.Path]::Combine($filedest,
				[System.IO.Path]::GetFileName($_))}

		$logfiles | Get-UncPath -Server $SourceServer | Copy-Item -Destination $logdest 
		$logfiles | ForEach-Object {
			$destlogs += [System.IO.Path]::Combine($logdest,
				[System.IO.Path]::GetFileName($_))
		}

		# Attach database
		$sc =  New-Object System.Collections.Specialized.StringCollection

		$destfiles | ForEach-Object{
			$sc.Add($(Get-LocalPath -uncpath $_ ))
		}
		$destlogs | ForEach-Object{
			$sc.Add($(Get-LocalPath -uncpath $_ ))
		}
		$smodest.AttachDatabase($db.Name, $sc, $owner, 
			[Microsoft.SqlServer.Management.Smo.AttachOptions]::None)

		# Reattach the source databases if switch is on
		If($ReattachSource.IsPresent)
		{
			$sc.Clear()
			$files | ForEach-Object{$sc.Add($_ )}
			$logfiles | ForEach-Object{$sc.Add($_ )}
			$smosrc.AttachDatabase($db.Name, $sc, $owner, 
				[Microsoft.SqlServer.Management.Smo.AttachOptions]::None)
		}
		Return $smodest.Databases[$database]
	}
	Catch 
	{
		$ex = $_.Exception
		Write-Error $ex.Message
		While($ex.InnerException) 
		{
			$ex = $ex.InnerException
			Write-Error $ex.Message
		}
	}	
}
End
{
	$smosrc.ConnectionContext.Disconnect()
	$smodest.ConnectionContext.Disconnect()

}

A small function to get a UNC path

Function Get-UncPath {
<#
.Synopsis
	Returns a windows UNC path when given a server and path on the server.
.Description
	Returns a windows UNC path when given a server and path on the server.
.Parameter ComputerName
	The name of a computer or full dns name. This will also take an SQL Instance
	and remove the Instance name leaving the server name.
.Parameter Path
	The local path name of a a path on the computer
.Example Get-UncPath -ComputerName "LCFSQL07" -Path "D:\Logs"
	Returns a UNC path of "\\LCFSQL07\D$\Logs"
.Example Get-UncPath -ComputerName "LCFSQL07\SQL01" -Path "D:\Logs"
	Returns a UNC path of "\\LCFSQL07\D$\Logs"
#>
	[CmdletBinding()]
	Param(
		[Parameter(Position=0,Mandatory=$true)]
		[Alias("ComputerName")]
		[string]$Server,
		[string]$path
	)
	Process{
	Join-Path "\\$($server.split('\')[0])" $path.replace(':','$')
	}
}

A function to get physical files associated with a database

Function Get-DatabaseDataFiles
{	
	[CmdletBinding()]
	Param(
		[string]$Server,
		[string]$Database
	)
	Try
	{
		$smo = New-SMOconnection -server $Server
	}
	Catch
	{
		Throw $_.Exception
	}
	Try
	{
		$files = @()
		$db = $smo.Databases[$Database]
		If(!($db)) { Throw "$Database was not found" }
		Foreach($group in $db.FileGroups)
		{
			Foreach($datafile in $group.Files)
			{
				$hashtable = @{Group = $group.Name;
					Name = $datafile.Name;
					FileName = $datafile.Filename
				}
				$file = New-Object -TypeName PsObject -Property $hashtable
				$files += $file
			}
		}
	}
	Finally
	{
		$smo.ConnectionContext.Disconnect()
	}
		Return $files
}

Convert a UNC path back to a local path

Function Get-LocalPath {
	[CmdletBinding()]
	Param(
		[Parameter(Mandatory=$true)]
		[string]$uncpath
	)
	Process{
	$dirs = ($uncpath.Remove(0,2)).Split('\')
	$dir = ($dirs[1]).Replace('$',':')
	For([int]$ix=2; $ix -lt $dirs.Count; $ix++) {
		$dir += "\$($dirs[$ix])"
	}
	$dir
	}
}