[FEATURE REQUEST] Add timeout to Restore-VESQLDatabase

[mwalton]

Hi,

I'd like to request the addition of a timeout for Restore-VESQLDatabase. I have an open case (#04497966) with Veeam support where a restore job sometimes fails to finish and causes all other jobs that follow to fail. I have to manually cancel the restore using Veeam B&R console. I tried to add a timeout to my powershell script but unfortunately I find that the job state for the Restore-VESQLDatabase is always 'complete' so that doesn't help. If there was a timeout setting in Restore-VESQLDatabase I could simply update my script to include this timeout so that any database restore that takes more than x hours should be timed out and cancelled.

Thanks,
Matt

[oleg.feoktistov]

Hi Matthew,

Timeout is a simple function, which we should be able to script in PS. So, there is no need to reinvent the wheel.
Can you, please, share your script here? I'm sure that there should be a way to code it.

Thanks,
Oleg

[mwalton]

Hello Oleg and thanks for your response.

I've found that the typical solutions to implement a timeout in the script don't work because when I issue the Start-job the parameters are deserialized and cannot be used in the job. I missed this when I was posting this enhancement request. It's true that when I run the Restore-VESQLDatabase cmdlet that the job's state in Get-Job is 'Completed' almost immediately but I missed that the reason it completed so quickly was that some of the variables when passed to the new job are deserialized and cannot be used to start the job. Therefore, no timeout will work in the script since I cannot find a way to serialize that value back to the original type. For example, here is code that I use in the scriptblock that I would like to be timed out if long running:

Code: Select all

Start-Job -name matthew -ScriptBlock {
Restore-VESQLDatabase -Database $args[0] -DatabaseName $args[1] -ServerName $args[2] -InstanceName $args[3] -TargetFolder "H:\VeeamRestoreDirectory" -ToPointInTime $args[4].ToUtc
} -ArgumentList $database,$DatabaseName, $ServerName, $InstanceName, $restoreinterval

When I start the job the status goes from 'Running' to 'Completed' and the Receive-Job gives me the reason why:

Code: Select all

Receive-Job -name matthew
Cannot bind parameter 'Database'. Cannot convert the "ReportServer - " value of type
"Deserialized.Veeam.SQL.PowerShell.VESQLDatabase" to type "Veeam.SQL.PowerShell.VESQLDatabase".
+ CategoryInfo : InvalidArgument: (:) [Restore-VESQLDatabase], ParameterBindingException
+ FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Veeam.SQL.PowerShell.RestoreVESQLDatabaseCmdlet
+ PSComputerName : localhost

I know this is outside the scope of this enhancement request, but any ideas on how I could serialize the parameter value back to the original type after I pass it as part of the new job? After searching a bit online, I thought that maybe I could just update the Veeam.Backup.PowerShell.types.ps1xml in C:\Program Files\Veeam\Backup and Replication\Console to include the mapping to serialize but that didn't work.

Code: Select all

    <Type>
         <Name>Deserialized.Veeam.SQL.PowerShell.VESQLDatabase</Name>
         <Members>
             <MemberSet>
                <Name>PSStandardMembers</Name>
                <Members>
                    <NoteProperty>
                        <Name>TargetTypeForDeserialization</Name>
                        <Value>Veeam.SQL.PowerShell.VESQLDatabase</Value>
                    </NoteProperty>
                </Members>
              </MemberSet>
          </Members>
    </Type>

Thanks and I can move this over to the forum if you cannot think of any easy answer.

-Matt

[mwalton]

Here is the script I use to test things out:

Code: Select all

