Management recently decided to use database mirroring as our DR solution. Because mirroring is done at the database level and not at the server level, I had a lot, a very lot of databases to be mirrored. To make it easier I decided to cobble together a simplistic script to do this.
I typically use a ‘management’ server for most of my needs and have therefore written the script to use UNC type pathing for the primary as well as mirror server. You will also notice I defaulted several of the parameters. This is handy when working on a set of servers for multiple databases.
I hope you find the script useful. You can also find a copy of the script at www.scriptinganswers.com.
<#
.SYNOPSIS
Set up an SQL Server mirrored database
.DESCRIPTION
Backs up a database and tlog, copies it to the destination,
Restores the database on the mirror server, sets up the partner,
and starts the mirror.
.PARAMETER database
The name of the database to be mirrored
.PARAMETER SourceServer
The name of the primary server
.PARAMETER SourcePath
Local Path for the backup
.PARAMETER DestServer
The name of mirror server
.PARAMETER DestPath
Local path for restore file
.EXAMPLE
PS C:\> Invoke-Mirror -database 'string value' `
-SourceServer 'string\string' -SourcePath 'string' `
-DestServer 'string\string' -DestPath 'string'
.NOTES
The SQL connections rely on Windows authentication and assumes Endpoints
already exist. Error checking is minimal (i.e. no check is made to
verify the recovery model is FULL).
.AUTHOR
John P. Wood
07/28/2010
#>
Param(
[Parameter(Mandatory=$true)]
[string]$database,
[string]$SourceServer='Server1\Instance',
[string]$SourcePath='K:\SQL Backups',
[string]$DestServer='Server2\Instance',
[string]$DestPath='K:\SQL Backups'
)
[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) for database backup of $($dbname)."
}
$smo = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
$smo
}
Function Invoke-SqlBackup {
$dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
$dbbk.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
$dbbk.BackupSetDescription = "Full backup of " + $database
$dbbk.BackupSetName = $database + " Backup"
$dbbk.Database = $database
$dbbk.MediaDescription = "Disk"
$device = "$SourcePath\$bkpfile"
$dbbk.Devices.AddDevice($device, 'File')
$smo = New-SMOconnection -server $SourceServer
Try {
$dbbk.SqlBackup($smo)
$dbbk.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Log
$dbbk.SqlBackup($smo)
$smo.ConnectionContext.Disconnect()
}
Catch {
$ex = $_.Exception
Write-Output $ex.message
$ex = $ex.InnerException
while ($ex.InnerException)
{
Write-Output $ex.InnerException.message
$ex = $ex.InnerException
};
continue
}
Finally {
if ($smo.ConnectionContext.IsOpen -eq $true) {
$smo.ConnectionContext.Disconnect()
}
}
}
Function Invoke-SqlRestore {
Param([string]$filename)
$restore = new-object Microsoft.SqlServer.Management.Smo.Restore
$restore.Action = 'Database'
$restore.Database = $database
$device = "$DestPath\$filename"
$restore.Devices.AddDevice($device, 'File')
$restore.ReplaceDatabase = $true
$restore.NoRecovery = $true
$smo = New-SMOconnection -server $DestServer
Try {
$restore.SqlRestore($smo)
$restore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Log
$restore.FileNumber = 2
$restore.SqlRestore($smo)
$smo.ConnectionContext.Disconnect()
}
Catch {
$ex = $_.Exception
Write-Output $ex.message
$ex = $ex.InnerException
while ($ex.InnerException)
{
Write-Output $ex.InnerException.message
$ex = $ex.InnerException
};
continue
}
Finally {
if ($smo.ConnectionContext.IsOpen -eq $true) {
$smo.ConnectionContext.Disconnect()
}
}
}
Function Set-Mirror {
Param([string]$server,[string]$database,[string]$partner)
$conn = "Server=$server; Integrated Security=SSPI; Database=Master"
$cn = New-Object "System.Data.SqlClient.SqlConnection" $conn
$cn.Open()
$cmd = New-Object "System.Data.SqlClient.SqlCommand"
$cmd.CommandType = [System.Data.CommandType]::Text
$cmd.CommandText = "ALTER DATABASE $database SET PARTNER = 'TCP://" + $partner + ":5022'"
$cmd.Connection = $cn
$cmd.ExecuteNonQuery()
$cn.Close()
Trap {
$ex = $_.Exception
Write-Output $ex.message
$ex = $ex.InnerException
while ($ex.InnerException)
{
Write-Output $ex.InnerException.message
$ex = $ex.InnerException
};
continue
}
}
$srcUNC = Join-Path "\\$($SourceServer.Split('\\')[0])" $($SourcePath.Replace(':','$'))
if (-not(Test-Path $srcUNC)) { New-Item $srcUNC -ItemType directory | Out-Null}
$destUNC = Join-Path "\\$($DestServer.Split('\\')[0])" $($DestPath.Replace(':','$'))
if (-not(Test-Path $destUNC)) { New-Item $destUNC -ItemType directory | Out-Null}
$bkpfile = $($SourceServer.Replace("\", "$")) + "_" + $database + "_FULL_" + $(get-date -format yyyyMMdd-HHmmss) + ".bak"
Invoke-SqlBackup
Copy-Item $(Join-Path $srcUNC $bkpfile) -Destination $destUNC -Verbose
Invoke-SqlRestore -filename $bkpfile
# Establish Mirroring from the mirrored database
Set-Mirror -server $DestServer -database $database -partner $($SourceServer.Split('\\')[0])
# Start the mirror
Set-Mirror -server $SourceServer -database $database -partner $($DestServer.Split('\\')[0])