A query the polls backup history for SQL Server 200 & 2005 Database Servers?

Does any have a scripts that I can use to poll the backup history for 10 or more SQL Server 2000 & 2005 database servers? I am trying to create a global report on the backup history for all SQL DB servers 2000 & 2005 in our environment.
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.

chapmandewCommented:
If you are trying to do it for different servers I would gather the results of this query for all of the servers and store them into a table on one server:


select * from backupfile bf
join backupset bs on bf.backup_set_id = bs.backup_set_id
0
chapmandewCommented:
Sorry..pull the data from the msdb database.
0
Omega002Author Commented:
How would I just pull the job name,status on whether it failed or succeeded and display time started and time finished?
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!

chapmandewCommented:
Oh, so you are just talking about the backup jobs....No problem, create this procedure to pull the data.



you can call this like this:

EXECUTE sp_Jobs
CREATE PROCEDURE [dbo].[sp_Jobs]
(
	@JobName VARCHAR(255)=NULL
)
AS
BEGIN
	IF OBJECT_ID('tempdb..#Results')>0
		DROP TABLE #Results
	
	CREATE TABLE #Results 
	(
		job_id                	UNIQUEIDENTIFIER NOT NULL,
		last_run_date         	INT              NOT NULL,
		last_run_time         	INT              NOT NULL,
		next_run_date         	INT              NOT NULL,
		next_run_time         	INT              NOT NULL,
		next_run_schedule_id  	INT              NOT NULL,
		requested_to_run      	INT              NOT NULL,
		request_source        	INT              NOT NULL,
		request_source_id     	SYSNAME   COLLATE DATABASE_DEFAULT NULL,
		running               	INT              NOT NULL,
		current_step          	INT              NOT NULL,
		current_retry_attempt 	INT              NOT NULL,
		job_state             	INT              NOT NULL
	)
	
	DECLARE @JobID VARCHAR(100)
	SELECT TOP 1 @JobID = job_ID FROM msdb.dbo.sysjobs
	
	INSERT INTO #Results
	EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @JobID
	
	SELECT 
		s.Name,
		CASE WHEN s.enabled = 0 THEN 'No' ELSE 'Yes' END AS Enabled,
		CASE WHEN next_run_date > 0 THEN 'Yes' ELSE 'No' END AS Scheduled,
		sc.name AS Category,
		current_step AS CurrentExecutionStep,
		msdb.dbo.fn_GetJobDate(last_run_date,last_run_time) AS LastRunDate,
		msdb.dbo.fn_GetJobDate(next_run_date, next_run_time) AS NextRunDate,
		CASE WHEN xp.running = 0 THEN 'Not Running' ELSE 'Executing...' END AS Status,
		ISNULL((
			SELECT CASE WHEN run_status = 1 THEN 'Succeeded' WHEN run_status = 3 THEN 'Cancelled' WHEN run_status = 0 THEN 'Failed' WHEN run_status IS NULL THEN 'Unknown' END AS LastRunStatus
			FROM 
				msdb..sysjobhistory sho
			WHERE 
				sho.job_id = xp.job_id AND 
				sho.instance_id =
				(
					SELECT MAX(instance_id) 
					FROM msdb..sysjobhistory sj (NOLOCK) 
					WHERE sj.job_id = sho.job_id
				)
		) ,'Unknown') AS LastRunStatus
	FROM 	#Results xp 	
		INNER JOIN msdb..sysjobs s on xp.job_id = s.job_id
		INNER JOIN msdb..syscategories sc on s.category_id = sc.category_id
	WHERE
		s.Name = ISNULL(@JobName, s.Name)
	ORDER BY s.Name
 
 
	IF @JobName IS NOT NULL
	BEGIN
		CREATE TABLE #JobHistory
		(
			StepID	INT	,
			StepName	SYSNAME	,
			Message	NVARCHAR(1024)	,
			RunStatus	INT	,
			RunDate	INT	,
			RunTime	INT	,
			RunDuration	INT	,
			operator_emailed	NVARCHAR(20)	,
			operator_netsent	NVARCHAR(20)	,
			operator_paged	NVARCHAR(20)				
		)
		INSERT INTO #JobHistory
		SELECT 
			sjh.step_id,
			sjh.step_name,
			sjh.message,
			sjh.run_status,
			sjh.run_date,
			sjh.run_time,
			sjh.run_duration,
			operator_emailed = so1.name,
			operator_netsent = so2.name,
			operator_paged = so3.name
		FROM 
			msdb.dbo.sysjobhistory                sjh
			JOIN msdb.dbo.sysjobs sjj ON sjh.job_id = sjj.job_id
			LEFT OUTER JOIN msdb.dbo.sysoperators so1  ON (sjh.operator_id_emailed = so1.id)
			LEFT OUTER JOIN msdb.dbo.sysoperators so2  ON (sjh.operator_id_netsent = so2.id)
			LEFT OUTER JOIN msdb.dbo.sysoperators so3  ON (sjh.operator_id_paged = so3.id),
			msdb.dbo.sysjobs                 sj
		WHERE 
		sjj.Name = @JobName and
		(sj.job_id = sjh.job_id)
 
		SELECT 
			StepID, StepName, Message, msdb.dbo.fn_GetJobDate(RunDate, RunTime) AS LastRunTime, 
			CASE RunStatus 
				WHEN 0 THEN 'Failed'
				WHEN 1 THEN 'Succeeded'
				WHEN 2 THEN 'Retry (step only)'
				WHEN 3 THEN 'Canceled'
				WHEN 4 THEN 'In-progress message'
				WHEN 5 THEN 'Unknown'
			END AS RunStatus
		FROM #JobHistory
		ORDER BY LastRunTime DESC, StepID ASC
	END
 
END

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
Omega002Author Commented:
So every time I execute this procedure ts will delete all records in the target table then append new data? I executed this procedure and it generated errors. Will this work for SQL Server 2000 & 2005? Here is the error listed below:

(8 row(s) affected)

Server: Msg 208, Level 16, State 1, Procedure sp_Jobs, Line 33
Invalid object name 'msdb.dbo.fn_GetJobDate'.
Server: Msg 208, Level 16, State 1, Procedure sp_Jobs, Line 33
Invalid object name 'msdb.dbo.fn_GetJobDate'.
0
Omega002Author Commented:
Do you have a query that I can use to populate a table with the same information then I can create a job to export the results then delete the temporary data. .
0
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.