SSRS Report For MSSQL 200 & 2005 Backup History

Something like this?
SELECT backup_set_id, name, description, backup_start_date, backup_finish_date, type, backup_size, machine_name, database_name FROM msdb.dbo.backupset

I need something like this:

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
	

Select allOpen in new window

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%'

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

Select allOpen in new window

The query produced this error:

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

Select allOpen in new window

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

Select allOpen in new window

can you include the syntax that would convert the status 1 to success or status 0 to failed?

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?