Add-PSSnapin VeeamPSSnapin
$ServerInstance = 'SQLServer_and_Instance_name' ##SQL server hostname and instance that was backed up by Veeam B&R
$catalogDBfilter = 'Database_to_restore' ##this is leftover from my main script...just the DB name
##need credentials to connect to Veeam B&R
$password = Get-Content "H:\VeeamRestoreDirectory\veeamrestore\encrypted.txt" | ConvertTo-SecureString
$TargetCreds = New-Object System.Management.Automation.PsCredential("mydomain\myuser",$password)
Connect-VBRServer -Server "veeambr-server" -Credential $TargetCreds
##Splitting the hostname and instance name
$OriginServer = $ServerInstance.split('\') | Select -First 1
$OriginServer
$OriginServerInstance = $ServerInstance.split('\') | Select -Skip 1
if ($OriginServerInstance -eq $null) {$OriginServerInstance = ""} else {$OriginServerInstance}
## I restore backups to a different SQL server and instance...that info goes below
$ServerName = "ServerName_for_restore"
$InstanceName = "DBRESTORE" # SQL Instance name.  If you have default instance, use " "
$DatabaseName = $catalogDBfilter
## get latest restore point
$restorepoint = Get-VBRApplicationRestorePoint -SQL -Name $OriginServer | Sort -Property CreationTime -Descending | Select -First 1
$restorepoint
## Start the restore session
$sessions = Start-VESQLRestoreSession -RestorePoint $restorepoint
## Need to get the DB to restore to use with later cmdlet
$database = Get-VESQLDatabase -Session $sessions[0] -Name $DatabaseName | Where-Object -Property InstanceName -eq $OriginServerInstance
$database
#Get restore interval
$restoreinterval = Get-VESQLDatabaseRestoreInterval -Database $database
#setting timeout for long-running jobs and running job.  normally if this worked I would want to cancel the restore job but that's for later
$timeoutSeconds = 15
$code = {Restore-VESQLDatabase -Database $args[0] -DatabaseName $args[1] -ServerName $args[2] -InstanceName $args[3] -TargetFolder "H:\VeeamRestoreDirectory\" -ToPointInTime $args[4].ToUtc}
$j = Start-Job -name matthew -ScriptBlock $code -ArgumentList $database,$DatabaseName,$ServerName,$InstanceName,$restoreinterval
Get-Job -name matthew ## this should show state of 'running'
Start-Sleep -s 5 ##just added this here so that the job state will show 'completed' and I can get the error from Receive-Job.  make sure this value is less than the timeout.
#Receive-Job -name matthew
if (Wait-Job $j -Timeout $timeoutSeconds) { $fullnamexp = Receive-Job -name matthew }
Remove-Job -force -Name matthew
#Clean exit from session
$StopSession = Stop-VESQLRestoreSession -Session $sessions[0]
Disconnect-VBRServer | out-null

[oleg.feoktistov]

Hi Matthew,

I tried to find a way to serialise them back to the right types, but haven't succeeded so far.
Just a question - any particular reason you can't wrap the whole script in Start-Job, run it asynchronously and then listen to the particular restore session to stop it on a timeout explicitly instead of stopping background powershell job?

Thanks,
Oleg

[mwalton]

Thanks Oleg,

I had entertained doing that but my problem is I want to close out the session cleanly using the Stop-VESQLRestoreSession which only accepts the VESQLRestoreSession from Get-VESQLRestoreSession. If the job is stuck and I have no way to interact with the restore session then I cannot get the VESQLRestoreSession object. Maybe there is a way and I'm thinking about this wrong but I could not find a way to store that information since it too is deserialized when exported out of the PS session.

Thanks
Matt

[oleg.feoktistov]

Hi Matthew,

Sorry for being away. I have managed to duly test your case only now. Turned out that Get-VESQLRestoreSession returns running sql restore sessions only if they were launched in the same PS session. Since Start-Job invokes a separate process, it doesn't reflect anything.
Here is the possible workaround:

- Obtain restore session of CBaseSession class.
- Stop it calling AbortSession() method.

Code: Select all

$restoreSession = [Veeam.Backup.Core.CBaseSession]::GetRunning() | where {$_.JobType -eq 'ApplicationLevelRestore'}
$restoreSession.AbortSession()

It involves diving into core classes, so this workaround is not officially supported. But it worked for me - the session was stopped almost immediately.

Let me know if it helps.


Thanks,
Oleg

[mwalton]

Thanks Oleg! I'll give this a try and let you know how it goes.

[mwalton]

Hi Oleg,

When I tried to abort the session I got the following error:

Code: Select all

$convertedSession.AbortSession()
Method invocation failed because [Veeam.Backup.PowerShell.Infos.VBRBackupSession] does not contain a method named
'AbortSession'.
At line:1 char:1
+ $convertedSession.AbortSession()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : MethodNotFound

What do you think I'm missing in my session or environment that would cause this error? I'm running version 10 of Veeam Backup and Replication.

Thank you,
Matt

[oleg.feoktistov]

Hi Matthew,

Sorry, I overcomplicated things. You don't need to convert the session to another type. Just call AbortSession() right on your CBaseSession instance. I amended my original post for further reference. Please have a look.

Thanks,
Oleg

[mwalton]

Thanks a lot for the help with this Oleg. I updated my script yesterday and today I actually ran into the issue that started me down this path.

This morning I had a scheduled SQL Agent restore job that was stuck and was going to run all weekend if I didn't manually check on it or have a timeout. I know this job typically runs for about an hour if there are no issues so I passed my timeout as 75 minutes (instead of my default of 360) and waited to see what would happen if it did indeed fail (it only fails about once a month). A few minutes ago I got a message that the job had failed and when I checked my SQL instance where I run the job and also kick off the PowerShell job I noticed that everything had cleaned up nicely. I no longer have a Veeam job that is stuck running and the PowerShell process is also closed out nicely. Here is the part of my script where I implement the timeout and which may still not be the best designed but it works for me now.

thanks again!
Matt

For the sake of brevity I excluded the actual script block code:

Code: Select all

param([string]$ServerInstance,[int32]$IsAG=0,[int32]$FullSystemAccess=1,[int32]$MaxDBSizeMB=102000,[string]$CatalogFilter="",[int32]$DaysAgo=0,[int32]$TimeoutMinutes=360) #Input Parameters.  Must be the first statement in your script
#setting timeout for long-running jobs and running job.  normally if this worked I would want to cancel the restore job but that's for later
Add-PSSnapin VeeamPSSnapin
$password = Get-Content "Path_to_file_with_encrypted_password\encrypted.txt" | ConvertTo-SecureString
$TargetCreds = New-Object System.Management.Automation.PsCredential("domain\user",$password)
Connect-VBRServer -Server "veeambr_hostname" -Credential $TargetCreds
#setting the timeout and starting the timer to monitor the job so that it can be cancelled later if long-running
$timeoutSeconds = $TimeoutMinutes*60
$timer = [Diagnostics.Stopwatch]::StartNew()
#Code is moved into this script block so that it can be run as part of a Start-Job and new job and monitored from this process
$code = {
My SQL DB restore code is in this section now.
}
$j = Start-Job -name $ServerInstance -ScriptBlock $code -ArgumentList $ServerInstance,$IsAG,$FullSystemAccess,$MaxDBSizeMB,$CatalogFilter,$DaysAgo
Get-Job -name $ServerInstance ## this should show state of 'running'
 while (($timer.Elapsed.TotalSeconds -lt $timeoutSeconds) -AND (Get-job -name $ServerInstance | where state -eq Running))
 {
	 Start-Sleep -Seconds 120
	 "Still restoring databases."
	 Get-Job -name $ServerInstance
 }
 ## The action either completed or timed out. Stop the timer.
 $timer.Stop()
 if ($timer.Elapsed.TotalSeconds -gt $timeoutSeconds) {
$restoreSession = [Veeam.Backup.Core.CBaseSession]::GetRunning() | where {($_.JobType -eq 'ApplicationLevelRestore') -AND ($_.JobName -like "*" + $ServerName + "*")}
$restoreSession.AbortSession()
"Restore timeout exceeded"
Receive-Job -Keep -Name $ServerInstance -ErrorAction Stop
Disconnect-VBRServer | out-null
[System.Environment]::Exit(1)
}
Else
{
"Restore finished in allotted time."
Receive-Job -Keep -Name $ServerInstance
Disconnect-VBRServer | out-null
}
[Environment]::Exit(0)

[oleg.feoktistov]

I'm glad we could figure out the workaround. Thanks!