cancel
Showing results for 
Search instead for 
Did you mean: 

Report of MS-SQL-SERVER and your results

Kleber_Marra
Level 4
Partner Accredited Certified

Guys good morning,

I need to report to OpsCenter 8.2 for my manager of all MS-SQl-SERVER policy jobs showing
could the job result for each database help me?

Could you tell me which tables I need to build a report with the information below:

Server Name
SQL Database Name
Policy Name
Backup Start Date and Time
Backup End Date and Time
Backup Status (Successful or Unsuccessful)

Thanks.

8 REPLIES 8

sdo
Moderator
Moderator
Partner    VIP    Certified

There are many many examples of SQL queries here:

https://vox.veritas.com/t5/OpsCenter/bd-p/opscenter

...yet... I'm struggling to find a specific example of an "SQL query" for only "SQL policies", so I think you need to search the OpsCenter forum for examples of perhaps :  "SQL query for jobs of policy type" and that might give you some exampes of how to pick out backup jobs by a specific policy type.

Apologies, but I feel that perhaps I didn't quite understand the phrasing of your question... and so... if however, you meant to ask to "generate a list of all successful backups of individual databases and all failed backups of individual databases from within all SQL backup jobs", then I somehow suspect that OpsCenter might not be recording this in any meaningful way.  And so, if this is what you are after, then you many need to resort to custom scripting to pick your way through the embedded "tries" structure inside each record of a "bpdbjobs -all_columns" extract, and to then try to pick apart the output of the detailed activity monitor text.

Perhaps, another way might be to get your DBAs to implement some custom site specific code to get the SQL databases themselves to report their own recent history of backups of their own databases ?  i.e. MS SQL knows if and when it was backed-up, and MS SQL itself should be able to report on those backups.

Tape_Archived
Moderator
Moderator
   VIP   

If you are looking for the MSSQL job failures with database name then you can find that details in - C:\Program Files\Veritas\NetBackup\logs\mssql_backup_failures or /usr/openv/netbackup/logs/mssql_backup_failures directory. 

sdo
Moderator
Moderator
Partner    VIP    Certified

Is that log on client, media, or master - or all three?

Tape_Archived
Moderator
Moderator
   VIP   

Good catch, these logs are on master server.

Marianne
Moderator
Moderator
Partner    VIP    Accredited Certified

Wondering if @RiaanBadenhorst could possibly assist here? 

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

You can join these two tables on JobId

jobinst.PNGjobarch.PNG

 

Kleber_Marra
Level 4
Partner Accredited Certified

@RiaanBadenhorst ,

Thank you for your time, I will try to build the report taking into account what you indicated and then put the results here.

Best Regards,

Marra

Kleber_Marra
Level 4
Partner Accredited Certified

Hi @RiaanBadenhorsthow are you?

I'm running this query and is returning me with the start and end time totally different from OpsCenter itself, can you help me?

select domain_jobarchive.clientname as "Client"
,nb_jobdbinstancearchive.dbname as "Database"
,"DBA"."UTCBigintToUTCTime"(domain_jobarchive.starttime) as "Start Time"
,"DBA"."UTCBigintToUTCTime"(domain_jobarchive.endtime) as "End Time"
,domain_jobarchive.statuscode
from nb_jobdbinstancearchive
join domain_jobarchive
order by domain_jobarchive.endtime desc;

Best Regards

Marra