PowerShell script exchange
Post Reply
Leahcim
Influencer
Posts: 12
Liked: never
Joined: Feb 07, 2017 10:31 am
Full Name: Michael fenske
Contact:

PowerShell command to see the status from the SQL server tra

Post by 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
Product Manager
Posts: 20271
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: PowerShell command to see the status from the SQL server

Post by veremin » 1 person likes this post

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
Influencer
Posts: 12
Liked: never
Joined: Feb 07, 2017 10:31 am
Full Name: Michael fenske
Contact:

Re: PowerShell command to see the status from the SQL server

Post by 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
Product Manager
Posts: 20271
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: PowerShell command to see the status from the SQL server

Post by 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
Influencer
Posts: 12
Liked: never
Joined: Feb 07, 2017 10:31 am
Full Name: Michael fenske
Contact:

Re: PowerShell command to see the status from the SQL server

Post by 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
Product Manager
Posts: 20271
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: PowerShell command to see the status from the SQL server

Post by 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
Novice
Posts: 9
Liked: never
Joined: Sep 15, 2017 7:52 pm
Full Name: Andrew W Schilling
Contact:

Re: PowerShell command to see the status from the SQL server

Post by 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
Product Manager
Posts: 20271
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: PowerShell command to see the status from the SQL server

Post by 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
Novice
Posts: 4
Liked: never
Joined: Oct 31, 2022 9:01 am
Full Name: Eubenitez
Contact:

Re: PowerShell command to see the status from the SQL server tra

Post by 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
Veeam Software
Posts: 1034
Liked: 278 times
Joined: Jun 28, 2016 12:12 pm
Contact:

Re: PowerShell command to see the status from the SQL server tra

Post by 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?
David Domask | Director: Customer Care | Veeam Technical Support
eubenitez
Novice
Posts: 4
Liked: never
Joined: Oct 31, 2022 9:01 am
Full Name: Eubenitez
Contact:

Re: PowerShell command to see the status from the SQL server tra

Post by 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
Novice
Posts: 4
Liked: never
Joined: Mar 21, 2023 12:35 pm
Contact:

[MERGED] Backup Job check for application-aware processing

Post by 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
Product Manager
Posts: 8549
Liked: 2223 times
Joined: May 13, 2017 4:51 pm
Full Name: Fabian K.
Location: Switzerland
Contact:

Re: PowerShell command to see the status from the SQL server tra

Post by Mildur »

Hi Obie

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

Best,
Fabian
Product Management Analyst @ Veeam Software
Obie
Novice
Posts: 4
Liked: never
Joined: Mar 21, 2023 12:35 pm
Contact:

Re: PowerShell command to see the status from the SQL server tra

Post by 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
Product Manager
Posts: 8549
Liked: 2223 times
Joined: May 13, 2017 4:51 pm
Full Name: Fabian K.
Location: Switzerland
Contact:

Re: PowerShell command to see the status from the SQL server tra

Post by Mildur » 1 person likes this post

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
Product Management Analyst @ Veeam Software
Obie
Novice
Posts: 4
Liked: never
Joined: Mar 21, 2023 12:35 pm
Contact:

Re: PowerShell command to see the status from the SQL server tra

Post by Obie »

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

Who is online

Users browsing this forum: No registered users and 21 guests