BAckup job details of last one week

Question

BAckup job details of last one week

smileswithharish86

Old Hand

Points: 395
More actions
November 11, 2011 at 10:08 am

#246051

can any one provide me the query to fetch the below information
Obtain the following information:
list of backup jobs (full vs Differential/ sqlsafe vs native)
Time the jobs run
What drives are used
View the history of the job and give % of failures in the last week.
Thanks
DBA

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

MSDB - sysjobs, sysjobhistory and the sysbackup* tables.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Have you looked in the scripts section of SSC?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Here is a resource for instance that could be helpful.

http://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/

There is also this one:

http://blog.sqlauthority.com/2010/11/10/sql-server-get-database-backup-history-for-a-single-database/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

smileswithharish86 Old Hand Points: 395 More actions · Answer 4

smileswithharish86

Old Hand

Points: 395

November 11, 2011 at 12:30 pm

#1406552

yes but i couldn't get

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

smileswithharish86 (11/11/2011)
yes but i couldn't get

couldn't get what?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

smileswithharish86 Old Hand Points: 395 More actions · Answer 6

smileswithharish86

Old Hand

Points: 395

November 11, 2011 at 1:04 pm

#1406574

any script related to this

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

smileswithharish86 (11/11/2011)
any script related to this

K, did you look at the scripts in the links I provided?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

smileswithharish86 Old Hand Points: 395 More actions · Answer 8

yup that is really good but it's giving the information about database backup

i need the success and failure details of backup jobs

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

have you queried the tables that Gail provided?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

smileswithharish86 Old Hand Points: 395 More actions · Answer 10

ya i am sending you the query

use msdb

select j.name ,

j.description location,

--h.run_date

CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,

case h.run_status when 0 then 'failed'

when 1 then 'Succeded'

when 2 then 'Retry'

when 3 then 'Cancelled'

when 4 then 'In Progress'

end as ExecutionStatus

from sysjobs j inner join sysjobhistory h on h.job_id=j.job_id

where name like'%backup%' and name not like '%user trans%' and enabled=1

and h.run_date > convert(getdate()-7

but i have 2 problems in my query

1)not able to map the drive where backups are placing by the job.

2)scheduled information like" occurs once on monday"

can you please let me know , if you have a way to map these

smileswithharish86 Old Hand Points: 395 More actions · Answer 11

yes and also find the query

use msdb

select j.name ,

j.description location,

--h.run_date

CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,

case h.run_status when 0 then 'failed'

when 1 then 'Succeded'

when 2 then 'Retry'

when 3 then 'Cancelled'

when 4 then 'In Progress'

end as ExecutionStatus

from sysjobs j inner join sysjobhistory h on h.job_id=j.job_id

where name like'%backup%' and name not like '%user trans%' and enabled=1

and h.run_date > convert(getdate()-7

here i have 2 prob

1)i am not able to map the disk where the backup job are being generated by the job --

2)i need schedule details like "occurs once on monday at sometime"

can you please let me know, if you know any way to do this

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

the sysbackup* tables (there are a few of them), sysscheduled (hard to decode, not well documented)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 13

Backup path can be found from msdb.dbo.backupmediafamily.

For Schedule related info refer to

http://solihinho.wordpress.com/2009/01/01/query-for-listing-sql-server-job-schedule/

Requires you to create 3 functions on master database

Regards,
Raj

http://Strictlysql.blogspot.com