Verifique se o trabalho do SQL Agent está sendo executado manualmente

Usando o código de Kenneth e alguns outros pedaços que juntei, criei o seguinte código que acho que deve fazer o trabalho. Se alguém vir algum problema em potencial com isso, eu estaria interessado em saber!

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

Aqui está um começo para você.

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;

Isso deve fornecer uma lista de todos os trabalhos em execução no momento, juntamente com a origem que iniciou o trabalho.

Se o script for executado manualmente em vez de como parte de um trabalho agendado do SQL Agent, ou se o trabalho do SQL Agent for executado manualmente em vez de por meio de um agendamento, desejo que o trabalho falhe com uma mensagem de erro para permitir que o usuário veja que o backup não foi bem-sucedido por não estar na réplica preferencial. Se o trabalho for executado de acordo com um cronograma, obviamente não quero que ele falhe, pois produziria erros e enviaria alertas o dia todo no servidor que não é a réplica preferida.

Não vejo necessidade de complicar demais.

Apenas teste se é uma réplica preferida ou não e faça o 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