Get SQLLogBackup transaction log status

[jroig]

Hi, I am trying to get the status of the SQL Transaction log intervals using PowerShell.
I have the following code:

Code: Select all

$Job = Get-VBRJob -name db
$SQLJob = $Job.FindChildSqlLogBackupJob()
$Session = [Veeam.Backup.Core.CBackupSession]::GetByJob($SQLJob.Id) | select -Last 1 ## The Working session
$taskSession = (Get-VBRTaskSession -Session $session)[1] ## Using the first one as I have 3 hosts here

$logBackupLogs = $taskSession.Logger.GetLog().UpdatedRecords

The code works just fine but the problem is it only gives me the first 2 days of updated records (and the session lasts more than that, specifically a week which is the difference between the backup jobs):

Code: Select all

>  $taskSession.Status
InProgress
> $logBackupLogs.Count
1000
> date
Wednesday, June 19, 2024 12:13:34 AM

> $logBackupLogs.updatetime | Select -Last 1
Saturday, June 15, 2024 5:31:03 PM

Any ideas why I am getting only the first 2 days of updated records?

Thanks

[david.domask]

Hi Juan, welcome to the forums.

First, I need to inform you that you're using unsupported methods for our Powershell module here, so the research will be light as we simply cannot get deep into the behaviors of unsupported methods (they may change or not be intended for use, etc)

Code: Select all

$Job = Get-VBRJob -name db
$SQLJob = $Job.FindChildSqlLogBackupJob()
$Session = [Veeam.Backup.Core.CBackupSession]::GetByJob($SQLJob.Id) | select -Last 1 ## The Working session
$taskSession = (Get-VBRTaskSession -Session $session)[1] ## Using the first one as I have 3 hosts here

2nd and 3rd line here are using unsupported methods, and you can workaround this to get the SQL log backup SessionID with Get-VBRSession:

Code: Select all

$tlogSess = Get-VBRSession -Type Sqllogbackup

The ID can then be passed to

Code: Select all

Get-VBRBackupSession -Id 

and you will get a normal SQL Backup Log Transaction session in a supported way.

As for the Logger.GetLog() call, this pulls the messages from the UI window, and I suppose there's probably just some limit internally on that session view, but I'm not sure it's worth getting into.

Is there a reason our VeeamOne report isn't suitable for monitoring the SQL Transaction Log backups? https://helpcenter.veeam.com/docs/one/r ... story.html

If you really need to go the powershell route and to get information on each object that was processed by the SQL Log Backup job, use the report, or from the Session returned by Get-VBRBackupSession, there is an unsupported method GetTaskSessions() which I believe is more informative for your reporting, as it should cover all objects in the SQL Log backup session.

[jroig]

Thanks David for your answer it really helped me.

I do need to get this information using PowerShell as I need to push this metrics to our monitoring system...

I tried using the supported way of getting the session:

Code: Select all

> $session = Get-VBRSession -Type Sqllogbackup | where { $_.State -eq "Working"}

> $session

Progress     : 0
RunManually  : False
Log          : {Id: 461390, Usn: 4395631, Title: Job started at 6/14/2024 7:00:35 PM, Cookie: , De
               6/14/2024 7:00:38 PM, StartTime: 6/14/2024 7:00:38 PM, Status: Succeeded, Id: 46139
               Title: Building VMs list, Cookie: , Description: , Time: 6/14/2024 7:00:40 PM, Star
               7:00:38 PM, Status: Succeeded, Id: 461392, Usn: 4395644, Title: Preparing log shipp
               , Description: , Time: 6/14/2024 7:00:43 PM, StartTime: 6/14/2024 7:00:41 PM, Statu
               461954, Usn: 4400547, Title: Building VMs list, Cookie: , Description: , Time: 6/14
               StartTime: 6/14/2024 7:54:22 PM, Status: Succeeded}
