• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 584
  • Last Modified:

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?
0
Omega002
Asked:
Omega002
  • 7
  • 6
1 Solution
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now