使用 PowerShell 将 BACPAC 文件导入 SQL 数据库中的数据库

适用于：Azure SQL 数据库

以下 Azure PowerShell 脚本示例将数据库从 BACPAC 文件导入 SQL 数据库中的新数据库。

如果没有 Azure 试用版订阅，请在开始前创建一个试用版订阅。

注意

本文使用 Azure Az PowerShell 模块，这是与 Azure 交互时推荐使用的 PowerShell 模块。 若要开始使用 Az PowerShell 模块，请参阅安装 Azure PowerShell。 若要了解如何迁移到 Az PowerShell 模块，请参阅 将 Azure PowerShell 从 AzureRM 迁移到 Az。

本教程需要 Az PowerShell 1.4.0 或更高版本。 如果需要进行升级，请参阅 Install Azure PowerShell module（安装 Azure PowerShell 模块）。 此外，还需要运行 Connect-AzAccount -EnvironmentName AzureChinaCloud 以创建与 Azure 的连接。

示例脚本

# Connect-AzAccount -Environment AzureChinaCloud
# The SubscriptionId in which to create these objects
$subscriptionId = "<Subscription-ID>"
# Set the resource group name and location for your server
$resourceGroupName = "myResourceGroup-$(Get-Random)"
$location = "chinaeast2"
# Set an admin login and password for your server
$adminSqlLogin = "<admin>"
$password = "<password>"
# Set server name - the logical server name has to be unique in the system
$serverName = "server-$(Get-Random)"
# The sample database name
$databaseName = "myImportedDatabase"
# The storage account name and storage container name
$storageAccountName = "sqlimport$(Get-Random)"
$storageContainerName = "importcontainer$(Get-Random)"
# BACPAC file name
$bacpacFilename = "sample.bacpac"
# The IP address range that you want to allow to access your server
$startIp = "0.0.0.0"
$endIp = "0.0.0.0"

# Set subscription
Set-AzContext -SubscriptionId $subscriptionId

# Create a resource group
$resourceGroup = New-AzResourceGroup -Name $resourceGroupName -Location $location

# Create a storage account
$storageAccountParams = @{
    ResourceGroupName = $resourceGroupName
    Name              = $storageAccountName
    Location          = $location
    SkuName           = "Standard_LRS"
}
$storageAccount = New-AzStorageAccount @storageAccountParams

# Create a storage context for the storage account
$storageAccountKey = (Get-AzStorageAccountKey -ResourceGroupName $resourceGroupName -Name $storageAccountName).Value[0]
$storageContext = New-AzStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $storageAccountKey

# Create a storage container
$storageContainer = New-AzStorageContainer -Name $storageContainerName -Context $storageContext

# Download sample database from GitHub
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 #required by GitHub
Invoke-WebRequest -Uri "https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Standard.bacpac" -OutFile $bacpacFilename

# Upload sample database into storage container
$blobContentParams = @{
    Container = $storageContainerName
    File      = $bacpacFilename
    Context   = $storageContext
}
Set-AzStorageBlobContent @blobContentParams

# Create a credential for the server admin
$adminCredential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminSqlLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force)

# Create a new server with a system-wide unique server name
$serverParams = @{
    ResourceGroupName           = $resourceGroupName
    ServerName                  = $serverName
    Location                    = $location
    SqlAdministratorCredentials = $adminCredential
}
$server = New-AzSqlServer @serverParams

# Create a server firewall rule that allows access from the specified IP range
$firewallParams = @{
    ResourceGroupName = $resourceGroupName
    ServerName        = $serverName
    FirewallRuleName  = "AllowedIPs"
    StartIpAddress    = $startIp
    EndIpAddress      = $endIp
}
$serverFirewallRule = New-AzSqlServerFirewallRule @firewallParams

# Import BACPAC to database with an S3 performance level
$importParams = @{
    ResourceGroupName          = $resourceGroupName
    ServerName                 = $serverName
    DatabaseName               = $databaseName
    DatabaseMaxSizeBytes       = 100GB
    StorageKeyType             = "StorageAccessKey"
    StorageKey                 = (Get-AzStorageAccountKey -ResourceGroupName $resourceGroupName -StorageAccountName $storageAccountName).Value[0]
    StorageUri                 = "https://$storageAccountName.blob.core.chinacloudapi.cn/$storageContainerName/$bacpacFilename"
    Edition                    = "Standard"
    ServiceObjectiveName       = "S3"
    AdministratorLogin         = "$adminSqlLogin"
    AdministratorLoginPassword = $(ConvertTo-SecureString -String $password -AsPlainText -Force)
}
$importRequest = New-AzSqlDatabaseImport @importParams

# Check import status and wait for the import to complete
$importStatus = Get-AzSqlDatabaseImportExportStatus -OperationStatusLink $importRequest.OperationStatusLink
[Console]::Write("Importing")
while ($importStatus.Status -eq "InProgress") {
    $importStatus = Get-AzSqlDatabaseImportExportStatus -OperationStatusLink $importRequest.OperationStatusLink
    [Console]::Write(".")
    Start-Sleep -s 10
}
[Console]::WriteLine("")
$importStatus

# Scale down to S0 after import is complete
$scaleDownParams = @{
    ResourceGroupName             = $resourceGroupName
    ServerName                    = $serverName
    DatabaseName                  = $databaseName
    Edition                       = "Standard"
    RequestedServiceObjectiveName = "S0"
}
Set-AzSqlDatabase @scaleDownParams

# Clean up deployment
# Remove-AzResourceGroup -ResourceGroupName $resourceGroupName

清理部署

使用以下命令删除资源组及其相关的所有资源。

Remove-AzResourceGroup -ResourceGroupName $resourcegroupname

脚本说明

此脚本使用以下命令。 表中的每条命令链接到特定于命令的文档。

命令 注释
New-AzResourceGroup 创建用于存储所有资源的资源组。
New-AzSqlServer 创建托管数据库和弹性池的服务器。
New-AzSqlServerFirewallRule 为服务器创建服务器级防火墙规则。
New-AzSqlDatabaseImport 导入 BACPAC 文件，并在服务器上创建一个新数据库。
Remove-AzResourceGroup 删除资源组，包括所有嵌套的资源。

相关内容

  • Last updated on