PowerShell script exchange
Post Reply
anon_dev
Novice
Posts: 3
Liked: never
Joined: Oct 18, 2022 7:42 am
Contact:

SQL Server Log Backup Report

Post by 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
Veeam Software
Posts: 1963
Liked: 650 times
Joined: Sep 25, 2019 10:32 am
Full Name: Oleg Feoktistov
Contact:

Re: SQL Server Log Backup Report

Post by 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
Novice
Posts: 3
Liked: never
Joined: Oct 18, 2022 7:42 am
Contact:

Re: SQL Server Log Backup Report

Post by 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
Veeam Software
Posts: 1963
Liked: 650 times
Joined: Sep 25, 2019 10:32 am
Full Name: Oleg Feoktistov
Contact:

Re: SQL Server Log Backup Report

Post by 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
Novice
Posts: 3
Liked: never
Joined: Oct 18, 2022 7:42 am
Contact:

Re: SQL Server Log Backup Report

Post by anon_dev »

Hi,

thanks, that seems to help a lot. If any further questions arise, i will let you know.
kpolsana
Service Provider
Posts: 56
Liked: 2 times
Joined: Dec 10, 2021 6:35 pm
Full Name: Koushik Polsana
Contact:

Re: SQL Server Log Backup Report

Post by 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
Service Provider
Posts: 56
Liked: 2 times
Joined: Dec 10, 2021 6:35 pm
Full Name: Koushik Polsana
Contact:

Re: SQL Server Log Backup Report

Post by 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
Lurker
Posts: 2
Liked: never
Joined: Jan 27, 2019 11:15 pm
Full Name: Zahra Harati
Contact:

Re: SQL Server Log Backup Report

Post by 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!
Post Reply

Who is online

Users browsing this forum: No registered users and 5 guests