SQL Server代理作业中PowerShell任务的SQLCMD错误处理问题

问题原因 #

你已经给sqlcmd加了-b参数（执行失败时返回非零退出码），但PowerShell默认不会将外部命令的非零退出码视为终止错误，所以try/catch块无法捕获该错误；同时SQL Server代理仅通过脚本的最终退出码判断作业是否成功，只要脚本没有返回非零值，就标记为成功。

解决方案 #

1. 手动检查SQLCMD的退出码 #

执行完sqlcmd后，立即检查$LASTEXITCODE变量（存储外部命令的退出码），如果值不为0，主动抛出终止错误或直接退出脚本并返回非零码。

2. 确保脚本最终返回非零退出码 #

在catch块中处理完清理逻辑后，使用exit 1（或其他非零值）让SQL Agent识别作业失败。

修改后的脚本示例 #

#get database connections
$Query = "select value from rubrikAPItoken where name = 'token'"
$token = Invoke-SqlCmd -ServerInstance servername.internal.mcmcg.com -Database dba_stats -Query $Query

# Connect to Rubrik
function connectTo-Rubrik(){
    return Connect-Rubrik -Server rubserver -Token $token.value
}

# Start of script
Import-Module Rubrik -Verbose

# Connect to Rubrik
$connection = connectTo-Rubrik

# Select Managed Volume
$ManagedVolume = Get-RubrikManagedVolume -Name servername-full-mv -Verbose

# Check if Drive is not writeable
if(!$ManagedVolume.isWritable){
    # If not writeable, enable writing
    $ManagedVolume | Start-RubrikManagedVolumeSnapshot -Verbose
}

###################### Run MSSQL Backup ######################

try {
    # 执行SQLCMD
    sqlcmd -E -S servername -d DBA_STATS -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'SYSTEM_DATABASES', @Directory = N'\\10.110.123.227\servername-sys-mv_channel0_29fe2c98,\\10.110.123.241\servername-sys-mv_channel1_29fe2c98,\\10.110.123.223\servername-sys-mv_channel2_29fe2c98,\\10.110.123.230\servername-sys-mv_channel3_29fe2c98', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 240, @Compress='Y', @CheckSum = 'Y'" -b    
    
    # 检查SQLCMD退出码，非零则抛出错误
    if ($LASTEXITCODE -ne 0) {
        throw "SQLCMD执行失败，退出码：$LASTEXITCODE"
    }

    ##############################################################

    # Check to see if connection is available
    if(([datetime]$connection.value.datetime).AddMinutes(15) -le (Get-Date)){
        $connection = connectTo-Rubrik
    }

    # Stop Snapshot
    $ManagedVolume | Stop-RubrikManagedVolumeSnapshot -Verbose

    # Disconnect Rubrik
    Disconnect-Rubrik
}
catch{
    # 输出错误信息（可选，便于排查）
    Write-Error $_

    # Check to see if connection is available
    if(([datetime]$connection.value.datetime).AddMinutes(15) -le (Get-Date)){
        $connection = connectTo-Rubrik
    }

    # Stop Snapshot
    $ManagedVolume | Stop-RubrikManagedVolumeSnapshot -Verbose

    # 返回非零退出码，标记作业失败
    exit 1
}

额外说明 #

• $LASTEXITCODE是PowerShell的自动变量，仅存储最后一个外部命令的退出码，所以必须在sqlcmd执行后立即检查。
• exit 1会让PowerShell脚本返回退出码1，SQL Server代理识别到非零退出码后，会将作业标记为失败。

内容的提问来源于stack exchange，提问作者shan