Advanced T-SQL Question: Producing Backup Window Timeline Chart

I'm an Oracle DBA who inherited 100 or so SQL Databases. I know how I
would accomplish this task in Oracle but I have no clue how to do it in
T-SQL. In short, I want to produce a comma delimited gantt type
timeline chart of my Backup Windows for import into Excel.
I'm not sure if this is the best place to get this information but I
found the msdb sysjobservers table which contains information about the
last run and duration of a job. It might be nice to get historical
information but I'm not sure where I'd find it. If there is a better
place to get this information then I'd like to know.
Here is the data I'm looking at...
use msdb
go
select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
s.last_run_duration
from sysjobs j, sysjobservers s
where j.job_id = s.job_id
go
name last_run_outcome last_run_date last_run_time
last_run_duration
-- -- -- --
--
DB BACKUP1 1 20060406 200000 1958
DB BACKUP2 1 20060401 10000 5943
DB BACKUP3 1 20060402 30000 31934
(3 row(s) affected)
If I understand this correctly, the data shows backup1 last ran at
20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
to kick out a comma delimited timeline showing the duration of the
backup. The first field is of course the name. The remaining 24
fields corrospond to the hours of the day 0 through 23.
So using the above data my output would look like this...
DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
BACKUP1 ran at 20:00 for less than an hour so there is one X in the
field corrosponding to the 20:00 hour showing the timeblock where the
backup ran.
BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
where the backup ran.
BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
fields corrosponding to the 3:00 through 11:00 hours showing the
timeblock where the backup ran.
I would greatly appreciate it if someone could help me with the T-SQL
to produce such a report.
Thanks in advance,
Roger WestbrookThis conversation is ongoing in the .programming group.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Kael_Sidhe@.yahoo.com> wrote in message news:1144417913.196803.81210@.t31g2000cwb.googlegroups.com...
> I'm an Oracle DBA who inherited 100 or so SQL Databases. I know how I
> would accomplish this task in Oracle but I have no clue how to do it in
> T-SQL. In short, I want to produce a comma delimited gantt type
> timeline chart of my Backup Windows for import into Excel.
> I'm not sure if this is the best place to get this information but I
> found the msdb sysjobservers table which contains information about the
> last run and duration of a job. It might be nice to get historical
> information but I'm not sure where I'd find it. If there is a better
> place to get this information then I'd like to know.
> Here is the data I'm looking at...
> use msdb
> go
> select j.name, s.last_run_outcome, s.last_run_date, s.last_run_time,
> s.last_run_duration
> from sysjobs j, sysjobservers s
> where j.job_id = s.job_id
> go
> name last_run_outcome last_run_date last_run_time
> last_run_duration
> -- -- -- --
> --
> DB BACKUP1 1 20060406 200000 1958
> DB BACKUP2 1 20060401 10000 5943
> DB BACKUP3 1 20060402 30000 31934
> (3 row(s) affected)
> If I understand this correctly, the data shows backup1 last ran at
> 20:00 and ran for 1958 seconds or approx 33 minutes rounded up. I want
> to kick out a comma delimited timeline showing the duration of the
> backup. The first field is of course the name. The remaining 24
> fields corrospond to the hours of the day 0 through 23.
> So using the above data my output would look like this...
> DB BACKUP1,,,,,,,,,,,,,,,,,,,,,X,,,
> DB BACKUP2,,X,X,,,,,,,,,,,,,,,,,,,,,
> DB BACKUP3,,,,X,X,X,X,X,X,X,X,X,,,,,,,,,,,,
> BACKUP1 ran at 20:00 for less than an hour so there is one X in the
> field corrosponding to the 20:00 hour showing the timeblock where the
> backup ran.
> BACKUP2 ran at 1:00 for less than 2 hours so there are X's in the
> fields corrosponding to the 1:00 and 2:00 hours showing the timeblock
> where the backup ran.
> BACKUP3 ran at 3:00 for less than 9 hours so there are X's in the
> fields corrosponding to the 3:00 through 11:00 hours showing the
> timeblock where the backup ran.
>
> I would greatly appreciate it if someone could help me with the T-SQL
> to produce such a report.
> Thanks in advance,
> Roger Westbrook
>