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?
Omega002Asked:
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.

RimvisCommented:
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
0
Omega002Author Commented:
I need something like this:

job name: FULLBKUP
startdate/time: 1/1/08
enddate/time: 1/5/08
status: failed or success
0
RimvisCommented:
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
	

Open in new window

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!

RimvisCommented:
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%'
0
Omega002Author Commented:
how would you include the server name with this query?
0
RimvisCommented:
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

Open in new window

0
Omega002Author Commented:
The query produced this error:

Msg 207, Level 16, State 1, Line 8
Invalid column name 'originating_server'.
0
RimvisCommented:
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

Open in new window

0
Omega002Author Commented:
so what would be the syntax for MSSQL 2000
0
RimvisCommented:
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

Open in new window

0
Omega002Author Commented:
can you include the syntax that would convert the status 1 to success or status 0 to failed?
0
Omega002Author Commented:
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?
0
RimvisCommented:

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, CASE h.run_status WHEN 0 THEN 'FAILED' ELSE 'SUCCESS' END AS 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
WHERE DATEADD(year, -1, GETDATE())<=start_time
ORDER BY start_time

Open in new window

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
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 2005

From novice to tech pro — start learning today.