Querying Backup Exec's database created during BE install...

I am looking for a query that I can perform each day to determine the success of the backups from the night before.  I have 10 servers and do not want to have to have to check each individually.  Email notification doesn't always work, so that's not so reliable.  ExecView carries it's own problems (at least for me).

Any help would be appreciated.

Hank
paadminAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShogunWadeCommented:
Hi,   Backup exec has a stored proc that returns this data already..


ReportFailedJobs 'MyServerName',10    <---- last N number of days to report on
0
Saqib KhanSenior DeveloperCommented:
Ddi you try using OPERATORS?

You can Create an Operator and use NETSEND to target a PC which will receieve all notifications.

Good Luck.
0
Melih SARICAOwnerCommented:
or u can create an information Alert to add all BAckup Information to a Specific Database and to a specific table

and then u can check all UR BAckup history in one Table..

OR again

u can create an alert (010-Information All databases) and let it trigger an Job (on Advanced Tab of Alert creation Form) &#305;n this JOB u can do what ever u want..

Melih SARICA

here is an example that i Collect SQL Server Data BAckup informations
In My triggerd Job im sending Email About Everything happening

Its Just a JOB width Master DB and T-SQL and in T-SQL like
---- Start of TSQL
if [A-ERR] <> -1 begin DECLARE @msg nvarchar(4000) declare @JobName varchar(200) Set @msg = REPLACE("Error: [A-ERR]
Severity: [A-SEV]
Date: [STRTDT]
Time: [STRTTM]
Database: [A-DBN]
Message: [A-MSG] ", "'", "") set @JobName = 'Error no : [A-ERR] ' exec _SMTPMailSend @SenderName ='SQL Mail Agent ', @SenderAddress = 'melihsarica@m.com, @RecipientAddress = 'melihsarica@c.com, @Subject = @JobName, @Body = @msg end

---- End of TSQL

in this T-SQL
variables between [] r global variables filled when an alert is fired u can see detail explanation about these variables in BOL or MSDN

Melih SARICa

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

perfittlCommented:
Place the backup into a job then create this proc in the msdb database:


CREATE Proc JobName as

Exec sp_help_job null, 'JobName', 'job'

GO


This will give you lots of information!
0
ShogunWadeCommented:
Do you need any more assistance with this question ?
0
paadminAuthor Commented:
No, I suppose not, but none of the answers solve my problem
0
ShogunWadeCommented:
Did this not provide what you needed ?

ReportFailedJobs 'MyServerName',10
0
paadminAuthor Commented:
In my initial problem...I indicated I needed a query that showed me the status of 10 seperate backup jobs from the night before.  One query for ten servers, any status, not just failed.  So, no, it doesn't.
0
perfittlCommented:
You can use link server to connect to the 10 Servers then run the sp_help_job 10 times in  a proc. Each sp_help_job returns a recordset that could go into a temp table, having the Proc return the results.
0
ShogunWadeCommented:
perf,   you cant do that because Backup Exec doesnt use SQL Agent to perform its scheduling, etc.   It is a windows service based.
0
ShogunWadeCommented:
How about the

ReportOverNightSummary stored proc ?
0
paadminAuthor Commented:
PERFITTL,

I'm receiving an error message "Could not find stored procedure 'sp_help_job'."

0
perfittlCommented:
You need to run it from your msdb database.

this code will check the jobs from one server, but then you would need to build the results from all the servers then port the data to a single location.


DECLARE @is_sysadmin INT
DECLARE @job_owner sysname
DECLARE @job_id UNIQUEIDENTIFIER

Create table #t1(job_id varchar(100), LastRunDate varchar(10), LastRunTime varchar(10),
                              NextRunDate varchar(10), NextRunTime varchar(10), NextRunScheduleID int,
                              RequestedToRun int, RequestSource int, RequestSourceID varchar(10), Running int,
                        CurrentStep int, CurrentRetryAttempt int, State int)


SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = SUSER_SNAME()

SELECT @job_id = job_id FROM sysjobs WHERE name = 1st Job Name'

INSERT #t1
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @job_id

SELECT @job_id = job_id FROM sysjobs WHERE name = '2nd Job Name'

INSERT #t1
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @job_id
0
ShogunWadeCommented:
PERF,   as I said before

"perf,   you cant do that because Backup Exec doesnt use SQL Agent to perform its scheduling, etc.   It is a windows service based."

You are barking up the wrong tree!
0
paadminAuthor Commented:
Shogun:  when executing "ReportOverNightSummary" I receive no results.
0
ShogunWadeCommented:
Hmm.   ok,   some of these sp's are dependent on how backup exec is configured.  


Try this one, its less config specific


ReportDailyJobs 24    <--- the parameter here is the number of hours back from the current date & time for which to report.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
paadminAuthor Commented:
Shogun, still nothing...even tried ReportDailyJobs 48    
0
paadminAuthor Commented:
STRIKE THAT...I'M GETTING SOMETHING...LET ME CHECK IT OUT
0
ShogunWadeCommented:
I dont suppose you have backup exec  configured to flush the job history do you ?

Ive just tested this here and it works fine.
0
ShogunWadeCommented:
Cool.
0
paadminAuthor Commented:
Got the following back on one of my servers...I think I can work with this...is there a table that indicates what the FinalJobStatus number means?'

ActualStartTime                 2004-11-22 19:00:03.000      
ElapsedTimeSeconds          28799      
JobTypeID                         200      
TotalDataSizeKBytes           6.4246648E+7      
TotalRateMBMin                 371.0      
HasSkippedFiles                 1.0      
HasCorruptFiles                  0.0      
HasInuseFiles                     0.0      
TotalNumberOfFiles            349577.0      
TotalNumberOfDirectories   36968.0      
FinalJobStatus                   3      
JobName                           FULL BACKUP
0
ShogunWadeCommented:
Ill just have a look for it.   I cant remember whether this is in a table or its a frontend thing.

but 19 - Success (no Exceptions)  cos thats one of the checks i perform.
0
ShogunWadeCommented:
Hmm.   Ill keep searching but havent found it yet.    

however you might also want to look at

select * from dbo.vwJobHistorySummary     gives Last Error Message, tape used,  allsorts.
0
perfittlCommented:
Thats correct Sho, but if you run the backup from a job it WILL return the data into a table.
0
ShogunWadeCommented:
so long as the job is configured to log its history then yes it will.   But you also need to consider the maintenence job and make sure that that doesnt flush the job histories too quickly.

PS:  Still trying to find the enumeration of FinalJobStatus
0
paadminAuthor Commented:
I ended up using Crystal Reports, I created ODBC DSNs for each server, created one big report, that contained one subreport for each server.  I then ran the following select statement against vwJobHistorySummary view in each DB.

select TOP 1 OriginalStartTime, ActualStartTime, EndTime, ElapsedTimeSeconds, FinalJobStatus, JobLogFileName, TotalDataSizeBytes, TotalNumberOfFiles,TotalNumberOfDirectories, TotalSkippedFiles from vwJobHistorySummary
Where JobLogFileName LIKE '%C:\%'
order by EndTime Desc

Points go to ShogunWade...he pointed me in the right direction.

paadmin (Hank)

0
ShogunWadeCommented:
Suggest as paadmin say "Points go to ShogunWade...he pointed me in the right direction."
0
ShogunWadeCommented:
Glad i could help paad
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.