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 | Format-Table -AutoSize
You can also find the script on poshcode.org: http://poshcode.org/1316
The script generate the following output: