Azure Automation Runbook执行数据库刷新脚本失败求助

架构老林

2026-6-27

Azure SQL托管实例数据库刷新Runbook执行失败排查方案

问题概述

基于微软技术社区的数据库复制脚本，实现从生产Azure SQL托管实例刷新低环境数据库，本地运行完全正常，但部署为Azure Automation Runbook后无法执行。已开启详细日志仍无法定位有效错误，使用用户分配的托管身份，怀疑存在权限缺失但无法明确具体项。

脚本内容如下：

# Newest version for testing
Disable-AzContextAutosave -Scope Process

# Connect to Azure with user-assigned managed identity
$AzureContext = (Connect-AzAccount -Identity -AccountId "user-defined-managed-id here").context

# set and store context
$AzureContext = Set-AzContext -SubscriptionName $AzureContext.Subscription -DefaultProfile $AzureContext

# Select UAT Subscription
Select-AzSubscription -SubscriptionId [non-prod subscription id here]

$dbNames = @("Mv_SISD", "Mv_SISD_GEO", "Mv_SISD_WO")
$sourceMi = "sqlmi-prd-02"
$sourceRg = "SQL-Prod"
$targetMi = "sqlmi-dev-02"
$targetRg = "SQL-UAT"

$timeout = $false

$expectedDuration = New-TimeSpan -Days 0 -Hours 1 -Minutes 30
$startTime = Get-Date

Write-Host "Starting drop operation for databases: "
for ( $i = 0; $i -lt $dbNames.count; $i++) { Write-Host $dbNames[$i] " "}

## Start drop for all databases in the list.
##
for ( $i = 0; $i -lt $dbNames.count; $i++)
{
    $vmExists = Get-AzResource -Name $dbNames[$i] -ResourceGroupName $targetRg
    if ($vmExists) {
    Remove-AzSqlInstanceDatabase -Name $dbNames[$i] -InstanceName $targetMi -ResourceGroupName $targetRg -Force}
    else {
        Write-Output "VM $dbnames[$i] does not exist, skipping."
    }
}

##$allDropsSucceeded = $true

# Select PROD Subscription
Write-Host "Switch to Prod subscription: "
Select-AzSubscription -SubscriptionId [prod subscription id here] 

Write-Host "Starting copy operation for databases: "
for ( $i = 0; $i -lt $dbNames.count; $i++) { Write-Host $dbNames[$i] " "}

## Start copy for all databases in the list.
##
for ( $i = 0; $i -lt $dbNames.count; $i++)
{
    Copy-AzSqlInstanceDatabase -DatabaseName $dbNames[$i] -InstanceName $sourceMi -ResourceGroupName $sourceRg -TargetInstanceName $targetMi -TargetResourceGroupName $targetRg -AsJob | Format-Table -Property Name, PSBeginTime, State
}

$allCopiesSucceeded = $true

while($true)
{
    if ((Get-Date) -gt $startTime + $expectedDuration)
    {
        Write-Host "Script timeout. Terminating the script."
        $timeout = $true
        break
    }

    Write-Host "Getting the status of operations..."
    Start-Sleep -Seconds 10
    $operations = [System.Collections.ArrayList]@()

    ## Get status of all copy operations.
    ##
    for ( $i = 0; $i -lt $dbNames.count; $i++)
    {
        $operations += Get-AzSqlInstanceDatabaseCopyOperation -DatabaseName $dbNames[$i] -InstanceName $sourceMi -ResourceGroupName $sourceRg -TargetInstanceName $targetMi -TargetResourceGroupName $targetRg -OnlyLatestPerDatabase
    }

    try
    {
        $allCopiesSucceeded = $true;
        for ( $i = 0; $i -lt $dbNames.count; $i++)
        {
            ## Call with -AsJob, because this is long running operation (size of data),
            ## and we want to parallelize calls for multiple databases.
            ##
            Write-Host "Status of copy for " $dbNames[$i] " is " $operations[$i].State
            if (!($operations[$i].State -eq "Succeeded")) { $allCopiesSucceeded = $false; }
        }
    }
    catch { $allCopiesSucceeded = $false; Write-Host "Oops, something went wrong. Retrying..." }

    if ($allCopiesSucceeded)
    {
        Write-Host "All operations ready for completion!"
        break
    }
    else { Write-Host "Copying in progress..." }

    Write-Host "Waiting a bit..."
    Start-Sleep -Seconds 5
}