Name         : db SQL Server Transaction Log Backup
CreationTime : 6/14/2024 7:00:35 PM
EndTime      : 1/1/1900 12:00:00 AM
JobId        : cad3a7b9-e75d-47aa-bdc4-f0149982ab7a
Result       : None
State        : Working
Id           : 64a91603-1769-4b45-9dd6-fc82ddfb7c20

But the unsupported method you've mentioned does not seem to exist:

Code: Select all

PS C:\Users\jroig> $session.GetTaskSessions()
Method invocation failed because [Veeam.Backup.PowerShell.Infos.VBRBackupSession] does not contain
'GetTaskSessions'.
At line:1 char:1
+ $session.GetTaskSessions()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : MethodNotFound

Do you know why that does not work?
Thanks

[david.domask]

Hi Juan,

You're welcome for the assistance

It is a bit unexpected for me here, can you pipe $session to Get-Member (gm) and check the methods available? Doesn't it have one like GetTaskSessions()?

[jroig]

Thanks David, here is the info:

Code: Select all

> $session | Get-Member


   TypeName: Veeam.Backup.PowerShell.Infos.VBRBackupSession

Name         MemberType Definition
----         ---------- ----------
Equals       Method     bool Equals(System.Object obj)
GetHashCode  Method     int GetHashCode()
GetType      Method     type GetType()
ToString     Method     string ToString()
CreationTime Property   datetime CreationTime {get;}
EndTime      Property   System.Nullable[datetime] EndTime {get;}
Id           Property   guid Id {get;}
JobId        Property   guid JobId {get;}
Log          Property   Veeam.Backup.PowerShell.Infos.VBRLogItem[] Log {get;}
Name         Property   string Name {get;}
Progress     Property   int Progress {get;}
Result       Property   Veeam.Backup.PowerShell.Infos.VBRSessionResult Result {get;}
RunManually  Property   bool RunManually {get;}
State        Property   Veeam.Backup.PowerShell.Infos.VBRSessionState State {get;}

[david.domask]

> PowerShell.Infos.VBRBackupSession

That's why I guess I explained poorly, but take the ID from that object and pass it to Get-VBRBackupSession on the -Id parameter:

Code: Select all

PS C:\Users\david.LAB> $tlogsess = Get-VBRSession -Type SqlLogBackup
PS C:\Users\david.LAB> $tlogsess[0].GetType()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     False    VBRBackupSession                         Veeam.Backup.PowerShell.Infos.VBRSession


PS C:\Users\david.LAB> $realSess = Get-VBRBackupSession -Id $tlogsess[0].id
PS C:\Users\david.LAB> $realSess.GetType()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     False    CBackupSession                           Veeam.Backup.Core.CBaseSession

PS C:\Users\david.LAB> $realSess.GetType().GetMethods() | Where-Object {$_.Name -like "GetTaskSessions"}

Name                       : GetTaskSessions
DeclaringType              : Veeam.Backup.Core.CBackupSession
ReflectedType              : Veeam.Backup.Core.CBackupSession
MemberType                 : Method
MetadataToken              : 100763664
Module                     : Veeam.Backup.Core.dll
IsSecurityCritical         : True

The CBackupSession object returned by Get-VBRBackupSession has the unsupported method I'm discussing.

[jroig]

Thanks, sorry about that as I did not understand it correctly, I am able to get the task sessions now:

Code: Select all

> $realSess.GetTaskSessions()

Name                      Status         Operation
----                      ------         ---------
prod-fbn-db2              Pending
prod-fbn-db0              Pending
prod-fbn-db1              Pending

Status is Pending because they are currently running and syncing every 15 minutes, can I get the information of those syncs from here?

Thanks

[david.domask]

Hi Juan, no worries, your output was enough to see where the issue was, so glad we got it settled

As for checking further details, I have not explored these objects carefully, but I would unwrap the properties of the returned objects from GetTaskSessions() and see if you can find what you need. I'm not quite sure what you're looking for specifically, but just poke around the properties or use tools to expand all the properties in powershell and check what you have to work with. Sorry that these aren't more clearly documented, but we're in unsupported methods here as noted before, so it will be a bit of trial and error.

