Veeam 8 How to find SQL transaction log backup job results

[micwv8]

Hi All,

I normally use Get-VBRBackupSession or Get-VBRJob to find information about or results from a Veeam backup job.
How to find SQL transaction log backup job results, as the above cmdlets do not reveal this info.

Thanks in advance.

Michael.
Case# 00693424

[micwv8]

Hi All,
The following cmdlet gives me the VSS job options/settings (which show SQL transaction log backup settings):
New-VBRJobVssOptions

It is listed in the Veeam 8 PowerShell document:
http://www.veeam.com/documentation-guid ... heets.html

Example 1
This command looks for the VSS settings of the first VM included in job named "Exchange Backup Copy". The job to look for the VM in is obtained with Get-VBRJob and piped down. The VM is obtained with Get-VBRJobObject by selecting the first VM in the job object list and piped down.
Get-VBRJob -Name "Exchange Backup Copy" | Get-VBRJobObject | Select -First 1 | Get-VBRJobObjectVssOptions

Example 2
This command looks for the VSS settings of the VM represented by the $vm variable. The VM is obtained with Get-VBRJobObject and assigned to the variable beforehand.
Get-VBRJobObjectVssOptions -ObjectInJob $vm


However, it does not show any job 'results'.

I really need a PowerShell way to find the SQL transaction log backup results.

Any ideas?

Thanks.

Michael.

[veremin]

Hi, Michael,

Can you try the following example and see whether it answers your requirements?

Code: Select all

$Job = Get-VBRJob -name "Name of your backup job"
$Job.FindChildSqlLogBackupJob()

Thanks.

[micwv8]

Hi,
The commands

$Job = Get-VBRJob -name "Name of your backup job"
$Job.FindChildSqlLogBackupJob()

appear to give me all the job options/configuration for the actual SQL log backup. It does not appear to give any job results (success/failure, size of data transferred, duration etc).

For a normal job, I can use Get-VBRBackupSession to get all the job results.

How to obtain SQL log backup job results?

Thanks.

Michael.

[veremin]

You can assign the gotten object to a variable and get access to its properties, including result, state, and general statistics. The following example should give you some insights:

Code: Select all

$Job = Get-VBRJob -name "Name of your backup job"
$SQLJob = $Job.FindChildSqlLogBackupJob()
$SQLJob.GetLastResult()
$LastSQLJobSession  = $SQLJob.FindLastSession()
$LastSQLJobSession.SessionInfo

Thanks.

[micwv8]

Still seems to show just generic info - nothing specific to a success or failure.

Here's what I see...

PS C:\temp> $Job = Get-VBRJob -name "MYJOB"
PS C:\temp> $SQLJob = $Job.FindChildSqlLogBackupJob()
PS C:\temp> $SQLJob.GetLastResult()
None
PS C:\temp> $LastSQLJobSession = $SQLJob.FindLastSession()
PS C:\temp> $LastSQLJobSession.SessionInfo


Failures : 0
Warnings : 0
BackedUpSize : 0
BackupTotalSize : 0
JobSourceType : VDDK
Progress : Veeam.Backup.Model.CBackupProgressData
CompletionPercentage : 0
IsRetryMode : False
IsActiveFullMode : False
IsFullMode : False
WillBeRetried : True
CurrentPointId : 00000000-0000-0000-0000-000000000000
JobAlgorithm : Incremental
SessionAlgorithm : Unknown
TransportType : Agent
Id : 1bed0333-b2d4-4c6c-a7c3-8ec03b5e52d3
JobId : fb62ab83-132b-4115-a36b-132eed504f55
JobType : SqlLogBackup
JobName : MYJOB SQL Backup
RunManually : False
CreationTime : 5/12/2014 2:18:27 PM
OriginalSessionId : 1bed0333-b2d4-4c6c-a7c3-8ec03b5e52d3
AuxData : <AuxData><CBackupStats><BackupSize>55228928</BackupSize><DataSize>23710415</DataSize><DedupRatio
>100</DedupRatio><CompressRatio>100</CompressRatio></CBackupStats><SessionStarterInfo
Version="1" UserName="SYSTEM" UserDomainName="NT AUTHORITY" RunManually="False" /></AuxData>
JobSpec :
Result : None
IsCompleted : False
Description :
Operation :
State : Working
EndTime : 1/01/1900 12:00:00 AM
LeaseId : 00000000-0000-0000-0000-000000000000


However when I look at SQL Statistics in the Veeam console I can see the job is succeeding every hour, along with how much time it took, the amount of data transferred etc

Any other thoughts how to get this info via PowerShell?

Thanks.

Michael.

[veremin]

$SQLJob.GetLastResult() shows the latest result of SQL log backup job, $LastSQLJobSession.SessionInfo.BackupTotalSize/1mb - the size of the latest log backup. In your case, it looks like the SQL backup job is either disabled or stopped, thus, you get None as the result and zero as the backup size. Thanks.

[micwv8]

Hi,
According to the SQL Statics in the Veeam B&R Console, I can see data size that is transferred each time (hourly) that the SQL transaction log backups complete.

The "Latest session" values (duration, read, transferred) are non-zero values in the console, but they are zero using the cmdlets provided above.

The values do not correlate.

Cheers,

Michael.

[veremin]

Just for the testing purpose can you create another SQL log backup job and check its parameters via the mentioned commandlets? I'm asking since in my case the code provided above worked smoothly. Thanks.

[micwv8]

SQL transaction log backups are now enabled on another job

I can see data size and duration in Veeam Console - all looks good. However, nothing has changed in the PowerShell side compared to the last attempt (by the way, the Veeam Support Engineer handling case 00693424 phoned me today to say, after talking with T3 level support, it is not possible to get this information via PowerShell).

PS C:\temp> $Job = Get-VBRJob -name "MYDEVJOB"
PS C:\temp> $SQLJob = $Job.FindChildSqlLogBackupJob()
PS C:\temp> $LastSQLJobSession = $SQLJob.FindLastSession()
PS C:\temp> $LastSQLJobSession.Info.Progress


TotalObjects : 0
ProcessedObjects : 0
ProcessedSize : 0
ProcessedUsedSize : 0
ReadSize : 92980224
ReadedAverageSize : 0
TransferedSize : 42579764
ProcessedDelta : 0
ProcessedUsedDelta : 0
ReadDelta : 0
ReadedAverageDelta : 0
TransferedDelta : 0
StartTime : 9/12/2014 10:03:20 AM
StopTime : 1/01/1900 12:00:00 AM
Percents : 0
AvgSpeed : 0
BottleneckInfo : Veeam.Backup.Model.CBottleneckInfo
TotalSize : 0
TotalUsedSize : 0
UsedSpaceRation : 1
TotalSizeDelta : 0
TotalUsedSizeDelta : 0
Duration : 02:20:23.3550549
IsNeedSubstractReadDelay : False



PS C:\temp> $LastSQLJobSession.Info.State
Working
PS C:\temp> $LastSQLJobSession


BottleneckManager : CJobBottleneckManager
Info : Veeam.Backup.Model.CBackupSessionInfo
Progress : Veeam.Backup.Model.CBackupProgressData
StartupMode : Normal
JobSourceType : VDDK
CurrentPointId : 00000000-0000-0000-0000-000000000000
OriginalSessionId : 35a1f7ab-cea3-4cad-ab35-a80acbd9706d
IsFullMode : False
IsRetryMode : False
IsQuickBackup : False
IsVeeamZip : False
IsPlannedFailover : False
IsReplicaFromBackup : False
IsAdHocBackup : False
PostActivity : AskService
Name : MYDEVJOB SQL Backup
OrigJobName : MYDEVJOB SQL Backup
BackupStats : Veeam.Backup.Model.CBackupStats
WorkDetails : Veeam.Backup.Core.CBackupSessionWorkDetails
WillBeRetried : True
IsManuallyStopped : False
IsTransformLaunched : False
SessionCryptoSpec : Veeam.Backup.Crypto.CCryptoSymmetricSpec
UserCryptoSpec :
SelectiveProcessingSpec :
StorageVerificationResult : Veeam.Backup.Core.CStorageVerificationResultContainer
IsEncryptionEnabled : False
SessionInfo : Veeam.Backup.Model.CBackupSessionInfo
Id : 35a1f7ab-cea3-4cad-ab35-a80acbd9706d
JobType : SqlLogBackup
JobName : MYDEVJOB SQL Backup
JobSpec :
JobTypeString : Other job type
Operation :
Description :
BaseProgress : 0
IsCompleted : False
IsWorking : True
IsStarting : False
IsPostprocessing : False
JobId : 11949099-0df4-48c2-aa58-3e9c6eb72aed
Result : None
State : Working
EndTime : 1/01/1900 12:00:00 AM
CreationTime : 9/12/2014 10:03:20 AM
AuxData : <AuxData><CBackupStats><BackupSize>124507648</BackupSize><DataSize>92994749</DataSize><Dedu
pRatio>100</DedupRatio><CompressRatio>100</CompressRatio></CBackupStats><SessionStarterInfo
Version="1" UserName="SYSTEM" UserDomainName="NT AUTHORITY" RunManually="False"
/></AuxData>
IsLowerAgentPriority : False
LogName : Job.MYDEVJOB_SQL_Backup
LogsSubFolder : MYDEVJOB_SQL_Backup
Logger : Veeam.Backup.Core.XmlLogger
Tracer : Veeam.Backup.Core.CSessionLogTracer

[veremin]

Hmm, I'm currently out of the office, and don't have a console at hand. Once I'm there, I will double check whether or not there is something in PS that might answer your requirements. Thanks.

[cjg.swc]

I would also be interested in an answer for this.

I tried digging through the methods/properties of the Job Session for the transaction log backup, but could never find a result value for the individual log backup tasks. I felt like I was getting close with this:

$SQLJob = Get-VBRJob -Name "SQL - With Log Backup"
$SQLLogJob = $SQLJob.FindChildSqlLogBackupJob()
$SQLLogJobLastSession = $SQLLogJob.FindLastSession()
$SQLLogJobLastSession.GetTaskSessions()

But I feel like I started going in circles a bit and could never get more detail than the overall Log Backup task. Meaning I could never get down to the individual results of the latest log backup, only the current status of the overall log backup job (which is usually just pending while it waits for the timer to expire).