SQL Agent中PowerShell SSAS备份脚本错误处理失效问题

问题描述 #

我知道这是常见问题，但现有解决方案无法解决我的问题。以下代码用于通过SQLServer PowerShell模块备份SSAS多维数据集：脚本从JSON文件读取服务器、备份路径和备份保留时长等配置，循环遍历各服务器执行备份、清理旧备份等操作，类似简化版Ola Hallengren脚本。

核心问题：设置的错误处理无法正常工作，在SQL Agent作业中执行时，无论是否出错始终返回退出码0；但在本地PowerShell窗口执行时结果符合预期。

已尝试的解决方法 #

• 在SQL Agent作业中分别以PowerShell模式和CMDExec模式运行
• 使用Throw/Write-Error，包含指定/不指定退出码的情况
• 尝试多种try/catch、try/catch/finally写法，将退出逻辑移至END块等
• 测试不同的EXIT方法（脚本中已注释部分尝试项）
• 调整ErrorActionPreference的不同设置

SQL Agent作业CMDExec模式调用命令 #

powershell.exe 'D:\dba\dba - SSAS - Take Backups\Backup_SSAS_Databases.ps1'

实际执行的脚本代码 #

[CmdletBinding()]
param ()

BEGIN{
    Import-Module SQLServer

    #$ErrorActionPreference = "Stop"

    [boolean]$ErrorOccurred = $false
    [boolean]$ExitWhenFinished = $true
    [string]$LogFileName = "SSAS_Backup_log_$(GET-DATE -Format 'yyyyMMdd_HHmmss').txt"
    [string]$FileNameTemplate = "<ServerName>_<DBName>_$(get-date -Format 'yyyyMMdd_HHmmss').abf"
    [string]$ConfigFile = $MyInvocation.MyCommand.Source.Replace($MyInvocation.MyCommand.Name, 'SSAS_Config.json')

    if ($LogPath)
        {[String]$LogFile = "$LogPath\$LogFileName"}
    else 
        {[String]$LogFile = $MyInvocation.MyCommand.Source.Replace($MyInvocation.MyCommand.Name, $LogFileName)  }
    function Write-Log {
        [CmdletBinding()]
        param(
            [Parameter()]
            [ValidateNotNull()]
            [string]$Message,
     
            [Parameter()]
            [ValidateNotNullOrEmpty()]
            [ValidateSet('INFO','WARN','EROR')]
            [string]$Severity = 'INFO',

            [Parameter()]
            [ValidateNotNullOrEmpty()]
            [string]$Directory
        )
        [pscustomobject]@{
            Time = "[$(Get-Date -f "MM/dd/yyyy HH:mm:ss")]"
            Severity = "[$Severity]"
            Message = $Message
        } | Export-Csv -Path "$Directory" -Append -NoTypeInformation -Delimiter "`t"
    }

    Write-Log -Message "Let the games begin." -Severity INFO -Directory $LogFile
}
PROCESS
{
    try {
        ## Read the config file to capture the required server information.
        $SSASConfigInfo = (Get-Content -path $ConfigFile | Out-String | ConvertFrom-Json)     
        Write-Log -Message "Config File: $ConfigFile" -Severity INFO -Directory $LogFile
        Write-Log -Message "Records Read : $($SSASConfigInfo.Count)" -Severity INFO -Directory $LogFile
        Write-Log -Message "Exit when finished : $ExitWhenFinished)" -Severity INFO -Directory $LogFile
        

        ## Loop through each server
        foreach ($ServerInfo in $SSASConfigInfo)
        {
            [string]$Server = $ServerInfo.SSAS_Server
            [string]$BackupFolder = $ServerInfo.BackupFolder
            [int]$RetentionInHours = $ServerInfo.Retention

            Write-Log -Message "Server:  $Server" -Severity INFO -Directory $LogFile
            Write-Log -Message "BackupPath: $BackupFolder" -Severity INFO -Directory $LogFile
            Write-Log -Message "Retention: $RetentionInHours hours."  -Severity INFO -Directory $LogFile
            Write-Log -Message "Error Occurred : $ErrorOccurred"   -Severity INFO -Directory $LogFile

            Write-Log "Starting backups of [$Server]."  -Severity INFO -Directory $LogFile
            ## Let's get the db/cube information from the SSAS Server
            try
            {
                [xml]$AllDBInfo = Invoke-ASCmd -Server $Server -Query  "<Discover xmlns='urn:schemas-microsoft-com:xml-analysis'><RequestType>DBSCHEMA_CATALOGS</RequestType><Restrictions /><Properties /></Discover>" -ErrorAction Stop
                $DBList = $AllDBInfo.DiscoverResponse.return.root.row.Catalog_Name
                Write-Log "DB count retrieved = $($DBList.Count)"  -Severity INFO -Directory $LogFile
            }
            catch {
                $ErrorOccurred = $true
                Write-Log "$($_.Exception.Message)" -Severity EROR -Directory $LogFile
                continue
            }
           
            foreach ($db in $DBList)
            {
                ## Let's set our vairables 
                Write-Log ""  -Severity INFO -Directory $LogFile
                Write-Log "Starting backup of [$db]."  -Severity INFO -Directory $LogFile
                $BackupFileName = $FileNameTemplate.Replace('<ServerName>', $Server)
                $BackupFileName = $BackupFileName.Replace('<DBName>', $DB)

                $BackupPath = Join-Path -Path $BackupFolder -ChildPath $Server
                $BackupPath = Join-Path -Path $BackupPath -ChildPath $db
                $BackupPath = Join-Path -Path $BackupPath -ChildPath 'SSASBackup'

                $FullBackupName = join-path -path $BackupPath -childpath $BackupFileName
                Write-Log "Backup Path: $BackupPath"  -Severity INFO -Directory $LogFile
                Write-Log "Backup Name: $BackupFileName"  -Severity INFO -Directory $LogFile
                Write-Log "Full Path  : $FullBackupName" -Severity INFO -Directory $LogFile
                
                if (test-path -path $BackupPath)
                    {Write-Log "BackupPath validated successfully."-Severity INFO -Directory $LogFile}
                else
                    {
                        Write-Log "Created the backup path: '$BackupPath'"-Severity INFO -Directory $LogFile
                        New-Item -Path $BackupPath -ItemType Directory | out-null
                    }
                
                <##############################################     Backup SSAS Cube/DB #########################################>
                try
                {
                    write-log "Backup Command:  Backup-ASDatabase -Server $Server -BackupFile $FullBackupName -Name $db -AllowOverwrite -ApplyCompression -Verbose" -Severity INFO -Directory $LogFile
                    Write-Log "Backup Start" -Severity INFO -Directory $LogFile
                    measure-command {Backup-ASDatabase -Server $Server -BackupFile $FullBackupName -Name $db -AllowOverwrite -ApplyCompression -ErrorAction Stop} -OutVariable duration | out-null
                    Write-Log "Backup Command Completed. "  -Severity INFO -Directory $LogFile
                    Write-Log "Backup Duration: $($duration.Hours) hour(s) $($duration.Minutes) minute(s) $($duration.Seconds) seconds" -Severity INFO -Directory $LogFile
                    write-log "Results: Success." -Severity INFO -Directory $LogFile
                    
                }
                catch
                {
                    ## write the error to the log file and then continue to the next cube.  Note this will also bypass the delete of the older backup, which is intended.
                    Write-Log -Message "$($_.Exception.Message)" -Severity EROR -Directory $LogFile
                    Write-Log "Backup Command Completed"  -Severity INFO -Directory $LogFile
                    Write-Log "Backup Duration: $($duration.Hours) hour(s) $($duration.Minutes) minute(s) $($duration.Seconds) seconds" -Severity INFO -Directory $LogFile
                    Write-Log -Message "Result: Fail" -Severity INFO -Directory $LogFile
                    $ErrorOccurred = $true
                    continue
                }
        
                ## Let's remove older backups
                if ($RetentionInHours -gt 0)
                    {$RemoveFilesOlderThan = (get-date).AddHours($Retentioninhours * -1)}
                else 
                    {$RemoveFilesOlderThan = (get-date).AddHours($Retentioninhours)}
        
                ## Get file list
                $filesToRemove = get-childitem -Path $BackupPath | Where-Object {$_.extension -ieq '.abf' -and $_.LastWriteTime -le $RemoveFilesOlderThan}

                try 
                    {$filesToRemove  |  Remove-Item -ErrorAction Stop }
                catch {
                    ## Write the error but contiue. Shouldn't stop taking new backups if we can't remove the old ones.  
                    Write-Log -Message "$($_.Exception.Message)" -Severity EROR -Directory $LogFile
                    $ErrorOccurred = $true
                    continue
                }     
                
                Write-Log "Server: [$server] completed."  -Severity INFO -Directory $LogFile
                Write-Log "" -Severity INFO -Directory $LogFile
            }
        }
    }
    catch {
        Write-Log -Message 'Primary TRY/CATCH triggered.  Error below.' -Severity EROR -Directory $LogFile
        Write-Log -Message "$($_.Exception.Message)" -Severity EROR -Directory $LogFile
        Write-Log "Server [$Server] completed."  -Severity INFO -Directory $LogFile
        Write-Log "" -Severity INFO -Directory $LogFile
        $ErrorOccurred = $true
    }
    finally {
        Write-Host "Error Occurred = $ErrorOccurred"
        if ($ErrorOccurred) 
            {
                write-host 'Got inside the error block'
                Write-Verbose 'Yes, there is an error.'
                Write-Error  -Message "Error has occurred.  Log at $LogFile"   
                [System.Environment]::Exit(1)
                #throw "Error has occurred.  Log at $LogFile"
                #[System.Environment]::Exit(1)
            }
        else
            {
                Write-Host 'Process completed Successfully.'
                Write-Verbose 'Nope, these are not the errors you are looking for.'
                #[System.Environment]::Exit(0)
                [System.Environment]::Exit(0)
            }
    }
}
END{
    Write-Verbose 'Got to the END segment of the code.'     
}

