Export an Azure SQL database to a BACPAC file

This article discusses exporting an Azure SQL database to a BACPAC file. This article discusses using the following methods:

Important

Azure SQL Database Automated Export was retired on March 1, 2017. You can use long-term backup retention or Azure Automation to periodically archive SQL databases using PowerShell according to a schedule of your choice. For a sample, download the sample PowerShell script from Github.

Overview

When you need to export a database for archiving or for moving to another platform, you can export the database schema and data to a BACPAC file. A BACPAC file is a ZIP file with an extension of BACPAC containing the metadata and data from a SQL Server database. A BACPAC file can be stored in Azure blob storage or in local storage in an on-premises location and later imported back into Azure SQL Database or into a SQL Server on-premises installation.

Important

If you are exporting from SQL Server as a prelude to migration to Azure SQL Database, see Migrate a SQL Server database to Azure SQL Database.

Considerations

  • For an export to be transactionally consistent, you must ensure either that no write activity is occurring during the export, or that you are exporting from a transactionally consistent copy of your Azure SQL database.
  • If you are exporting to blob storage, the maximum size of a BACPAC file is 200 GB. To archive a larger BACPAC file, export to local storage.
  • Exporting a BACPAC file to Azure premium storage using the methods discussed in this article is not supported.
  • If the export operation from Azure SQL Database exceeds 20 hours, it may be canceled. To increase performance during export, you can:
    • Temporarily increase your service level.
    • Cease all read and write activity during the export.
    • Use a clustered index with non-null values on all large tables. Without clustered indexes, an export may fail if it takes longer than 6-12 hours. This is because the export service needs to complete a table scan to try to export entire table. A good way to determine if your tables are optimized for export is to run DBCC SHOW_STATISTICS and make sure that the RANGE_HI_KEY is not null and its value has good distribution. For details, see DBCC SHOW_STATISTICS.
Note

BACPACs are not intended to be used for backup and restore operations. Azure SQL Database automatically creates backups for every user database. For details, see Business Continuity Overview and SQL Database backups.

Azure portal

To export a database using the Azure portal, open the page for your database and click Export on the toolbar. Specify the *.bacpac filename, provide the Azure storage account and container for the export, and provide the credentials to connect to the source database.

Database export

To monitor the progress of the export operation, open the page for the logical server containing the database being exported. Scroll down to Operations and then click Import/Export history.

SQLPackage utility

To export a SQL database using the SqlPackage command-line utility, see Export parameters and properties. The SQLPackage utility ships with the latest versions of SQL Server Management Studio and SQL Server Data Tools for Visual Studio, or you can download the latest version of SqlPackage directly from the Microsoft download center.

We recommend the use of the SQLPackage utility for scale and performance in most production environments. For a SQL Server Customer Advisory Team blog about migrating using BACPAC files, see Migrating from SQL Server to Azure SQL Database using BACPAC Files.

This example shows how to export a database using SqlPackage.exe with Active Directory Universal Authentication:

SqlPackage.exe /a:Export /tf:testExport.bacpac /scs:"Data Source=apptestserver.database.windows.net;Initial Catalog=MyDB;" /ua:True /tid:"apptest.onmicrosoft.com"

SQL Server Management Studio

The newest versions of SQL Server Management Studio also provide a wizard to export an Azure SQL Database to a bacpac file. See the Export a Data-tier Application.

PowerShell

Use the New-AzureRmSqlDatabaseImport cmdlet to submit an export database request to the Azure SQL Database service. Depending on the size of your database, the export operation may take some time to complete.

$exportRequest = New-AzureRmSqlDatabaseExport -ResourceGroupName $ResourceGroupName -ServerName $ServerName `
  -DatabaseName $DatabaseName -StorageKeytype $StorageKeytype -StorageKey $StorageKey -StorageUri $BacpacUri `
  -AdministratorLogin $creds.UserName -AdministratorLoginPassword $creds.Password

To check the status of the export request, use the Get-AzureRmSqlDatabaseImportExportStatus cmdlet. Running this immediately after the request usually returns Status: InProgress. When you see Status: Succeeded the export is complete.

$importStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $importRequest.OperationStatusLink
[Console]::Write("Exporting")
while ($importStatus.Status -eq "InProgress")
{
    $importStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $importRequest.OperationStatusLink
    [Console]::Write(".")
    Start-Sleep -s 10
}
[Console]::WriteLine("")
$importStatus

Export SQL database example

The following example exports an existing Azure SQL database to a BACPAC and then shows how to check the status of the export operation.

To run the example, there are a few variables you need to replace with the specific values for your database and storage account. In the Azure portal, browse to your storage account to get the storage account name, blob container name, and key value. You can find the key by clicking Access keys on your storage account blade.

Replace the following VARIABLE-VALUES with values for your specific Azure resources. The database name is the existing database you want to export.

$subscriptionId = "YOUR AZURE SUBSCRIPTION ID"

Login-AzureRmAccount
Set-AzureRmContext -SubscriptionId $subscriptionId

# Database to export
$DatabaseName = "DATABASE-NAME"
$ResourceGroupName = "RESOURCE-GROUP-NAME"
$ServerName = "SERVER-NAME
$serverAdmin = "ADMIN-NAME"
$serverPassword = "ADMIN-PASSWORD" 
$securePassword = ConvertTo-SecureString -String $serverPassword -AsPlainText -Force
$creds = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $serverAdmin, $securePassword

# Generate a unique filename for the BACPAC
$bacpacFilename = $DatabaseName + (Get-Date).ToString("yyyyMMddHHmm") + ".bacpac"

# Storage account info for the BACPAC
$BaseStorageUri = "https://STORAGE-NAME.blob.core.windows.net/BLOB-CONTAINER-NAME/"
$BacpacUri = $BaseStorageUri + $bacpacFilename
$StorageKeytype = "StorageAccessKey"
$StorageKey = "YOUR STORAGE KEY"

$exportRequest = New-AzureRmSqlDatabaseExport -ResourceGroupName $ResourceGroupName -ServerName $ServerName `
  -DatabaseName $DatabaseName -StorageKeytype $StorageKeytype -StorageKey $StorageKey -StorageUri $BacpacUri `
  -AdministratorLogin $creds.UserName -AdministratorLoginPassword $creds.Password

$exportRequest

# Check status of the export
Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink

Next steps