PowerShell command to see the status from the SQL server tra

[Leahcim]

Hi @all,
if anybody know how I can seee the status from the SQL transaction logs ? If the transaction logs fail, if they DB is potected and so one ?
I become enough informations about main backup job, but I can't find nothing about the child job which is in the background and run every one hour. If everyone have an idea how I can reach informations from the child job ?


mfg
Lehcim

[veremin]

May I ask you one question before we proceed to actual scripting? Do you happen to have Veeam ONE deployed? If so, isn't SQL Backup Job Historical Information report something that you're after?

[Leahcim]

Yes i have installed the Veeam One Reporter and I know the report for Microsoft SQL server. But I will create my own report, because in my mind the report from Veeam one is confusing and the second reason is that this report not include the Oracle server, that I need too.


BR
Leahcim

[veremin]

Example provided below should be a good starting point:

Code: Select all

asnp VeeamPSSnapin
#SQL
$SQLJob  = Get-VBRJob -Name "Name of your SQL Backup"
$SQLJob = $Job.FindChildSqlLogBackupJob()
$SQLJob.GetLastResult()
$SQLJob.GetLastState()

#Oracle
$OracleJob = Get-VBRJob -Name "Name of your Oracle backup Job"
$OracleJob = $OracleJob.FindChildOracleLogBackupJob()
$OracleJob.GetLastResult()
$OracleJob.GetLastState()

Thanks.

[Leahcim]

Hi, thank you for the script. I can see many entries about the Oracle job. But what I need if the information if the Database is protected or not like the report from Veeam Backup and replication:

http://fs5.directupload.net/images/170601/xponoo6y.jpg

With your script the informations for

$OracleJob.GetLastResult() are None
$OracleJob.GetLastState() are Working

but I can't find any information about the status. Any idea ?

Regards
Leahcim

[veremin]

You can try to load assembly (not an obvious way) and get the required data from there. The following script will find the latest completed (it is important) session and query its state:

Code: Select all

$Job = Get-VBRJob -name "Name of backup job"
$SQLJob = $Job.FindChildSqlLogBackupJob()
$Session = [Veeam.Backup.Core.CBackupSession]::GetByJob($SQLJob.Id) | where {$_.IsCompleted -eq $True} | sort creationtimeutc -Descending | select -Last 1
$Session.Result

Thanks.

[aschillling]

I'm trying to get similar information. Down to the individual run of the each transaction log backup. I may have 15 database servers in the single parent job then 5-20 databases on each server. I would like to send the DBAs a list of transaction log failures so they can work out any issues.

[veremin]

Don't have a SQL transaction job at hand, but you can try to get backup session first (my previous post). Then, using the backup session, get task sessions (Get-VBRTaskSession), and try to get the needed information from them. Thanks.

[eubenitez]

Hello,

I tried with the following commands

$Job = Get-VBRJob -name "My Job Name"
$SQLJob = $Job.FindChildSqlLogBackupJob()
$Session = [Veeam.Backup.Core.CBackupSession]::GetByJob($SQLJob.Id) | where {$_.IsCompleted -eq $True} | sort creationtimeutc -Descending | select -Last 1
$Session.Result

But when I look inside $Job I can see the name of my job but when I launch the second command $SQLJob = $Job.FindChildSqlLogBackupJob() I get an error due to $Job.FindChildSqlLogBackupJob() returns NULL. How is this possible? I can see in veeam console the transactional logs running.

Thanks in advance

[david.domask]

Hi @eubenitez,

I just tested this in lab and for Vmware/HyperV jobs it works; is it possible you are trying to do this for Agent jobs?

Can you show your shell output in full?

[eubenitez]

Hello David,

Yes, the sql transactions logs jobs I'm traying to monitoring belong to Agents jobs?.
It's still possible to monitor them with Powershell?

[Obie]

Hi,

I use VBR 12 and I try to get results from a particular job. In this job I use "application-aware processing" with "Oracle Redo Log Backup". I try to catch the results of Oracle backup with powershell but I get the "success status" of the main job (vm backup) while Oracle is in failed status.
I use Get-VBRBackupSession but maybe there is an other method to use.

I don't have veeam agent on the vm.

Thanks for the help

[Mildur]

Hi Obie

Please try the suggestions from this topic.
Let us know if the command still works.
post244605.html#p244605

Best,
Fabian

[Obie]

Hi,

it don't works for me with this :

Code: Select all

$Job = Get-VBRJob -name "Test_Oracle"
$SQLJob = $Job.FindChildSqlLogBackupJob()
$Session = [Veeam.Backup.Core.CBackupSession]::GetByJob($SQLJob.Id) | where {$_.IsCompleted -eq $True} | sort creationtimeutc -Descending | select -Last 1
$Session.Result

[Mildur]

Hi

For Oracle, please try:

Code: Select all

$Job = Get-VBRJob -name "Test_Oracle"
$OracleJob = $Job.FindChildOracleLogBackupJob()
$Session = [Veeam.Backup.Core.CBackupSession]::GetByJob($OracleJob.Id) | where {$_.IsCompleted -eq $True} | sort creationtimeutc -Descending | select -Last 1
$Session.Result

Best,
Fabian

[Obie]

Nice, I get the result of Oracle failed job. Is there a way to get the vmname associated with the failed status only ?