Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a stored procedure that performs database backups. Because I'm looking at Availability Groups within SQL Server 2012 I want to utilise the *sys.fn_hadr_backup_is_preferred_replica* function within that script to test whether the script should produce the backup.

If the script is run manually instead of as part of a scheduled SQL Agent job, or if the SQL Agent job is executed manually rather than via a schedule, then I want the job to fail with an error message to allow the user to see that the backup has not succeeded due to not being on the preferred replica. If the job is run on a schedule then I obviously don't want it to fail as it would be producing errors and sending out alerts all day long on the server that is not the preferred replica.

The only bit that I don't know how to do is to check whether the job is being executed by a schedule or manually. Is this possible?

Failing that, is there any other way that I can alert a user that the script hasn't produced a backup, whilst not causing the scheduled task to fail and produce an alert?

share|improve this question

3 Answers 3

Here is a start for you.

SELECT j.name as jobname, 
    CASE ja.run_requested_source
        WHEN 1 THEN 'SOURCE_SCHEDULER'
        WHEN 2 THEN 'SOURCE_ALERTER'
        WHEN 3 THEN 'SOURCE_BOOT'
        WHEN 4 THEN 'SOURCE_USER'  -- This one is manual
        WHEN 6 THEN 'SOURCE_ON_IDLE_SCHEDULE'
        ELSE 'UNKNOWN' END AS job_source
FROM msdb.dbo.sysjobactivity ja
JOIN msdb.dbo.sysjobs j
    ON ja.job_id = j.job_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
AND stop_execution_date is null;

This should give you a list of all currently running jobs along with the source that started the job.

share|improve this answer
up vote 1 down vote accepted

Using Kenneth's code, and a few other bits and pieces that I pulled together, I came up with the following code which I think should do the job. If anybody sees any potential problem with this I'd be interested in knowing!

-- Check if this server is the preferred replica for backups.
    If sys.fn_hadr_backup_is_preferred_replica( @DatabaseName ) <> 1
    BEGIN

        -- Check whether the script is running from SQL Agent.  If it is not then raise an error so that the user knows that the backup hasn't completed.
        -- (If the job is run from the agent then there should be an equivalent job running on the preferred replica, so no need to alert).
        declare @AgentAccount varchar(100)
        set @AgentAccount = (select cast(value_data as varchar(100)) as [AccountName]
                                FROM sys.dm_server_registry
                                WHERE registry_key LIKE '%SQLSERVERAGENT%' --For named instance change to SQLAgent$<instanceName>
                                AND value_name = 'ObjectName'
                            )


        if @AgentAccount = SYSTEM_USER
        begin

            declare @jobSource tinyint
            SELECT  @jobSource = ja.run_requested_source
                    --WHEN 1 THEN 'SOURCE_SCHEDULER'
                    --WHEN 2 THEN 'SOURCE_ALERTER'
                    --WHEN 3 THEN 'SOURCE_BOOT'
                    --WHEN 4 THEN 'SOURCE_USER'  -- This one is manual
                    --WHEN 6 THEN 'SOURCE_ON_IDLE_SCHEDULE'
                    --ELSE 'UNKNOWN' END
            FROM msdb.dbo.sysjobactivity ja
            WHERE ja.session_id = @@SPID
            AND start_execution_date is not null
            AND stop_execution_date is null;

            if @jobSource = 4   -- Job was run manually.
            begin
                RAISERROR ('No backup produced as this server is not the preferred replica for backups.  Please run the script again from the preferred replica.', 16, 1)
                return 1
            end
            else
            begin
                print 'No backup produced as this server is not the preferred replica for backups.'
                return 0
            end
        end
        else
        begin
            RAISERROR ('No backup produced as this server is not the preferred replica for backups.  Please run the script again from the preferred replica.', 16, 1)
            return 1
        end
    END
share|improve this answer

If the script is run manually instead of as part of a scheduled SQL Agent job, or if the SQL Agent job is executed manually rather than via a schedule, then I want the job to fail with an error message to allow the user to see that the backup has not succeeded due to not being on the preferred replica. If the job is run on a schedule then I obviously don't want it to fail as it would be producing errors and sending out alerts all day long on the server that is not the preferred replica.

I don't see a need to over complicate it.

Just test it if it is a preferred replica or not and then take backup.

declare @DBNAME sysname
set @DBNAME = 'mydatabaseName'

IF  sys.fn_hadr_backup_is_preferred_replica(@DBNAME) <> 1
BEGIN
      print 'This is not the preferred replica, exiting with success';
      RETURN 0 -- This is a normal, expected condition, so the script returns success
END
else
begin
print 'The is the the preferred replica ... moving further ....'
BACKUP DATABASE @DBNAME TO DISK='D:\backups\dbname_full.bak'
   -- WITH COPY_ONLY
end
share|improve this answer
    
The reason for over-complicating it is that if a sysadmin goes on to the primary replica and runs the job to create a backup, then it will return success even though no backup has been created. This seems like an extremely bad idea as the sysadmin would then assume that a valid and current backup exists when it doesn't. –  paulH Aug 30 '13 at 15:41
    
@paulH sysadmin goes on to the preferred replica and runs the job to create a backup why would a sysadmin cant create backup. I would be only worried if someone takes a log backup. –  Kin Aug 30 '13 at 15:44
    
What I mean is, if a sysadmin logs on to the replica that is not the preferred replica and then runs the backup job, it will return a 'success' status but not produce a backup as it is not the preferred replica for backups. This could lead the sysadmin to mistakenly think there is a valid backup of the database and proceed to do something destructive to the database. Unless the sysadmin looked in the SQL Agent logs or actually went and verified that the backup file existed then they would not realise their mistake until it was too late. –  paulH Aug 30 '13 at 15:57
    
@paulH Understand your point ... If your backup job has the logic to check if the database is a preferred replica using sys.fn_hadr_backup_is_preferred_replica then it wont take backup at all -- sysadmin (SA or DBA) should be aware of that. If he takes it manually, then should be OK ... either way, better to make sure that a backup is there compared to we dont have backups at all. –  Kin Aug 30 '13 at 16:13
    
Idera and I'm sure Red Gate and probably several others have tools that will check your backups and make sure they are up to date. I would suggest either getting one of those or building a home grown one. That way the sysadmin doesn't have to worry about "Do I have a valid backup". He get's a report telling him one way or the other. –  Kenneth Fisher Aug 30 '13 at 20:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.