Powershell to get status last backup of SQL-server

[leeuwena5]

Hi,

I am new to VEEAM B&R. We have the on-premise installation, version 12.3...

We have set up a Protection Group, for a failover cluster.
Two Windows-servers are part of this cluster, and therefor this PG.
Application-integration for SQL has been enabled, application log are truncated.
Job details:
OSPlatform : Windows
Type : FailoverCluster
Mode : ManagedByBackupServer
BackupType : SelectedVolumes

A customer wants daily reports about the status of the backup of one particular database in this cluster.

I have tried several cmdets, scripts, to get the information needed, however I cannot get the session information..

Used:
Get-VBRcomputerBackupJobSession -Name "backupjobname" | Get-VBRTaskSession -Name "servernaam" | Select *

Used:
$job = Get-VBRComputerBackupJob -Name "backupjobname"
foreach ($sessions in $job)
{
$jname = Get-VBRComputerBackupJobSession -Name "$($sessions.Name)?*"
$jname1=$jname| where-object {$_.EndTime -ge (Get-Date).addhours(-24)}
foreach ($session in $jname1)
{
Get-VBRTaskSession -Session $session| Select-Object -Property Name,status
}
}

Either no information is returned, or the commando times out.

Anyone have a script ready, or pointers for me?

Thanks in advance!

[david.domask]

Hi Arjan,

Just to confirm, you have a SQL Transaction Log backup enabled as part of the SQL Cluster backup and you want to report on the Transaction Log backups? Or it's about backup of the server hosting the SQL databases themselves?

For transaction log backups, please review the methods listed here, they will help you get Log Backup session data.

For getting information on the server backup, at first blush your code looks correct. Can you reproduce the timeout and share the error regarding the timeout here? Just copy/paste whatever's in your shell when it happens please.

[leeuwena5]

I want to report on the success of the backup of one of the databases, not particulary about the transaction logs.

$Job = Get-VBRJob -name "backupjobname"
$SQLJob = $Job.FindChildSqlLogBackupJob()
$Session = [Veeam.Backup.Core.CBackupSession]::GetByJob($SQLJob.Id) | select -Last 1
Cannot convert argument "jobId", with value: "", for "GetByJob" to type "System.Guid": "Cannot convert null to type "System.Guid"."
At line:1 char:1
+ $Session = [Veeam.Backup.Core.CBackupSession]::GetByJob($SQLJob.Id) | ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodException
+ FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument

$SQLJob is empty.

Get-VBRJOB does result in some information:
Job Name Type State Last Result Description
-------- ---- ----- ----------- -----------
Backupjobname Windows Agent Backup Stopped Success VEEAM Backupjob voor SQL-cluster

$job = Get-VBRComputerBackupJob -Name "backupjobname"
foreach ($sessions in $job)
{
$jname = Get-VBRComputerBackupJobSession -Name "$($sessions.Name)?*"
$jname1=$jname| where-object {$_.EndTime -ge (Get-Date).addhours(-24)}
foreach ($session in $jname1)
{
Get-VBRTaskSession -Session $session| Select-Object -Property Name,@{n='Jobname';e= {$($sessions.Name)}},status,@{n='StartTime';e={$_.Progress.StartTimeLocal}}, @{n='EndTime';e={$_.Progress.StopTimeLocal}},@{n='TransferedSizeinMB';e={[math]::Round(($_.Progress.TransferedSize/1048576),2)}}
}
}

It is just empty, no result, no information...

[david.domask]

Hi Arjan,

The examples I provided in the previous link were for SQL Transaction Log backups only -- if you don't see those enabled/running in your Veeam, then that one is not for you, it just was not quite clear what you were trying to retrieve.

Can you maybe show a screenshot of what you're trying to report on? Individual databases are only reported on within either plugin-based backups (MSSQL Plugin, RMAN Plugin, etc) or within Transaction Log Backups, and I'm getting the impression you're not using either.

As for your code with Get-VBRComputerBackupJob, the code at first blush looks alright, but can you run it line by line and show the output of each variable you try to set? My guess is that

$jname = Get-VBRComputerBackupJobSession -Name "$($sessions.Name)?*"

Is not populating $jname correctly, but I would have expected an error on the sessions personally, so if possible please:

1. Provide a screenshot of what in the UI you're trying to report on with Powershell
2. Test your existing script with Get-VBRComputerBackupJob and print the resulting variables ($job, $jname, $jname1) and check that they're populated correctly.

[leeuwena5]

Hi David,

Thank you for taking the time to help me!

First off, here are the job(s) I want to report about:


We have a Windows Agent Backupjob, backing up a VEEAM Protection Group consisting of a Windows Failover Cluster with two Windows servers hosting a SQL Availability Group.




The Windows Agent Backup backups up all volumes, including the volumes containing the SQL databases and logfiles. In this backup, we enabled Application Integration and the VEEAM truncates the SQL Transaction Logs...

If I enable reporting on this job, I receive an email once a day about the completion of this job and all the databases on these servers (all 147 of them).

I have multiple teams requesting a report on only their databases, they do not want to search for their database(s) in these long reports.
So, that is why I tried to create a report using Powershell.

Now on to the script (results):

As you can see, the variable $sessions does get filled up, but Get-VBRComputerBackupJobSession does not do anything, and therefore $jname is empty.