如何用PowerShell批量导入同一服务器上的多个Azure SQL数据库？

Got it, let's revamp your script to handle bulk database imports without manually editing the database name every time. Here's a polished, scalable version that loops through a list of target databases and their corresponding bacpac files:

改造后的完整脚本 #

# 导入依赖模块
Import-Module $PSScriptRoot\..\util\utilConnection.ps1
Import-Module $PSScriptRoot\..\util\utilDate.ps1
# Import-Module $PSScriptRoot\..\logging\Logging_Functions.ps1
Import-Module AzureRM.sql

# 初始化通用配置（只执行一次，避免重复操作）
$TIMESTAMP = getTimeStamp
# $LogPath = getPathLog
# $logFileName = "prueba_jobDatabaseBackup.log"
# Log-Start -LogPath $LogPath -LogName $logFileName -ScriptVersion "1.5"
# return;

# 登录Azure（如果未登录的话）
# Login-AzureRmAccount -Credential (Get-Credential)
# Set-AzureRmContext -SubscriptionName "itsits"

# 创建数据库管理员凭据（只创建一次）
$myPasswordDB = ConvertTo-SecureString $SQL_ACCOUNT_PASSWORD_QA -AsPlainText -Force
$myCredentialDB = New-Object System.Management.Automation.PSCredential ($SQL_ACCOUNT_NAME_QA, $myPasswordDB)
$sqlCredential = Get-Credential -Credential $myCredentialDB

# 通用参数
$resourceGroup = "resGroupDB"
$serverName = "domserverqa"
$primarykey = $STORAGE_ACCOUNT_BACKUP_KEY
$SQL_SERVER_FULLNAME_QA = getSQLServerFullNameAzureQA
$TIMEOUT = 300
$elasticPoolName = "ElasticPoolDOMQA(Basic)"

# --------------------------
# 定义批量导入的数据库列表
# 每个条目包含：目标数据库名、对应的bacpac文件存储URI
# --------------------------
$databaseImportList = @(
    @{
        DatabaseName = "mirroring_dbdom_coquimbo(2018-01-09-07:00)"
        StorageUri   = "https://strdatabasebackup.blob.core.windows.net/strdatabasebackupblob/(2018-01-09-07:00)dbdom_coquimbo.bacpac"
    },
    @{
        DatabaseName = "mirroring_dbdom_santiago(2018-01-09-07:00)"
        StorageUri   = "https://strdatabasebackup.blob.core.windows.net/strdatabasebackupblob/(2018-01-09-07:00)dbdom_santiago.bacpac"
    },
    # 在这里继续添加更多数据库条目
    @{
        DatabaseName = "mirroring_dbdom_valparaiso(2018-01-09-07:00)"
        StorageUri   = "https://strdatabasebackup.blob.core.windows.net/strdatabasebackupblob/(2018-01-09-07:00)dbdom_valparaiso.bacpac"
    }
)

# --------------------------
# 循环执行批量导入
# --------------------------
foreach ($dbImport in $databaseImportList) {
    $currentDbName = $dbImport.DatabaseName
    $currentStorageUri = $dbImport.StorageUri

    Write-Output "`n=== Starting import for database: $currentDbName ==="

    # 发起导入请求
    $importRequest = New-AzureRmSqlDatabaseImport `
        –ResourceGroupName $resourceGroup `
        –ServerName $serverName `
        –DatabaseName $currentDbName `
        –StorageKeytype StorageAccessKey `
        –StorageKey $primarykey `
        -StorageUri $currentStorageUri `
        -AdministratorLogin $sqlCredential.UserName `
        –AdministratorLoginPassword $sqlCredential.Password `
        –Edition Basic `
        –ServiceObjectiveName basic `
        -DatabaseMaxSizeBytes 2147483648 # 2GB

    # 等待导入完成
    $importStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $importRequest.OperationStatusLink
    while ($importStatus.Status -eq "InProgress") {
        Write-Output "."
        [System.Threading.Thread]::Sleep(2000)
        $importStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $importRequest.OperationStatusLink
    }

    # 检查导入结果
    if ($importStatus.Status -eq "Succeeded") {
        Write-Output "`nImport completed successfully for $currentDbName"

        # 将数据库移入弹性池
        $QUERY = "ALTER DATABASE [$currentDbName] MODIFY (SERVICE_OBJECTIVE = ELASTIC_POOL (name = [$elasticPoolName]))"
        Write-Output "Executing query: $QUERY"
        Invoke-Sqlcmd `
            -AbortOnError `
            -EncryptConnection `
            -ConnectionTimeout $TIMEOUT `
            -Database 'master' `
            -ServerInstance $SQL_SERVER_FULLNAME_QA `
            -Username "prueba" `
            -Password "prueba._" `
            -Query $QUERY

        Write-Output "Successfully added $currentDbName to elastic pool"
    } else {
        Write-Error "`nImport failed for $currentDbName. Status: $($importStatus.Status). Message: $($importStatus.ErrorMessage)"
    }
}

Write-Output "`n=== Bulk import process finished ==="

关键改动说明 #

1. 定义批量导入列表 #

我创建了一个$databaseImportList数组，每个元素是一个哈希表，包含要导入的目标数据库名和对应的bacpac文件存储URI。你只需要在这里添加/删除条目，不用修改脚本的核心逻辑。

如果你的bacpac文件命名有规律（比如和数据库名对应），还可以自动遍历存储容器里的bacpac文件来生成这个列表，比如用Get-AzureStorageBlob命令，这样更高效：

# 示例：自动从存储容器获取所有bacpac文件并生成导入列表
$storageAccount = Get-AzureRmStorageAccount -ResourceGroupName "your-storage-rg" -Name "strdatabasebackup"
$container = Get-AzureStorageContainer -Context $storageAccount.Context -Name "strdatabasebackupblob"
$databaseImportList = $container | Get-AzureStorageBlob | Where-Object { $_.Name -like "*.bacpac" } | ForEach-Object {
    # 根据bacpac文件名提取数据库名（根据你的命名规则调整）
    $dbName = "mirroring_$($_.Name.Replace('.bacpac',''))"
    @{
        DatabaseName = $dbName
        StorageUri   = $_.ICloudBlob.Uri.AbsoluteUri
    }
}

2. 循环执行导入逻辑 #

把原来的单库导入代码套进foreach循环，每次迭代使用列表里的currentDbName和currentStorageUri替换原来的硬编码值。这样脚本会自动依次处理每个数据库。

3. 优化通用配置 #

把凭据创建、通用参数定义（比如资源组、服务器名）移到循环外面，只执行一次，避免重复操作，提升脚本效率。

4. 增加状态检查和错误处理 #

在导入完成后添加了状态检查，如果导入失败会输出错误信息，方便排查问题；成功的话才执行弹性池调整操作。

注意事项 #

• 确保$SQL_ACCOUNT_PASSWORD_QA、$SQL_ACCOUNT_NAME_QA等环境变量已经正确设置
• 如果使用自动遍历存储容器的方式，要确保你的命名规则和脚本里的提取逻辑匹配
• 可以根据需要调整导入时的Edition、ServiceObjectiveName和DatabaseMaxSizeBytes参数

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