Check to see if a backup is running on a database

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 1

One solution is to check if one of the scheduled jobs are running!

Suppose one can also look at all the DBCC INPUTBUFFER for the relevant database and check for the sp or a specific BACKUP/DUMP command

growl Hall of Fame Points: 3068 More actions · Answer 2

Hall of Fame

Points: 3068

August 6, 2003 at 7:08 pm

How could I do this?

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 3

-- Need some more work, maybe function, etc.

Set nocount on

Declare @Job_ID UNIQUEIDENTIFIER

DECLARE @is_sysadmin INT

DECLARE @job_owner sysname

Select @JOB_ID=JOB_ID from sysjobs

Where Name='TEST'

IF NOT (@JOB_ID IS NULL)

Begin

CREATE TABLE #xp_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 NULL,

running INT NOT NULL, current_step INT NOT NULL,current_retry_attempt INT NOT NULL,job_state INT NOT NULL)

SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)

--Change to something else if you know the owner

SELECT @job_owner = SUSER_SNAME()

INSERT INTO #xp_results

EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

Set nocount off

--1 Executing.

--2 Waiting for thread.

--3 Between retries.

--4 Idle.

--5 Suspended.

--7 Performing completion actions.

SELECT 'Answer ',job_state

FROM #xp_results

WHERE job_id = @JOB_ID

Drop table #xp_results

End

Set nocount off

--#####################################

-- To test against system proc

exec msdb..sp_help_Job @JOB_NAME='TEST'

--#####################################