Omega002
asked on
SSRS Report For MSSQL 200 & 2005 Backup History
Does anyone has the t-sql syntanx I can use for an SSRS report for MSSQL 2000 & 2005 Backup History?
ASKER
I need something like this:
job name: FULLBKUP
startdate/time: 1/1/08
enddate/time: 1/5/08
status: failed or success
job name: FULLBKUP
startdate/time: 1/1/08
enddate/time: 1/5/08
status: failed or success
This select returns job history for ALL jobs:
SELECT name, start_time, DATEADD(second, duration_sec, start_time) AS end_time, run_status
FROM (
SELECT name,
CAST(LEFT(start_time, 4) + '-' + SUBSTRING(start_time, 5, 2) + '-' + SUBSTRING(start_time, 7, 2) + ' '
+ SUBSTRING(start_time, 9, 2) + ':' + SUBSTRING(start_time, 11, 2) + ':' + SUBSTRING(start_time, 13, 2) AS DATETIME) AS start_time,
duration_sec, run_status
FROM (
SELECT j.name, h.run_status, CAST(h.run_date AS VARCHAR(8)) + RIGHT('0000000' + CAST(h.run_time AS VARCHAR(6)), 6) AS start_time,
h.run_duration % 100 + ((h.run_duration/100) %100) * 60 + (h.run_duration/10000)*60*60 AS duration_sec
FROM msdb.dbo.sysjobhistory AS h INNER JOIN msdb.dbo.sysjobs AS j ON h.job_id = j.job_id
WHERE h.step_id = 0) AS x) AS xx
ORDER BY start_time
Maybe you can rely on job name to filter backup jobs only. In this case:
WHERE h.step_id = 0 AND j.name LIKE '%backup%'
WHERE h.step_id = 0 AND j.name LIKE '%backup%'
ASKER
how would you include the server name with this query?
Add originating_server column:
SELECT originating_server, name, start_time, DATEADD(second, duration_sec, start_time) AS end_time, run_status
FROM (
SELECT originating_server, name,
CAST(LEFT(start_time, 4) + '-' + SUBSTRING(start_time, 5, 2) + '-' + SUBSTRING(start_time, 7, 2) + ' '
+ SUBSTRING(start_time, 9, 2) + ':' + SUBSTRING(start_time, 11, 2) + ':' + SUBSTRING(start_time, 13, 2) AS DATETIME) AS start_time,
duration_sec, run_status
FROM (
SELECT j.name, j.originating_server, h.run_status,
CAST(h.run_date AS VARCHAR(8)) + RIGHT('0000000' + CAST(h.run_time AS VARCHAR(6)), 6) AS start_time,
h.run_duration % 100 + ((h.run_duration/100) %100) * 60 + (h.run_duration/10000)*60*60 AS duration_sec
FROM msdb.dbo.sysjobhistory AS h INNER JOIN msdb.dbo.sysjobs AS j ON h.job_id = j.job_id
WHERE h.step_id = 0 AND j.name LIKE '%backup%') AS x) AS xx
ORDER BY start_time
ASKER
The query produced this error:
Msg 207, Level 16, State 1, Line 8
Invalid column name 'originating_server'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'originating_server'.
It's different on 2005:
SELECT originating_server, name, start_time, DATEADD(second, duration_sec, start_time) AS end_time, run_status
FROM (
SELECT originating_server, name,
CAST(LEFT(start_time, 4) + '-' + SUBSTRING(start_time, 5, 2) + '-' + SUBSTRING(start_time, 7, 2) + ' '
+ SUBSTRING(start_time, 9, 2) + ':' + SUBSTRING(start_time, 11, 2) + ':' + SUBSTRING(start_time, 13, 2) AS DATETIME) AS start_time,
duration_sec, run_status
FROM (
SELECT j.name, s.srvname AS originating_server, h.run_status,
CAST(h.run_date AS VARCHAR(8)) + RIGHT('0000000' + CAST(h.run_time AS VARCHAR(6)), 6) AS start_time,
h.run_duration % 100 + ((h.run_duration/100) %100) * 60 + (h.run_duration/10000)*60*60 AS duration_sec
FROM msdb.dbo.sysjobhistory AS h INNER JOIN msdb.dbo.sysjobs AS j ON h.job_id = j.job_id
INNER JOIN master.dbo.sysservers AS s ON j.originating_server_id = s.srvid
WHERE h.step_id = 0 AND j.name LIKE '%backup%') AS x) AS xx
ORDER BY start_time
ASKER
so what would be the syntax for MSSQL 2000
Same as I stated before:
SELECT originating_server, name, start_time, DATEADD(second, duration_sec, start_time) AS end_time, run_status
FROM (
SELECT originating_server, name,
CAST(LEFT(start_time, 4) + '-' + SUBSTRING(start_time, 5, 2) + '-' + SUBSTRING(start_time, 7, 2) + ' '
+ SUBSTRING(start_time, 9, 2) + ':' + SUBSTRING(start_time, 11, 2) + ':' + SUBSTRING(start_time, 13, 2) AS DATETIME) AS start_time,
duration_sec, run_status
FROM (
SELECT j.name, j.originating_server, h.run_status,
CAST(h.run_date AS VARCHAR(8)) + RIGHT('0000000' + CAST(h.run_time AS VARCHAR(6)), 6) AS start_time,
h.run_duration % 100 + ((h.run_duration/100) %100) * 60 + (h.run_duration/10000)*60*60 AS duration_sec
FROM msdb.dbo.sysjobhistory AS h INNER JOIN msdb.dbo.sysjobs AS j ON h.job_id = j.job_id
WHERE h.step_id = 0 AND j.name LIKE '%backup%') AS x) AS xx
ORDER BY start_time
ASKER
can you include the syntax that would convert the status 1 to success or status 0 to failed?
ASKER
also how can I get an output of all the backup history going back 1 year with the current syntax you have for this query?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT backup_set_id, name, description, backup_start_date, backup_finish_date, type, backup_size, machine_name, database_name FROM msdb.dbo.backupset