Powershell: Script to Query the Veeam Backup database

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: