Veeam 8 How to find SQL transaction log backup job results

PowerShell script exchange

Veeam 8 How to find SQL transaction log backup job results

Veeam Logoby micwv8 » Wed Dec 03, 2014 3:54 am

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
Novice
 
Posts: 9
Liked: never
Joined: Thu Nov 13, 2014 2:51 am
Full Name: Michael Walkenhorst

Re: Veeam 8 How to find SQL transaction log backup job resul

Veeam Logoby micwv8 » Thu Dec 04, 2014 1:04 am

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.
micwv8
Novice
 
Posts: 9
Liked: never
Joined: Thu Nov 13, 2014 2:51 am
Full Name: Michael Walkenhorst

Re: Veeam 8 How to find SQL transaction log backup job resul

Veeam Logoby v.Eremin » Thu Dec 04, 2014 11:20 am

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.
v.Eremin
Veeam Software
 
Posts: 13266
Liked: 968 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: Veeam 8 How to find SQL transaction log backup job resul

Veeam Logoby micwv8 » Fri Dec 05, 2014 12:28 am

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.
micwv8
Novice
 
Posts: 9
Liked: never
Joined: Thu Nov 13, 2014 2:51 am
Full Name: Michael Walkenhorst

Re: Veeam 8 How to find SQL transaction log backup job resul

Veeam Logoby v.Eremin » Fri Dec 05, 2014 8:47 am

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.
v.Eremin
Veeam Software
 
Posts: 13266
Liked: 968 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: Veeam 8 How to find SQL transaction log backup job resul

Veeam Logoby micwv8 » Fri Dec 05, 2014 9:08 am

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.
micwv8
Novice
 
Posts: 9
Liked: never
Joined: Thu Nov 13, 2014 2:51 am
Full Name: Michael Walkenhorst

Re: Veeam 8 How to find SQL transaction log backup job resul

Veeam Logoby v.Eremin » Fri Dec 05, 2014 9:19 am

$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.
v.Eremin
Veeam Software
 
Posts: 13266
Liked: 968 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: Veeam 8 How to find SQL transaction log backup job resul

Veeam Logoby micwv8 » Mon Dec 08, 2014 3:11 am

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.
micwv8
Novice
 
Posts: 9
Liked: never
Joined: Thu Nov 13, 2014 2:51 am
Full Name: Michael Walkenhorst

Re: Veeam 8 How to find SQL transaction log backup job resul

Veeam Logoby v.Eremin » Mon Dec 08, 2014 8:14 pm

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.
v.Eremin
Veeam Software
 
Posts: 13266
Liked: 968 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: Veeam 8 How to find SQL transaction log backup job resul

Veeam Logoby micwv8 » Tue Dec 09, 2014 4:30 am

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
micwv8
Novice
 
Posts: 9
Liked: never
Joined: Thu Nov 13, 2014 2:51 am
Full Name: Michael Walkenhorst

Re: Veeam 8 How to find SQL transaction log backup job resul

Veeam Logoby v.Eremin » Tue Dec 09, 2014 8:11 pm

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.
v.Eremin
Veeam Software
 
Posts: 13266
Liked: 968 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: Veeam 8 How to find SQL transaction log backup job resul

Veeam Logoby cjg.swc » Mon Dec 15, 2014 9:29 pm

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).
cjg.swc
Lurker
 
Posts: 1
Liked: never
Joined: Mon Dec 15, 2014 9:09 pm


Return to PowerShell



Who is online

Users browsing this forum: No registered users and 4 guests