Monthly Archives: May 2012

Failover SQL Server with Powershell

To failover an SQL mirrored database is fairly easy using SSMS or T-SQL. However, in a DR situation when things are happening quickly and time is of the essence, there is not always the luxury of having the resources available to accommodate failing over a large number of databases. If at all possible it is most desirable to fail over all over your databases while the principal server is still available.

In preparation for our next DR test I developed a Powershell script which would identify all mirrored databases and failover those databases. In the event the principal server is unavailable, the script can also be used for a forced failover on the mirrored server. Our environment consists of mirroring databases on one server to another like server at our DR site. Although you could mirror various databases from one instance to a multitude of instances, we adhere to a one-to-one instance environment. The script assumes this one-to-one placement but it could be easily changed to accommodate a one-to-many or even a many-to-many environment. That’s the beauty of Powershell scripting.

The script is developed on a Windows 7 platform with SQL 2008 R2 Tools. It was tested on Windows Server 2008 and 2008 R2 with SQL 2008 R2 EE. You are free to use the script as is or to modify it as needed. However, I do ask you maintain the attribution to the author


Param(
	[CmdletBinding()]
	[Parameter(Mandatory=$true,Position=0)]
	[string]$PrincipalServer,
	[Parameter(Mandatory=$true,Position=1)]
	[string]$MirrorServer,
	[Parameter(Mandatory=$false,Position=2)]
	[String[]]$Database,
	[Parameter(Mandatory=$false,Position=3)]
	[switch]$Force
)

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 {
		Write-Exception $_.Exception
	}
	if ($conn.IsOpen -eq $false) {
		Throw "Could not connect to $server SQL Instance."
	}
	$smo = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
	$smo
	}
}

Function Write-Exception {
	Param($exception)
	Write-Host $exception.Message
	While ($exception.InnerException) {
		$exception = $exception.InnerException
		Write-Host $exception.Message
	}
}

Function Force-FailOver {
Param(
	[CmdletBinding()]
	[Parameter(Mandatory=$true,Position=0,ValueFromPipeLine=$true)]
	$database
)
	Process {
		Try{
			$database.ChangeMirroringState([Microsoft.SqlServer.Management.Smo.MirroringOption]::ForceFailoverAndAllowDataLoss)
		}
		Catch {
			Write-Exception $_.Exception
			Return "`nUnsuccessful Failover for $($database.Name)"
		}
		Return "Database mirror $($database.Name) is failed over with possible data loss"
	}
}

Function Normal-FailOver {
<#
	Normal failover executes on the Principal database. Once the failover completes,
	we then need to adjust our Mirroring Safety Level back to OFF. We have to switch
	to the Principal database which is now on the mirror server and alter the Saftey
	level there.
#>
Param(
	[CmdletBinding()]
	[Parameter(Mandatory=$true,Position=0)]
	$smo,
	[Parameter(Mandatory=$true,Position=0,ValueFromPipeLine=$true)]
	$database
)
	Process {
		Try{
			$database.MirroringSafetyLevel = [Microsoft.SqlServer.Management.Smo.MirroringSafetyLevel]::Full
			$database.Alter()
			# We have to wait to do the actual failover. The database needs to
			# catch up after changing the safety level and altering the database
			Start-Sleep -Seconds 3
			$database.ChangeMirroringState([Microsoft.SqlServer.Management.Smo.MirroringOption]::Failover)

			# Switching to server where the Principal now resides
			$db = $smo.Databases[$($Database.Name)]
			$db.MirroringSafetyLevel = [Microsoft.SqlServer.Management.Smo.MirroringSafetyLevel]::Off
			$db.Alter()
		}
		Catch {
			Write-Exception $_.Exception
			Return "`nUnsuccessfull failover for $($database.Name)"
		}
		Return "Database mirror $($database.Name) is failed over"
	}
}
# Initialize process
Set-StrictMode -Version Latest
[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")

# Get a connection to the Mirror server
Try {
	$smoMirror = New-SMOconnection -server $MirrorServer
}
Catch {
	Write-Exception $_
	Throw "`nMirror server unavailable. Failover can not be run for $MirrorServer"
}

If($Force -eq $false) {
	# Get a connection to the Principal Server
	Try {
		$smoPrincipal = New-SMOconnection -server $PrincipalServer
	}
	Catch {
		Write-Exception $_.Exception
		Throw "Principal server $PrincipalServer is unavailable. Try running with the -Force parameter"
	}
	# Get a collection of mirrored databases from the principal server
	$databases = @($smoPrincipal.databases | Where-Object {$_.IsMirroringEnabled -and
		$_.Status -eq 'Normal' -and
		$_.MirroringStatus -eq [Microsoft.SqlServer.Management.Smo.MirroringStatus]::Synchronized})
}
Else {
	# Get a collection of mirrored databases from the mirrored server
	$databases = @($smoMirror.databases | Where-Object {$_.IsMirroringEnabled -AND ($_.Status -eq 'Restoring')})
}

# Use the database parameter list (if supplied) to limit the databases to the list
If($database) {
	$databases = @($databases | Where-Object {$database -contains $_.Name})
}

# Use a function to fail over the databases depending on the type of failover
If($Force -eq $true) {
	$databases | Force-FailOver
}
Else {
	$databases | Normal-FailOver -smo $smoMirror
}

If($smoPrincipal) {$smoPrincipal.ConnectionContext.Disconnect()}
$smoMirror.ConnectionContext.Disconnect()
Write-Host "`nScript completed for $($databases.Count) database(s)"

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