Tag Archives: smo

Move MSSQL Databases using Powershell with Detach\Attach

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(':','$')
	}
}

Function to get an SMO connection

Function New-SMOconnection
{
	[CmdletBinding()]
    Param (
		[Parameter(Mandatory=$true)]
		[string]$Server,
		[Parameter(Mandatory=$false)]
		[string]$ApplicationName = "Powershell SMO",
		[Parameter(Mandatory=$false)]
		[int]$StatementTimeout=0
	)
	$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($Server)
	$conn.ConnectTimeout = 5
	$conn.applicationName = $ApplicationName
	$conn.StatementTimeout = $StatementTimeout
	Try 
	{
		$conn.Connect()
		Return New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
	}
	Catch 
	{
		Throw 
	}
}

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
	}
}

Remove Older SQL Server Job Logs with Multithreaded Powershell

A short time ago I attended a WEB presentation called Speeding Up PowerShell: Multithreading and presented by Tobias Weltner. I have used Start-Job, etc. for running some tasks asynchronously but the presentation convinced me to try multiple threads instead. Watch the presentation and you will see there are many advantages to multiple threads over multiple Jobs.

I recently implemented a script to search all my SQL Servers for SQL Job logs older than 28 days and to remove them. I prefer using a centralized Powershell script to do this because it is easy to add/remove servers to a list rather than create a new task for a new server. The code presented here will operate on all my servers initiating a new Powershell thread for each server. This significantly cuts down on total processing time.

# (C) 2012 Dr. Tobias Weltner
# you may freely use this code for commercial or non-commercial purposes at your own risk
# as long as you credit its original author and keep this comment block.
# For PowerShell training or PowerShell support, feel free to contact tobias.weltner@email.de

# Set a limit for our pool so we don't overload the machine
$throttleLimit = 4
$SessionState = [system.management.automation.runspaces.initialsessionstate]::CreateDefault()
$Pool = [runspacefactory]::CreateRunspacePool(1, $throttleLimit, $SessionState, $Host)
$Pool.Open()

<#	Our script block will remove old SQL Job logs from a server.
	Keep in mind, each thread is run in it's own environment which means it does
	not automitacally enherit your sessions assemblies, variables, etc. Also, it (the thread) does 
	not include a profile. Therefore, you must make available any assemblies or 
	modules for EACH thread. Hence, the inclusion of the SQL assemplies and an
	included module with a number of SQL functions.
#>
$ScriptBlock = {
Param(
	[string]$server
)
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum")
Import-Module SqlDbAdmin

#	Number of days of logs to keep
$days = 28
$LastWrite = (Get-Date).AddDays(-$days)

#	call functions, which are in an included module, to connect to
#	SQL with SMO and to parse the server and log path into a valid UNC 
$smo = New-SMOconnection -server $server
$logpath = Get-UncPath -computername $server -path $smo.Information.ErrorLogPath
$smo.ConnectionContext.Disconnect()

#	Remove only the selected files
get-childitem $logpath -filter '*.txt'  | 
	Where {$_.LastWriteTime -le $LastWrite} | ForEach-Object {
		Remove-Item $_.FullName
		Write-Output "Removed file $($_.FullName)"
	}
}
#	End of Script Block

# 	Get an array list of all our servers
#         You will need to change this to your own path
$servers = @(Get-Content C:\Scripts\PoSH\Servers.txt)

<# 	Start a new thread for each server. The throttle we set up for our pool
	will insure the threads don't overload our machine in case we have a lot 
	of servers #>
$threads = @()
$handles = for ($x = 1; $x -le $servers.Count; $x++) {
	$server = $servers[$x]
	$powershell = [powershell]::Create().AddScript($ScriptBlock).AddArgument($server)
	$powershell.RunspacePool = $Pool
	$powershell.BeginInvoke()
  	$threads += $powershell
}

#	Cycle through and close the threads as each completes
do { 
  $i = 0
  $done = $true
  foreach ($handle in $handles) {
    if ($handle -ne $null) {
  	  if ($handle.IsCompleted) {
        $threads[$i].EndInvoke($handle)
        $threads[$i].Dispose()
        $handles[$i] = $null
      } else {
        $done = $false
      }
    }
    $i++ 
  }
  if (-not $done) { Start-Sleep -Milliseconds 500 }
} until ($done)

There are two Powershell Functions which are called from an included module. You can either include them in the script block or in your own module. For more information on script blocks, see the excellent article ScriptBlocks On the Fly. More information on Powershell 2.0 modules, try the article An Introduction to PowerShell Modules.

The first function you will need is used to get an SMO connection to an SQL Server.

