检查是否正在手动执行 SQL 代理作业

使用 Kenneth 的代码以及我整理的其他一些零碎的代码，我想出了以下代码，我认为它应该可以完成这项工作。如果有人对此有任何潜在问题，我会很想知道！

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

这是你的一个开始。

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;

这应该为您提供所有当前正在运行的作业的列表以及启动该作业的源。

如果脚本是手动运行而不是作为计划的 SQL 代理作业的一部分，或者如果 SQL 代理作业是手动执行而不是通过计划执行，那么我希望作业失败并显示错误消息以允许用户看到由于不在首选副本上，备份未成功。如果作业按计划运行，那么我显然不希望它失败，因为它会产生错误并整天在不是首选副本的服务器上发送警报。

我认为没有必要过度复杂化。

只需测试它是否是首选副本，然后进行备份。

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