SQL Server Log Backup Report

[anon_dev]

Hi there,

I am new here an really searched for an existing topic, but I was not able find anythin similar.

I have to build an reportabout SQL Server Transcation Logs. I am able to retrieve all required data as a Report from the management Console. What I need is a programmtic way to get the data within this report. At first I tried via REST API, which seems not to be the correct way. Now I am focused on Powershell. Is there a way to either get the data of the report or generate the report itself by calling a Powershell Function? Both Methods would be suitable for my case.

The Report I a talking about can be generated by right-click on our MSSQL Job (Type: Windows Agent Backup) -> Report -> Server SQL Log Backup. Unfortunatly I a not able to attach a Screenshot.

Thanks

[oleg.feoktistov]

Hi,

Unfortunately there is no official cmdlet to get log backup sessions.
However, you can get them using workaround with .NET methods below:

Code: Select all

$jobType = [Veeam.Backup.Model.EDbJobType]::EndpointSqlLogBackup
$sessions = [Veeam.Backup.Core.CBackupSession]::GetByJobType($jobType)

The example is related to windows agent backup job, but if you need another type, have a look at values in EDbJobType.
The session object should hold most of the properties needed, bu let me know if you have any further questions.

Best regards,
Oleg

[anon_dev]

Hi,

thanks for your help.
Although the session object holds many information, the most important for the report our customer requestst are not available there, (but within the report I mentioned before).
What I need are the data in the "details" Section of the report: The Database Name, Amount of Data "Read" and amount of Data "Transferred".
Would be great if there is any possibility to retrieve those data programmatically.

Thanks and best regards

[oleg.feoktistov]

Hi,

Looks like I found a workaround for this as well:

Code: Select all

$jobType = [Veeam.Backup.Model.EDbJobType]::EndpointSqlLogBackup
$sessions = [Veeam.Backup.Core.CBackupSession]::GetByJobType($jobType)
$databasesTotal = @()
foreach ($session in $sessions) {
  $taskSession = $session.GetTaskSessions()
  $intervalSessions = [Veeam.Backup.Core.CSqlBackupIntervalSession]::GetInfosByTaskSessionId($taskSession.Id)
  foreach ($intervalSession in $intervalSessions) {
    $databases = $intervalSession.Databases.GetAll()
    foreach ($database in $databases) {
      $databasesTotal += $database
    } 
    
  }
}

$databasesTotal | select FriendlyName, BackupResult, Read, Transferred

The example above is for databases details for each log backup session interval. You can then group databases and stats to reflect the sums for unique databases.
If you need further interval details like in the UI report, check the variable from this line:

Code: Select all

 $intervalSessions = [Veeam.Backup.Core.CSqlBackupIntervalSession]::GetInfosByTaskSessionId($taskSession.Id)

And if required write an array of them to a new variable like I did for databases in the script.

Let me know if you have any questions.

Thanks,
Oleg

[anon_dev]

Hi,

thanks, that seems to help a lot. If any further questions arise, i will let you know.

[kpolsana]

I was trying to use the below script on VBR V12 but getting following errors

$jobType = [Veeam.Backup.Model.EDbJobType]::EndpointSqlLogBackup
$sessions = [Veeam.Backup.Core.CBackupSession]::GetByJobType($jobType)
$databasesTotal = @()
foreach ($session in $sessions) {
$taskSession = $session.GetTaskSessions()
$intervalSessions = [Veeam.Backup.Core.CSqlBackupIntervalSession]::GetInfosByTaskSessionId($taskSession.Id)
foreach ($intervalSession in $intervalSessions) {
$databases = $intervalSession.Databases.GetAll()
foreach ($database in $databases) {
$databasesTotal += $database
}

}
}

$databasesTotal | select FriendlyName, BackupResult, Read, Transferred

Following Errors

Method invocation failed because [Veeam.Backup.Core.CBackupSession] does not contain a method named 'GetByJobType'.
At line:1 char:1
+ $sessions = [Veeam.Backup.Core.CBackupSession]::GetByJobType($jobType ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound

[kpolsana]

Looks like the function in V12 was renamed to as following
$sessions = [Veeam.Backup.Core.CBackupSession]::GetByJobTypes($jobType)

so the updated script is as following

$jobType = [Veeam.Backup.Model.EDbJobType]::EndpointSqlLogBackup
$sessions = [Veeam.Backup.Core.CBackupSession]::GetByJobTypes($jobType)
$databasesTotal = @()
foreach ($session in $sessions) {
$taskSession = $session.GetTaskSessions()
$intervalSessions = [Veeam.Backup.Core.CSqlBackupIntervalSession]::GetInfosByTaskSessionId($taskSession.Id)
foreach ($intervalSession in $intervalSessions) {
$databases = $intervalSession.Databases.GetAll()
foreach ($database in $databases) {
$databasesTotal += $database
}

}
}

$databasesTotal | select FriendlyName, BackupResult, Read, Transferred

[haratiz]

I tried this script, and it is not working for me. We are running version 12. VeeamOne has a report called: Database Log Transaction History

The only issue is that if I run it from the VeeamOne server, I can drill down to the servers and see each database.

If I export to a PDF or Excel and open the report on another computer, I cannot click on the hyperlink.

I understand why. There are no connections to the database from my laptop. However, the report is exactly what I need. I only need the report for some of the SQL jobs.VeeamOne lets me choose.

Thoughts? Do you have a script that works? Or is there a way to Export the VeeamOne report with all the details in the hyperlink?

Thanks!