Veeam: A file level restore gets stuck in a "stopping" state.


Today a colleague of mine was trying to perform a file level restore on one of the Veeam servers. But unfortunately the job became unresponsive and got stuck in a stopping state. See the print screen below:

image

To fix this issue, you need to run a SQL query on the Veeam database to clear the locks. I have included the solution form KB1534:

Problem:

A file level restore gets stuck in a "stopping" or "starting" state.

Cause:

This issue typically occurs when the VeeamBackup database has become out of sync with Veeam and the true state of the File Level Restore is not updated in the GUI.

Solution:

To fix this you will need to run a SQL query against the VeeamBackup Database, instructions are as shown below. 
Before running this SQL query against the “VeeamBackup” Database, please make sure that there are currently no jobs running. If you are unaware of how to run a query against a DB please read the steps below, if you are aware please disregard.
1.       Open up SQL Server Management Studio and connect to the VEEAM instance.
2.       Expand “Databases”.
3.       Right-click “VeeamBackup”>New query.
4.       Paste the query below into the query window and hit either “execute” or F5.

The query:

UPDATE [ReportRestoreSessionsAndTaskSessionsView]
   SET "state" = -1 
    WHERE "initiator_name" not like 'null'

After running the query and restarting the Veeam console. My colleague was able to perform the file level restore.

Source: http://www.veeam.com/kb1534

Powershell: Script to Query the Veeam Backup database


image

Earlier this year I wrote a post about how to query the Veeam Backup SQL database to get the total job running time. I wanted to see if I was able to run this Query via Powershell. So I started to search on Google and I found a great series of articles on http://www.databasejournal.com about how to use Powershell to access Microsoft SQL databases. After reading part two, I was able to create a script to run my Query.

The only thing you have to change are the next three variables:

$dbServer = "servername\instance" 
$db = "VeeamBackup"
$veeamJob = "VeeamJobName"

 

Run the next script to query the Veeam Backup database and return the total job time.

$dbServer = "servername\instance"
$db = "VeeamBackup"
$veeamJob = "VeeamJobName"
$Query = "SELECT [job_name],CONVERT(char(10),[creation_time], 101) AS start_date `
,CONVERT(varchar, [creation_time], 108) AS job_start,CONVERT(char(10), [end_time], 101) AS end_date `
,CONVERT(varchar, [end_time], 108) AS job_end, `
LEFT(CONVERT(VARCHAR,CAST([end_time] AS DATETIME)-CAST([creation_time] AS DATETIME), 108),5) AS total_time `
FROM [VeeamBackup].[dbo].[BSessions] WHERE [job_name] = '$veeamJob' ORDER BY start_date"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$dbServer;Database=$db;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $Query
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0] | Format-Table -AutoSize 

You can also find the script on poshcode.org: http://poshcode.org/1316

 

The script generate the following output:

image