[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 920
  • Last Modified:

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.
0
Omega002
Asked:
Omega002
  • 3
  • 3
1 Solution
 
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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

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