Edit

Use PowerShell to import a BACPAC file into a database in SQL Database

Applies to: Azure SQL Database

This Azure PowerShell script example imports a database from a BACPAC file into a new database in SQL Database.

If you don't have an Azure subscription, create an Azure free account before you begin.

Note

This article uses the Azure Az PowerShell module, which is the recommended PowerShell module for interacting with Azure. To get started with the Az PowerShell module, see Install Azure PowerShell. To learn how to migrate to the Az PowerShell module, see Migrate Azure PowerShell from AzureRM to Az.

Use Azure Cloud Shell

Azure hosts Azure Cloud Shell, an interactive shell environment that you can use through your browser. You can use either Bash or PowerShell with Cloud Shell to work with Azure services. You can use the Cloud Shell preinstalled commands to run the code in this article, without having to install anything on your local environment.

To start Azure Cloud Shell:

Option Example/Link
Select Try It in the upper-right corner of a code block. Selecting Try It doesn't automatically copy the code to Cloud Shell. Screenshot that shows an example of Try It for Azure Cloud Shell.
Go to https://shell.azure.com, or select the Launch Cloud Shell button to open Cloud Shell in your browser. Screenshot that shows how to launch Cloud Shell in a new window.
Select the Cloud Shell button on the menu bar at the upper right in the Azure portal. Screenshot that shows the Cloud Shell button in the Azure portal

To run the code in this article in Azure Cloud Shell:

  1. Start Cloud Shell.

  2. Select the Copy button on a code block to copy the code.

  3. Paste the code into the Cloud Shell session by selecting Ctrl+Shift+V on Windows and Linux, or by selecting Cmd+Shift+V on macOS.

  4. Select Enter to run the code.

If you choose to install and use PowerShell locally, this tutorial requires Az PowerShell 1.4.0 or later. If you need to upgrade, see Install Azure PowerShell module. If you are running PowerShell locally, you also need to run Connect-AzAccount to create a connection with Azure.

Sample script

# Connect-AzAccount
# 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 = "westeurope"
# 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.windows.net/$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

Clean up deployment

Use the following command to remove the resource group and all resources associated with it.

Remove-AzResourceGroup -ResourceGroupName $resourcegroupname

Script explanation

This script uses the following commands. Each command in the table links to command-specific documentation.

Command Notes
New-AzResourceGroup Creates a resource group in which all resources are stored.
New-AzSqlServer Creates a server that hosts databases and elastic pools.
New-AzSqlServerFirewallRule Creates a server-level firewall rule for a server.
New-AzSqlDatabaseImport Imports a BACPAC file and create a new database on the server.
Remove-AzResourceGroup Deletes a resource group including all nested resources.

Related content

  • Last updated on