SQL mirroring is a means of providing high availability for your SQL database. It is available in Standard Edition and although the feature is deprecated it is still widely utilised. You can read more about it on MSDN here and Jes Borland wrote a useful post answering many questions here
There are situations where you may need to refresh these databases. Disaster Recovery is an obvious one but also during development to provide testing or development environments to test your High Availability implementations, run through disaster scenarios, create run books or ensure that the code changes still work with mirroring. There are other scenarios but this post covers the automation of restoring a mirrored database from a backup.
I have mentioned before and no doubt I shall again, John Sansom wrote a great post about automation and I am a strong follower of that principle.
To refresh a SQL mirror the following steps are required, there are some gotchas that you need to be aware of which I will discuss later
remove mirroring
restore principle database from backup
perform a transaction log backup of the principle database
restore both backups on the mirror server with no recovery
recreate mirroring
resolve orphaned users
check mirroring status
Regular blog followers will know that I prefer to use Powershell when I can (and where it is relevant to do so) and so I have used Powershell to automate all of the steps above
The script requires some variables to be set up at the beginning. You can easily change this and make the script into a function and call it if you desire, but for this post I shall consider the script as a standalone. The reasoning for this is that I imagine that it will be placed into a run book or stored for use in a repository for specific use and therefore reduces any pre-requisites for using it.
Set variables as follows, the last three variables set the types for the backup action type and device type and do not need to be altered.
# Set up some variables
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
"$PrincipalServer = '' # Enter Principal Server Name $MirrorServer = '' # Enter Mirror Server Name $DBName = '' # Enter Database Name $FileShare = '' # Enter FileShare with trailing slash $LocationReplace = $FileShare + $DBName + 'Refresh.bak' $LocationTran = $FileShare + $DBName + 'formirroring.trn' $PrincipalEndPoint = 'TCP://SERVERNAME:5022' # Change as required $MirrorEndpoint = 'TCP://SERVERNAME:5022' # Change as required $WitnessEndpoint = 'TCP://SERVERNAME:5022' # Change as required $Full = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Database $Tran = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Log $File = [Microsoft.SqlServer.Management.Smo.DeviceType]::File&lt;/PRE&gt;</PRE> |
After some error checking the first thing is to create server and database SMO objects
|
1 2 3 4 5 6 7 |
"# Create Server objects $Principal = New-Object Microsoft.SQLServer.Management.SMO.Server $PrincipalServer $Mirror = New-Object Microsoft.SQLServer.Management.Smo.server $MirrorServer #Create Database Objects $DatabaseMirror = $Mirror.Databases[$DBName] $DatabasePrincipal = $Principal.Databases[$DBName]&lt;/PRE&gt;</PRE> |
(Added Extra – Use New-ISESnippet to create a SMO Server Snippet and use CTRL + J to find it
|
1 2 |
"New-IseSnippet -Title SMO-Server -Description "Create A SQL Server SMO Object" -Text "`$srv = New-Object Microsoft.SqlServer.Management.Smo.Server `$server" |
)
Remove Mirroring
Before we can restore the database we need to remove mirroring
|
1 |
"$DatabasePrincipal.ChangeMirroringState([Microsoft.SqlServer.Management.Smo.MirroringOption]::Off) |
restore principle database from backup
Once mirroring has been removed we can restore the database. Stuart Moore’s Great Series provides all the code you need to backup and restore databases with Powershell. There is however a bug which can catch you out. Here’s the code
|
1 2 3 4 5 6 7 8 |
"$restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore|Out-Null $restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationReplace,$File)|Out-Null $restore.Database = $DBName $restore.ReplaceDatabase = $True $restore.Devices.add($restoredevice) #Perform Restore $restore.sqlrestore($PrincipalServer) $restore.Devices.Remove($restoredevice) |
The bug is as follows, if your restore is going to take longer than 10 minutes and you are using an earlier version of SQL than SQL 2012 SP1 CU8 then you will find that the restore fails after 10 minutes. This is the default timeout. You may try to set the
|
1 |
"$srv.ConnectionContext.StatementTimeout |
Value to a larger value or 0 and this will work after SQL 2012 SP1 CU8 but prior to that you will still face the same error. The simple workaround is to use Invoke-SQLCmd2 and to script the restore as follows
|
1 2 3 4 5 6 7 8 9 10 11 |
"#Set up Restore using refresh backup $restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore $restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationReplace,$File)|Out-Null $restore.Database = $DBName $restore.ReplaceDatabase = $True $restore.Devices.add($restoredevice) #Perform Restore $restore.sqlrestore($PrincipalServer) # if query time < 600 seconds # $query = $restore.Script($PrincipalServer) # if using Invoke-SQLCMD2 $restore.Devices.Remove($restoredevice) |
perform a transaction backup of the principle database
We need to have a full and transaction log backup to set up mirroring. Again you may need to use the script method if your backup will take longer than 600 seconds.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
" #Setup Trans Backup $Backup = New-Object Microsoft.SqlServer.Management.Smo.Backup|Out-Null $Full = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Database $Tran = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Log $File = [Microsoft.SqlServer.Management.Smo.DeviceType]::File $Backup.Action = $Tran $Backup.BackupSetDescription = “Log Backup of “ + $DBName $Backup.Database = $DBName $BackupDevice = New-Object –TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationTran,$File)|Out-Null $Backup.Devices.Add($BackupDevice) # Perform Backup $Backup.SqlBackup($PrincipalServer) # $query = $Backup.Script($PrincipalServer) # if query time < 600 seconds $Backup.Devices.Remove($BackupDevice) # Invoke-Sqlcmd2 –ServerInstance $PrincipalServer –Database master –Query $query –ConnectionTimeout 0 # comment out if not used |
Restore both backups on the mirror server with no recovery
To complete the mirroring set up we need to restore the backups onto the mirror server with no recovery as follows
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
"#Set up Restore of Full Backup on Mirror Server $restore = New-Object -TypeName Microsoft.SqlServe r.Management.Smo.Restore|Out-Null $restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationReplace,$File)|Out-Null $restore.Database = $DBName $restore.ReplaceDatabase = $True $restore.NoRecovery = $true $restore.Devices.add($restoredevice) $restore.sqlrestore($MirrorServer) # if query time < 600 seconds # $query = $restore.Script($MirrorServer) # if using Invoke-SQLCMD2 $restore.Devices.Remove($restoredevice) # Invoke-Sqlcmd2 -ServerInstance $MirrorServer -Database master -Query $query -ConnectionTimeout 0 # comment out if not used # Set up Restore of Log Backup on Mirror Server $restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore|Out-Null $restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationTran,$File)|Out-Null $restore.Database = $DBName $restore.ReplaceDatabase = $True $restore.NoRecovery = $true $restore.Devices.add($restoredevice) $restore.sqlrestore($MirrorServer) $restore.Devices.Remove($restoredevice) |
Recreate mirroring
You recreate mirroring in the same way as you would if you were using T-SQL simply add the principal endpoint to the mirror, and the mirror and witness endpoints to the principal
|
1 2 3 4 5 6 |
"#Recreate Mirroring $DatabaseMirror.MirroringPartner = $PrincipalEndPoint $DatabaseMirror.Alter() $DatabasePrincipal.MirroringPartner = $MirrorEndpoint $DatabasePrincipal.MirroringWitness = $WitnessEndpoint $DatabasePrincipal.Alter() |
Resolve orphaned users
You will need to resolve any users and permissions on your destination servers. I do not know a way to do this with PowerShell and would be interested if anyone has found a way to replace the password or the SID on a user object, please contact me if you know.
Many people do this with the sp_rev_logins stored procedure which will create the T-SQL for recreating the logins. However, Powershell cannot read the outputs of the message window where the script prints the script. If you know that your logins are staying static then run sp_rev_logins and store the output in a sql file and call it with Invoke-SQLCmd2
|
1 2 |
"$SQL = ‘’ #Path to File Invoke-Sqlcmd2 –ServerInstance $Server –Database master –InputFile $SQL |
The other option is to set up a SSIS package following this blog post and call it from Powershell as follows
|
1 |
"Invoke-Command –ComputerName $Server –scriptblock {DTExec.exe /File “PATHTOPackage.dtsx”} |
This requires Powershell Remoting to have been set up on the server which may or may not be available to you in your environment.
IMPORTANT NOTE – The script does not include any methods for resolving orphaned users so you will need to test and then add your own solution to the script.
check mirroring status
Lastly you want to check that the script has run successfully and that mirroring is synchronised (I am from the UK!!) To do this I check that time and file used for the last database backup using this script
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
"#Check that correct file and backup date used $query = "SELECT TOP 1 [rs].[destination_database_name] as 'database', [rs].[restore_date] as 'restoredate', [bs].[backup_finish_date] as 'backuptime', [bmf].[physical_device_name] as 'Filename' FROM msdb..restorehistory rs INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id] INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id] ORDER BY [rs].[restore_date] DESC" Invoke-Sqlcmd2 -ServerInstance $PrincipalServer -Database msdb -Query $query |Format-Table -AutoSize –Wrap |
and that mirroring has synchronised using the following Powershell command
|
1 |
"$DatabasePrincipal | select Name, MirroringStatus, IsAccessible |Format-Table -AutoSize |
Depending on your needs you may add some error checking using the results of the above scripts. As I said at the top of the post, you can turn this script into a function and call it at will or add it to an Agent Job for regular scheduling or just kept in a folder ready to be run when required. The choice is yours but all usual rules apply. Don’t believe anything you read on this blog post, don’t run any scripts on production, test before running any scripts, understand what the code is doing before you run it or I am not responsible if you break anything
Here is the script
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 |
<# .NOTES Name: Refresh Mirrored Database Author: Rob Sewell http://sqldbawithabeard.com Requires: Invoke-SQLCMD2 (included) Version History: 1.2 22/08/2014 .SYNOPSIS Refreshes a mirrored database .DESCRIPTION This script will refresh a mirrored database, recreate mirroring and chekc status of mirroring. Further details on the website Requires the variables at the top of the script to be filled in IMPORTANT - Orpahaned users are not resolved with this acript without additions. See blog post for options #> # Load Invoke-SQLCMD2 #Load the assemblies the script requires [void][reflection.assembly]::LoadWithPartialName( "Microsoft.SqlServer.Management.Common" ); [void][reflection.assembly]::LoadWithPartialName( "Microsoft.SqlServer.SmoEnum" ); [void][reflection.assembly]::LoadWithPartialName( "Microsoft.SqlServer.Smo" ); [void][reflection.assembly]::LoadWithPartialName( "Microsoft.SqlServer.SmoExtended " ); [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")|Out-Null # Set up some variables $PrincipalServer = '' # Enter Principal Server Name $MirrorServer = '' # Enter Mirror Server Name $DBName = '' # Enter Database Name $FileShare = '' # Enter FileShare with trailing slash $LocationReplace = $FileShare + $DBName + 'Refresh.bak' $LocationFUll = $FileShare + $DBName + 'formirroring.bak' $LocationTran = $FileShare + $DBName + 'formirroring.trn' $PrincipalEndPoint = 'TCP://SERVERNAME:5022' # Change as required $MirrorEndpoint = 'TCP://SERVERNAME:5022' # Change as required $WitnessEndpoint = 'TCP://SERVERNAME:5022' # Change as required $Full = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Database $Tran = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Log $File = [Microsoft.SqlServer.Management.Smo.DeviceType]::File ###################### <# .SYNOPSIS Runs a T-SQL script. .DESCRIPTION Runs a T-SQL script. Invoke-Sqlcmd2 only returns message output, such as the output of PRINT statements when -verbose parameter is specified .INPUTS None You cannot pipe objects to Invoke-Sqlcmd2 .OUTPUTS System.Data.DataTable .EXAMPLE Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -Query "SELECT login_time AS 'StartTime' FROM sysprocesses WHERE spid = 1" This example connects to a named instance of the Database Engine on a computer and runs a basic T-SQL query. StartTime ----------- 2010-08-12 21:21:03.593 .EXAMPLE Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -InputFile "C:\MyFolder\tsqlscript.sql" | Out-File -filePath "C:\MyFolder\tsqlscript.rpt" This example reads a file containing T-SQL statements, runs the file, and writes the output to another file. .EXAMPLE Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -Query "PRINT 'hello world'" -Verbose This example uses the PowerShell -Verbose parameter to return the message output of the PRINT command. VERBOSE: hello world .NOTES Version History v1.0 - Chad Miller - Initial release v1.1 - Chad Miller - Fixed Issue with connection closing v1.2 - Chad Miller - Added inputfile, SQL auth support, connectiontimeout and output message handling. Updated help documentation v1.3 - Chad Miller - Added As parameter to control DataSet, DataTable or array of DataRow Output type #> function Invoke-Sqlcmd2 { [CmdletBinding()] param( [Parameter(Position = 0, Mandatory = $true)] [string]$ServerInstance, [Parameter(Position = 1, Mandatory = $false)] [string]$Database, [Parameter(Position = 2, Mandatory = $false)] [string]$Query, [Parameter(Position = 3, Mandatory = $false)] [string]$Username, [Parameter(Position = 4, Mandatory = $false)] [string]$Password, [Parameter(Position = 5, Mandatory = $false)] [Int32]$QueryTimeout = 600, [Parameter(Position = 6, Mandatory = $false)] [Int32]$ConnectionTimeout = 15, [Parameter(Position = 7, Mandatory = $false)] [ValidateScript( {test-path $_})] [string]$InputFile, [Parameter(Position = 8, Mandatory = $false)] [ValidateSet("DataSet", "DataTable", "DataRow")] [string]$As = "DataRow" ) if ($InputFile) { $filePath = $(resolve-path $InputFile).path $Query = [System.IO.File]::ReadAllText("$filePath") } $conn = new-object System.Data.SqlClient.SQLConnection if ($Username) { $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance, $Database, $Username, $Password, $ConnectionTimeout } else { $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance, $Database, $ConnectionTimeout } &n bsp; $conn.ConnectionString = $ConnectionString #Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller if ($PSBoundParameters.Verbose) { $conn.FireInfoMessageEventOnUserErrors = $true $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Verbose "$($_)"} $conn.add_InfoMessage($handler) } $conn.Open() $cmd = new-object system.Data.SqlClient.SqlCommand($Query, $conn) $cmd.CommandTimeout = $QueryTimeout $ds = New-Object system.Data.DataSet $da = New-Object system.Data.SqlClient.SqlDataAdapter($cmd) [void]$da.fill($ds) $conn.Close() switch ($As) { 'DataSet' { Write-Output ($ds) } 'DataTable' { Write-Output ($ds.Tables) } 'DataRow' { Write-Output ($ds.Tables[0]) } } } #Invoke-Sqlcmd2 # Check for existence of Backup file with correct name If (!(Test-Path $LocationReplace)) { Write-Output " There is no file called " Write-Output $LocationReplace Write-Output "Please correct and re-run" break } # Remove Old Backups if (Test-Path $locationFull) { Remove-Item $LocationFUll -Force } if (Test-Path $locationTran) { Remove-Item $LocationTran -Force } # Create Server objects $Principal = New-Object Microsoft.SQLServer.Management.SMO.Server $PrincipalServer $Mirror = New-Object Microsoft.SQLServer.Management.Smo.server $MirrorServer #Create Database Objects $DatabaseMirror = $Mirror.Databases[$DBName] $DatabasePrincipal = $Principal.Databases[$DBName] # If database is on Mirror server fail it over to Principal if ($DatabasePrincipal.IsAccessible -eq $False) { $DatabaseMirror.ChangeMirroringState([Microsoft.SqlServer.Management.Smo.MirroringOption]::Failover) } # remove mirroring $DatabasePrincipal.ChangeMirroringState([Microsoft.SqlServer.Management.Smo.MirroringOption]::Off) #Set up Restore using refresh backup $restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore $restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationReplace, $File)|Out-Null $restore.Database = $DBName $restore.ReplaceDatabase = $True $restore.Devices.add($restoredevice) #Perform Restore $restore.sqlrestore($PrincipalServer) # if query time < 600 seconds # $query = $restore.Script($PrincipalServer) # if using Invoke-SQLCMD2 $restore.Devices.Remove($restoredevice) # Invoke-Sqlcmd2 -ServerInstance $PrincipalServer -Database master -Query $query -ConnectionTimeout 0 # comment out if not used # Set up Full Backup $Backup = New-Object Microsoft.SqlServer.Management.Smo.Backup $Backup.Action = $Full $Backup.BackupSetDescription = "Full Backup of " + $DBName $Backup.Database = $DatabasePrincipal.Name $BackupDevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationFull, $File) $Backup.Devices.Add($BackupDevice) # Perform Backup $Backup.SqlBackup($PrincipalServer) # $query = $Backup.Script($PrincipalServer) # if query time < 600 seconds $Backup.Devices.Remove($BackupDevice) # Invoke-Sqlcmd2 -ServerInstance $PrincipalServer -Database master -Query $query -ConnectionTimeout 0 # comment out if not used #Setup Trans Backup $Backup = New-Object Microsoft.SqlServer.Management.Smo.Backup|Out-Null $Full = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Database $Tran = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Log $File = [Microsoft.SqlServer.Management.Smo.DeviceType]::File $Backup.Action = $Tran $Backup.BackupSetDescription = "Log Backup of " + $DBName $Backup.Database = $DBName $BackupDevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationTran, $File)|Out-Null $Backup.Devices.Add($BackupDevice) # Perform Backup $Backup.SqlBackup($PrincipalServer) # $query = $Backup.Script($PrincipalServer) # if query time < 600 seconds $Backup.Devices.Remove($BackupDevice) # Invoke-Sqlcmd2 -ServerInstance $PrincipalServer -Database master -Query $query -ConnectionTimeout 0 # comment out if not used #Set up Restore of Full Backup on Mirror Server $restore = New-Object -TypeName Microsoft.SqlServe r.Management.Smo.Restore|Out-Null $restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationFUll, $File)|Out-Null $restore.Database = $DBName $restore.ReplaceDatabase = $True $restore.NoRecovery = $true $restore.Devices.add($restoredevice) $restore.sqlrestore($MirrorServer) # if query time < 600 seconds # $query = $restore.Script($MirrorServer) # if using Invoke-SQLCMD2 $restore.Devices.Remove($restoredevice) # Invoke-Sqlcmd2 -ServerInstance $MirrorServer -Database master -Query $query -ConnectionTimeout 0 # comment out if not used # Set up Restore of Log Backup on Mirror Server $restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore|Out-Null $restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationTran, $File)|Out-Null $restore.Database = $DBName $restore.ReplaceDatabase = $True $restore.NoRecovery = $true $restore.Devices.add($restoredevice) $restore.sqlrestore($MirrorServer) $restore.Devices.Remove($restoredevice) #Recreate Mirroring $DatabaseMirror.MirroringPartner = $PrincipalEndPoint $DatabaseMirror.Alter() $DatabasePrincipal.MirroringPartner = $MirrorEndpoint $DatabasePrincipal.MirroringWitness = $WitnessEndpoint $DatabasePrincipal.Alter() # Resolve Orphaned Users if needed #Check that correct file and backup date used $query = "SELECT TOP 20 [rs].[destination_database_name] as 'database', [rs].[restore_date] as 'restoredate', [bs].[backup_finish_date] as 'backuptime', [bmf].[physical_device_name] as 'Filename' FROM msdb..restorehistory rs INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id] INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id] ORDER BY [rs].[restore_date] DESC" Invoke-Sqlcmd2 -ServerInstance $PrincipalServer -Database msdb -Query $query |Format-Table -AutoSize -Wrap $DatabasePrincipal | select Name, MirroringStatus, IsAccessible |Format-Table -AutoSize |
Pingback: (SFTW) SQL Server Links 29/08/14 - John Sansom
Nice blog post Rob! Automation definitely the way to go.
Nice blog post Rob! Automation is definitely the way to go.
Thank you, I like to make things easy and repeatable, it saves time and errors
Nice post! I enjoy seeing other DBAs embracing PowerShell to automate tasks.
I did notice in your code that you made the comment “# if query time < 600 seconds”. You can work around this default command timeout by adding the following code after you initialize your principal and mirror objects…
## Allow any queries issued through the respective Server objects to run forever.
$Principal.ConnectionContext.StatementTimeout = 0
$Mirror.ConnectionContext.StatementTimeout = 0
Thank you for your comment. Using SQL 2008R2 this did not work for me but for SQL 2012 onwards it has
Hi SQLDBAWithTheBeard,
While i am executing the script getting the below error from recreating mirror database step :
\\10.209.18.117\BackupShare\
\\10.209.18.117\BackupShare\formirroring.bak
\\10.209.18.117\BackupShare\formirroring.trn
TCP://10.209.18.117:5022
TCP://10.209.22.155:5022
True
True
True
True
The property ‘MirroringPartner’ cannot be found on this object. Verify that the property exists and can be set.
At line:152 char:1
+ $DatabaseMirror.MirroringPartner = $PrincipalEndPoint
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : PropertyNotFound
You cannot call a method on a null-valued expression.
At line:153 char:1
+ $DatabaseMirror.Alter()
+ ~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
Exception calling “Alter” with “0” argument(s): “Alter failed for Database ‘aircomSrv’. ”
At line:155 char:1
+ $DatabasePrincipal.Alter()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : FailedOperationException
Are the values for MirrorServer and DbName correct? Is the mirror server accessible?
If you run the script (even if it fails) do you have any results if you then run $DatabaseMirror
Hi, not sure whether the functionality has changed with later versions of SMO, however I cannot see how line 232 ‘$DatabaseMirror.Alter()’ can run as this is executed against the database that is in recovery and I get following error “VERBOSE: Database ‘nexus’ cannot be opened. It is in the middle of a restore.” Is there something I’ve missed in your script the ensures the Alter is performed against master?