PowerShell调用SqlCmd执行DBCC CheckDB时如何捕获输出存入变量

我使用的PowerShell代码逻辑为遍历SQL实例上的每个数据库，通过DBCC CheckDB SQL命令检测数据库损坏情况。检测到损坏时（比如示例中的50Ways数据库出现页损坏），DBCC CheckDB会输出消息而非抛出标准报错，处理50Ways时会弹出对应提示说明检测到损坏页。我需要捕获这类异常消息存入变量，方便后续发送邮件通知，求实现方案。

现有PowerShell代码 #

$ServerInstance = "myinstance" ## instance name 
$Databases = Invoke-SqlCmd -ServerInstance $ServerInstance -Database master -Query "SELECT [name] AS [Database] FROM sys.databases  ORDER BY 1 DESC;"

foreach ($DB in $Databases)
{
    Write-Output "Processing $($DB.Database)..."

    Invoke-SqlCmd -ServerInstance $ServerInstance -Database master -Query "DBCC CHECKDB ([$($DB.Database)]) WITH NO_INFOMSGS,PHYSICAL_ONLY;" -OutputSqlErrors:$true -Verbose
}
Write-Output "Complete."

输出示例 #

Processing WideWorldImporters...
Processing tempdb...
Processing msdb...
Processing model...
Processing master...
Processing DBA...
Processing AdventureWorks2017...
Processing 50Ways...
Invoke-SqlCmd : Table error: Object ID 901578250, index ID 0, partition ID 
72057594043105280, alloc unit ID 72057594049527808 (type In-row data), page (1:312). 
Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
Object ID 901578250, index ID 0, partition ID 72057594043105280, alloc unit ID 
72057594049527808 (type In-row data): Page (1:312) could not be processed.  See other 
errors for details.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'ToLeaveYourLover' 
(object ID 901578250).
CHECKDB found 0 allocation errors and 2 consistency errors in database '50Ways'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB 
(50Ways).
At line:11 char:5
+     Invoke-SqlCmd -ServerInstance $ServerInstance -Database master -Q ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
 
Complete.

错误截图 #

已尝试的失效方案 #

我试过用SQL层面的TRY CATCH块捕获错误，但无法成功捕获，代码如下：

BEGIN TRY
   DBCC CHECKDB ([50Ways]) WITH NO_INFOMSGS,PHYSICAL_ONLY
END TRY
BEGIN CATCH
   SELECT 
          ERROR_MESSAGE() AS [Error Message]
         ,ERROR_LINE() AS ErrorLine
         ,ERROR_NUMBER() AS [Error Number]  
         ,ERROR_SEVERITY() AS [Error Severity]  
         ,ERROR_STATE() AS [Error State]  

END CATCH

SQL层面TRY CATCH捕获失败的原因是：DBCC CHECKDB输出的损坏提示属于消息类输出，严重性等级默认低于CATCH块触发阈值，所以不会进入CATCH逻辑。直接在PowerShell层面修改代码捕获错误即可：

1. 初始化变量存储所有错误信息
2. 调用Invoke-SqlCmd时添加-ErrorVariable参数捕获输出的错误，同时加-ErrorAction SilentlyContinue避免错误打断检测循环
3. 遍历完所有数据库后判断错误变量是否有值，有值即可触发后续邮件通知逻辑

修改后的完整代码如下：

$ServerInstance = "myinstance"
# 初始化错误存储变量
$checkDbErrors = @()

$Databases = Invoke-SqlCmd -ServerInstance $ServerInstance -Database master -Query "SELECT [name] AS [Database] FROM sys.databases  ORDER BY 1 DESC;"

foreach ($DB in $Databases)
{
    Write-Output "Processing $($DB.Database)..."
    # 用ErrorVariable捕获当前命令的错误，错误不中断执行
    Invoke-SqlCmd -ServerInstance $ServerInstance -Database master -Query "DBCC CHECKDB ([$($DB.Database)]) WITH NO_INFOMSGS,PHYSICAL_ONLY;" -OutputSqlErrors:$true -ErrorAction SilentlyContinue -ErrorVariable dbErr
    
    # 如果当前数据库检测出错误，加入总错误集合
    if ($dbErr) {
        $checkDbErrors += [PSCustomObject]@{
            DatabaseName = $DB.Database
            ErrorDetail = $dbErr.ToString()
            CheckTime = Get-Date
        }
    }
}
Write-Output "Complete."

# 错误非空时后续处理，比如发邮件
if ($checkDbErrors.Count -gt 0) {
    # 此处补充你的发邮件逻辑，$checkDbErrors里就是所有检测到的损坏错误详情
    Write-Output "检测到损坏的数据库: $($checkDbErrors.DatabaseName -join ', ')"
    Write-Output "错误详情: $($checkDbErrors.ErrorDetail -join "`n`n")"
}

所需的错误消息已全部存储在$checkDbErrors变量中，可直接提取内容拼接邮件正文。

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