[JTT]

> PowerShell.Infos.VBRBackupSession

That's why I guess I explained poorly, but take the ID from that object and pass it to Get-VBRBackupSession on the -Id parameter:

Code: Select all

PS C:\Users\david.LAB> $tlogsess = Get-VBRSession -Type SqlLogBackup
PS C:\Users\david.LAB> $tlogsess[0].GetType()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     False    VBRBackupSession                         Veeam.Backup.PowerShell.Infos.VBRSession


PS C:\Users\david.LAB> $realSess = Get-VBRBackupSession -Id $tlogsess[0].id
PS C:\Users\david.LAB> $realSess.GetType()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     False    CBackupSession                           Veeam.Backup.Core.CBaseSession

PS C:\Users\david.LAB> $realSess.GetType().GetMethods() | Where-Object {$_.Name -like "GetTaskSessions"}

Name                       : GetTaskSessions
DeclaringType              : Veeam.Backup.Core.CBackupSession
ReflectedType              : Veeam.Backup.Core.CBackupSession
MemberType                 : Method
MetadataToken              : 100763664
Module                     : Veeam.Backup.Core.dll
IsSecurityCritical         : True

The CBackupSession object returned by Get-VBRBackupSession has the unsupported method I'm discussing.

Is it still unsupported even in latest v12.2?

[meelisn]

How could I get the job related to that backupsession? Both Get-VbrSession and Get-VbrBackupSession return object with JobId, but Get-VbrJob does not support searching by JobId and Get-VbrJob doesn't return that related job. Is there some other way to get related job?

[david.domask]

@JTT , the unsupported item was using the GetTaskSessions() method on the CBackupSession object returned by Get-VBRBackupSession. The supported way is to get a CBackupSession object with Get-VBRBackupSession and pass that to Get-VBRTaskSession on the -Session parameter.

@meelisn, What kind of job is it? You should be able to do something like:

$Sess = Get-VBRBackupSession -Name "some job name"
Get-VBRJob | Where-Object {$_.id -eq $Sess.JobId}

[JTT]

We have created also a Case # 07425934

[david.domask]

Hi JTT, I checked the case, and it looks like the attempts with Powershell are using the previously mentioned unsupported methods.

So you can use the methods mentioned above in the topic here for the reporting purposes.

Start with the method mentioned here. Pass the resulting session IDs from Get-VBRSession to Get-VBRBackupSession on the -id parameter, and that will return your SQL Log Backup Sessions. From there, you can do your reporting, or if you need more granular reporting, you can pass the resulting CBackupSession object returned by Get-VBRBackupSession to Get-VBRTaskSession on the -Session parameter.

$tlogSess = Get-VBRSession -Type Sqllogbackup
$fullsess = Get-VBRBackupSession -Id $tlogSess.id
$fullsess[0]| Get-VBRTaskSession

Name Status Operation
---- ------ ---------
Some-SQL-01-DB Pending

Fully supported and should return the objects you need for reporting.

[meelisn]

@meelisn, What kind of job is it? You should be able to do something like:

$Sess = Get-VBRBackupSession -Name "some job name"
Get-VBRJob | Where-Object {$_.id -eq $Sess.JobId}

I'm interested on getting SQL Log backup current status and connecting it to related job (for monitoring solution). And the problem is, that SQL log backup job is itself a related job to VM or Agent Backup job. and Get-VbrJob does not return these.

Also, with Get-VBRBackupSession output, there is problem, that latest backup session is always in Working state. And the state changes only when next session starts. This may take a day or more. Not very good for notifying admin about failed job.

[david.domask]

Hi meelisn,

Please check my post above in response to JTT for how to retrieve this information for SQL Log Backups.

>Also, with Get-VBRBackupSession output, there is problem, that latest backup session is always in Working state. And the state changes only when next session starts. This may take a day or more. Not very good for notifying admin about failed job.

Bolded part does not sound correct -- what type of job are you checking? Can you perhaps show your code + an example output?