如何在单个事务中执行多个Invoke-Sqlcmd命令实现失败全量回滚

你原有基于TransactionScope的写法不生效有两个核心原因：

1. Invoke-Sqlcmd默认每次调用会创建独立数据库连接，不会自动加入当前事务上下文
2. 默认配置下Invoke-Sqlcmd执行SQL报错属于非终止错误，不会触发catch块逻辑，也就不会触发回滚

方案一：手动控制单连接事务（最稳妥，无分布式事务依赖） #

该方案全程复用同一个数据库连接，在连接上开启全局事务，所有SQL脚本都在该事务下执行，不需要依赖MSDTC服务，成功率更高。

修改后的完整代码： #

1. 主执行逻辑

# 先定义数据库连接参数
$ServerInstance = "你的实例名"
$DBName = "你的库名"
$SvcAdminAccount = "账号"
$SvcAdminPassword = "密码"
$SqlFilesDirectory = "SQL脚本根目录"

try {
    # 1. 创建并打开数据库连接
    $connectionString = "Server=$ServerInstance;Database=$DBName;User ID=$SvcAdminAccount;Password=$SvcAdminPassword;Connect Timeout=60"
    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    $connection.Open()
    
    # 2. 开启全局事务
    $transaction = $connection.BeginTransaction()

    # 3. 递归执行所有SQL脚本，传入连接和事务对象
    GetFiles -path $SqlFilesDirectory -connection $connection -transaction $transaction

    # 4. 全部执行成功提交事务
    $transaction.Commit()
    Write-Host "所有SQL脚本执行成功，事务已提交"
}
catch {
    # 任何报错回滚事务
    if ($transaction) {
        $transaction.Rollback()
    }
    Write-Error "执行失败，事务已回滚，错误信息：$($_.Exception.Message)"
}
finally {
    # 释放资源
    if ($connection.State -eq 'Open') {
        $connection.Close()
    }
    $connection.Dispose()
    $transaction.Dispose()
}

2. 修改后的GetFiles函数

function GetFiles($path = $pwd, $connection, $transaction) 
{ 
    $subFolders = Get-ChildItem -Path $path -Directory  | Select-Object FullName,Name | Sort-Object -Property Name 
    $sqlFiles = Get-ChildItem -Path $path -Filter *.sql | Select-Object FullName,Name | Sort-Object -Property Name
    
    foreach ($file in $sqlFiles)
    {
      Write-Host "正在执行文件: " $file.Name
      # 传入全局连接、事务，设置错误触发终止异常
      Invoke-Sqlcmd -Connection $connection -Transaction $transaction -InputFile $file.FullName -QueryTimeout 65535 -ErrorAction Stop
    } 

    foreach ($folder in $subFolders)
    {
       Write-Host "`n处理子文件夹: " $folder.Name
       GetFiles -path $folder.FullName -connection $connection -transaction $transaction
    } 
}

方案二：基于原有TransactionScope改造 #

如果你要保留TransactionScope的写法，需要做以下修改：

• 所有Invoke-Sqlcmd调用增加-ErrorAction Stop参数，触发终止异常
• 开启系统的**分布式事务协调器(MSDTC)**服务，且配置允许入站/出站事务
• 确保所有Invoke-Sqlcmd的连接参数完全一致，保证连接可以加入同一事务上下文
• 若使用SQL Server身份验证，需保证账号有分布式事务执行权限

注意事项 #

• 所有执行的SQL脚本中不能包含COMMIT、ROLLBACK、BEGIN TRANSACTION这类事务控制语句，否则会破坏外层全局事务
• 如果脚本包含GO批处理分隔符，Invoke-Sqlcmd原生支持无需额外处理，不影响事务
• 大脚本需要确保-QueryTimeout参数设置足够大，避免超时中断导致回滚

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