排查思路与解决方案 #

1. 统一错误处理策略 #

在脚本BEGIN块开头强制设置：

$ErrorActionPreference = "Stop"

确保所有非终止错误转为终止错误，让try/catch能捕获到所有异常，避免错误被遗漏导致$ErrorOccurred未被标记。

2. 修复SQL Agent中退出码传递问题 #

SQL Agent环境中，Exit命令可能无法正确传递退出码，改用[System.Environment]::Exit($code)直接终止进程，确保退出码被SQL Agent接收。脚本中finally块的退出逻辑已做对应修改。

3. 修正错误引用问题 #

脚本中部分catch块使用$Error.exception.message，这会引用全局错误变量而非当前异常，应统一改为$_.Exception.Message，确保错误日志准确，同时保证$ErrorOccurred被正确设置。

4. 优化SQL Agent调用命令 #

CMDExec模式下，使用-File参数处理带空格的脚本路径，同时添加-NoProfile和-ExecutionPolicy Bypass避免环境差异：

powershell.exe -NoProfile -ExecutionPolicy Bypass -File "D:\dba\dba - SSAS - Take Backups\Backup_SSAS_Databases.ps1"

5. 增加错误验证日志 #

在finally块中添加日志写入，记录最终的$ErrorOccurred值和退出码，方便排查逻辑是否执行：

Write-Log -Message "Final Error Status: $ErrorOccurred, Exiting with code: $(if($ErrorOccurred){1}else{0})" -Severity INFO -Directory $LogFile

6. 测试错误路径 #

手动构造错误场景（如配置文件缺失、SSAS服务器不可达），在SQL Agent中执行脚本，查看日志确认$ErrorOccurred是否被正确标记，以及退出码是否传递给SQL Agent。

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