if ($allCopiesSucceeded -and !$timeout) 
{
    ## Let's complete all database copy operations.
    ##
    Write-Host "Completing all database copy operations..."

    for ( $i = 0; $i -lt $dbNames.count; $i++)
    {
        ## Call with -AsJob, because this is not super quick operation
        ## and we want to parallelize calls for multiple databases.
        ##
        Complete-AzSqlInstanceDatabaseCopy -DatabaseName $dbNames[$i] -InstanceName $sourceMi -ResourceGroupName $sourceRg -TargetInstanceName $targetMi -TargetResourceGroupName $targetRg -AsJob | Format-Table -Property Name, PSBeginTime, State
    }

    $completeDone = $true

    while($true)
    {
        Write-Host "Getting the status of copy complete operations..."
        $completeOperations = [System.Collections.ArrayList]@()

        for ( $i = 0; $i -lt $dbNames.count; $i++)
        {
            $completeOperations += Get-AzSqlInstanceDatabaseCopyOperation -DatabaseName $dbNames[$i] -InstanceName $sourceMi -ResourceGroupName $sourceRg -TargetInstanceName $targetMi -TargetResourceGroupName $targetRg -OnlyLatestPerDatabase
        }

        try
        {
            $completeDone = $true
            for ( $i = 0; $i -lt $dbNames.count; $i++)
            {
                Write-Host "Status of copy completion for " $dbNames[$i] " is " $completeOperations[$i].State
                if (!($completeOperations[$i].State -eq "Succeeded")) { $completeDone = $false; }
            }
        }
        catch { Write-Host "Oops, something went wrong. Retrying..." }

        if ($completeDone)
        {
            Write-Host "Database copy succeeded for databases: "
            for ( $i = 0; $i -lt $dbNames.count; $i++) { Write-Host $dbNames[$i] " "}

            break
        }
        else { Write-Host "Complete in progress..." }

        Write-Host "Waiting a bit..."
        Start-Sleep -Seconds 5
    }
}

排查与解决方案

一、权限验证（核心怀疑点）

托管身份需同时拥有生产订阅和非生产订阅的对应权限，具体配置项：

目标资源组（SQL-UAT）：分配SQL托管实例参与者或参与者角色，允许删除目标数据库
源资源组（SQL-Prod）：分配SQL托管实例参与者角色，允许发起数据库复制操作
托管身份关联：确认用户分配的托管身份已正确绑定到Azure Automation账户
跨订阅权限：确保托管身份在两个订阅中均完成权限分配，避免切换订阅后权限失效

二、Runbook环境适配调整

上下文切换优化
原脚本使用Select-AzSubscription切换订阅，建议改为Set-AzContext明确指定订阅ID，避免上下文切换异常：

# 切换到非生产订阅
Set-AzContext -SubscriptionId [non-prod subscription id here] -DefaultProfile $AzureContext
# 切换到生产订阅
Set-AzContext -SubscriptionId [prod subscription id here] -DefaultProfile $AzureContext

后台作业适配
Azure Automation中-AsJob的处理逻辑与本地环境不同，建议先移除该参数改为同步执行，验证基础功能正常后再考虑并行优化

日志增强
原脚本仅用Write-Host，Runbook中需改用Write-Output或Write-Verbose确保日志被捕获，同时添加错误捕获与详细输出：

try {
    Remove-AzSqlInstanceDatabase -Name $dbNames[$i] -InstanceName $targetMi -ResourceGroupName $targetRg -Force -ErrorAction Stop
    Write-Output "Successfully dropped database $($dbNames[$i])"
} catch {
    Write-Output "Failed to drop database $($dbNames[$i]): $_"
    throw $_ # 按需选择终止脚本或继续执行
}

三、脚本逻辑修正

数据库存在性检查修正
原脚本用Get-AzResource检查数据库存在性，会返回所有类型资源导致误判，应改用专门的SQL托管实例数据库检查：

$dbExists = Get-AzSqlInstanceDatabase -Name $dbNames[$i] -InstanceName $targetMi -ResourceGroupName $targetRg -ErrorAction SilentlyContinue
if ($dbExists) {
    Remove-AzSqlInstanceDatabase -Name $dbNames[$i] -InstanceName $targetMi -ResourceGroupName $targetRg -Force
} else {
    Write-Output "Database $($dbNames[$i]) does not exist, skipping."
}

超时逻辑优化
原脚本仅对比开始时间判断超时，建议添加剩余时间输出，同时确保超时后能清理未完成的复制操作

四、Runbook配置检查

模块版本：确认Azure Automation账户中的Az.Sql模块版本与本地运行版本一致，避免API差异导致失败
托管身份启用：在Automation账户的身份设置中，确认用户分配的托管身份已启用且权限配置正确
网络访问：若托管实例使用私有端点，需确保Automation账户能访问对应私有网络（可通过配置混合工作器组实现）

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

Azure Automation Runbook执行数据库刷新脚本失败求助

问题概述 #

排查与解决方案 #

一、权限验证（核心怀疑点） #

二、Runbook环境适配调整 #

三、脚本逻辑修正 #