Function New-SMOconnection {
	[CmdletBinding()]
    Param (
		[Parameter(Mandatory=$true)]
		[string]$server,
		[int]$StatementTimeout=0
	)
	Process {
	If(!(Test-Connection -ComputerName $($server.Split('\')[0]) -Quiet -Count 1)) {
		Throw "Could not connect to Server Machine $($server.Split('\')[0])."
	}
	$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($server)
	$conn.applicationName = "PowerShell SMO"
	$conn.StatementTimeout = $StatementTimeout
	Try {$conn.Connect()}
	Catch {Throw $_.Exception}
	if ($conn.IsOpen -eq $false) {
		Throw "Could not connect to SQL Instance $server."
	}
	$smo = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
	$smo
	}
}

The second function merely creates a UNC path by joining the server machine name with the local path of ErrorLogPath we got from SMO

Function Get-UncPath {
	[CmdletBinding()]
	Param(
		[string]$computername,
		[string]$path
	)
	Process{
	Join-Path "\\$($computername.split('\')[0])" $path.replace(':','$')
	}
}

Powershell Function to Get Last SQL Server Backup File

Powershell is a great tool to use for a multitude of SQL Server administrative functions. I frequently need to know or get the file name of the last backup of a database. Sometimes I just need it for a report or to pass it off to another function. I created a Powershell function to obtain the data for me and had been using it for some time. Unfortunately, it recently broke down.

After some debugging, I was able to find the cause of my problem and I thought it would be a good idea to post my new and improved function. The function had to be changed because of snapshots being taken by VEEAM backups. We recently built a new SQL Server on VMWare and I implemented my normal backup methodology. However, when I recently wanted a report of the latest backups, my Powershell function failed! VEEAM snapshots are reported as backups but there is no file name associated with it. The new function excludes all backups which are snapshots.

I’ll first post the T-SQL to obtain the latest backup for a database and then I’ll show you how to use it in a Powershell function.

DECLARE @dbname sysname
SET		@dbname = 'YOURDB'
SELECT	f.physical_device_name as [backup]
FROM	msdb.dbo.backupset AS s WITH (nolock) INNER JOIN
			msdb.dbo.backupmediafamily AS f WITH (nolock) ON s.media_set_id = f.media_set_id
WHERE	(s.database_name = @dbname) AND (s.type = 'D') AND (f.device_type <> 7)
			AND (s.backup_finish_date = (SELECT     MAX(backup_finish_date)
FROM        msdb.dbo.backupset WITH (nolock)
WHERE	(database_name = @dbname) AND (type = 'D') AND (is_snapshot = 0)))

And now the Powershell use of it.

Param(
	[Parameter(Mandatory=$true,Position=0)]
	[string]$server,
	[Parameter(Mandatory=$true,Position=1)]
	[string]$database
)
Function New-SMOconnection {
    Param (
		[Parameter(Mandatory=$true)]
		[string]$server,
		[int]$StatementTimeout=0
	)
	If(!(Test-Connection -ComputerName ($server.Split('\')[0]) -Quiet -Count 1)) {
		Throw "Could not connect to SQL Server $server."
	}
	$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($server)
	$conn.applicationName = "PowerShell SMO"
	$conn.StatementTimeout = $StatementTimeout
	Try {$conn.Connect()}
	Catch {Throw $Error}
	if ($conn.IsOpen -eq $false) {
		Throw "Could not connect to SQL Instance $server."
	}
	$smo = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
	$smo
}
Function Get-LastBackupFile {
	Param(
		[string]$server,
		[string]$database
	)
	<# 	Use a hereto to construct the T-SQL
		You will notice the query eliminates any snapshots. This is because we
		sometimes have VEEAM backups on some servers.
	#>
	$qry = @"
DECLARE @dbname sysname 
SET @dbname = '$database'
SELECT	f.physical_device_name as [backup]
FROM	msdb.dbo.backupset AS s WITH (nolock) INNER JOIN
			msdb.dbo.backupmediafamily AS f WITH (nolock) ON s.media_set_id = f.media_set_id
WHERE	(s.database_name = @dbname) AND (s.type = 'D') AND (f.device_type <> 7) 
		AND (s.backup_finish_date = (SELECT MAX(backup_finish_date)
FROM         msdb.dbo.backupset WITH (nolock)
WHERE     (database_name = @dbname) AND (type = 'D') AND (is_snapshot = 0)))
"@

	# Get an SMO Connection
	$smo = New-SMOconnection -server $server
	# most appropriate to use MSDB
	$db = $smo.Databases["msdb"]
	# Execute query with results
	$rs = $db.ExecuteWithResults($qry)
	# SMO connection is no longer needed
	$smo.ConnectionContext.Disconnect()
	# Return the result
	$rs.Tables[0].Rows[0].Item('backup')
}
# Load SMO Assemblies
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")

# Call the function and trap any error
Try {$backup = Get-LastBackupFile -server $server -database $database}
Catch {
	$ex = $Error[0].Exception
	Write-Host $ex.Message
	While($ex.InnerException) {
		$ex = $ex.InnerException
		Write-Host $ex.Message
	}
}
<# Verify the file  
	NOTE: most developent and run-time is performed on remote servers
	so there may be a need to convert to UNC format
#>
$backup = Join-Path "\\$($server.split('\')[0])" $backup.replace(':','$')

if(!(Test-Path $backup)) {
	Throw "Database backup $backup not found"
}
Write-Host $backup

Powershell Restore to New SQL Server Database

I don’t often have to restore a database. However, while having to restore a few dozen to set up database mirroring, I came across a few with multiple database files. My first ‘mirroring’ script did not account for this. I quickly wrote a new restore script to accommodate the possibility of multiple files.

<#
     .SYNOPSIS
          Restore to a NEW Database
     .DESCRIPTION
          Restores an SQL Server backup file to a new database.
	   1) Uses existing Logical and physical file names and restores to the
	      SQL server default file locations.
	   2) Restores multiple files.
	   3) Can specify NoRecover (necessary for mirrored database)
     .PARAMETER  file
          Full path and file name for the backup file. Must be local
     .PARAMETER  Server
          The name\instance of the SQL Server.
     .PARAMETER  database
          The name of the database to be restored
     .EXAMPLE
          PS C:\> Invoke-SqlRestore -file 'D:\Backups\mydb.bak' `
		  	-server 'MyServr\SQLinstance' -database NEWDB
     .NOTES
         AUTHOR:    John P. Wood
         CREATED:   July, 2010
         VERSION:   1.0.3
     .LINK
         http://www.webofwood.com
#>
Param(
	[string]$file,
	[string]$server,
	[string]$database,
	[switch]$norecovery=$true
	)
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
Function New-SMOconnection {
    Param ([string]$server)
	$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($server)
	$conn.applicationName = "PowerShell SMO"
	$conn.StatementTimeout = 0
	$conn.Connect()
	if ($conn.IsOpen -eq $false) {
		Throw "Could not connect to server $($server)."
	}
	$smo = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
	$smo
}
Function Get-FileName {
	Param([string]$path)
	$names = $path.Split('\\')
	$names[$names.Count - 1]
}
Function Invoke-SqlRestore {
	Param(
		[string]$backupFile,
		[Microsoft.SqlServer.Management.Smo.Server]$smo
	)
	$backupDevice = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") `
		($backupFile, "File")
	# Get local paths to the Database and Log file locations
	If ($smo.Settings.DefaultFile.Length -eq 0) {$DBPath = $smo.Information.MasterDBPath }
	Else { $DBPath = $smo.Settings.DefaultFile}
	If ($smo.Settings.DefaultLog.Length -eq 0 ) {$DBLogPath = $smo.Information.MasterDBLogPath }
	Else { $DBLogPath = $smo.Settings.DefaultLog}

	# Load up the Restore object settings
	$Restore = new-object Microsoft.SqlServer.Management.Smo.Restore
	$Restore.Action = 'Database'
	$Restore.Database = $database
    $Restore.ReplaceDatabase = $true
	if ($norecovery.IsPresent) { $Restore.NoRecovery = $true }
	Else { $Restore.Norecovery = $false }
	$Restore.Devices.Add($backupDevice)

	# Get information from the backup file
	$RestoreDetails = $Restore.ReadBackupHeader($smo)
	$DataFiles = $Restore.ReadFileList($smo)

	# Restore all backup files
	ForEach ($DataRow in $DataFiles) {
        $LogicalName = $DataRow.LogicalName
        $PhysicalName = Get-FileName -path $DataRow.PhysicalName
		$RestoreData = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
		$RestoreData.LogicalFileName = $LogicalName
		if ($DataRow.Type -eq "D") {
			# Restore Data file
			$RestoreData.PhysicalFileName = $DBPath + "\" + $PhysicalName
		}
		Else {
			# Restore Log file
			$RestoreData.PhysicalFileName = $DBLogPath + "\" + $PhysicalName
		}
		[Void]$Restore.RelocateFiles.Add($RestoreData)

	}
	$Restore.SqlRestore($smo)
	# If there are two files, assume the next is a Log
	if ($RestoreDetails.Rows.Count -gt 1) {
		$Restore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Log
		$Restore.FileNumber = 2
		$Restore.SqlRestore($smo)
	}
}
Clear-Host
# Get a new connection to the server
$smo = New-SMOconnection -server $server
Write-Host "Starting restore to New Database $database on $server."
Try {
	Invoke-SqlRestore -backupFile $file -smo $smo
	}
Catch {
	$ex = $_.Exception
	Write-Output $ex.message
	$ex = $ex.InnerException
	while ($ex.InnerException) {
		Write-Output $ex.InnerException.message
		$ex = $ex.InnerException
	}
	Throw $ex
}
Finally {
	$smo.ConnectionContext.Disconnect()
}
Write-Host "Restore ended without any errors."
	 1) Uses existing Logical and physical file names and restores to the
	    SQL server default file locations.
	 2) Restores multiple files.
	 3) Can specify NoRecover (necessary for mirrored database)