| title | description | services | documentationcenter | author | manager | editor | ms.assetid | ms.service | ms.custom | ms.devlang | ms.date | ms.author | ms.workload | ms.topic | ms.tgt_pltfrm |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
PowerShell: Export an Azure SQL database to a BACPAC file | Microsoft Docs |
Export an Azure SQL database to a BACPAC file by using PowerShell |
sql-database |
stevestein |
jhubbard |
9439dd83-812f-4688-97ea-2a89a864d1f3 |
sql-database |
migrate and move |
NA |
02/07/2017 |
sstein |
data-management |
article |
NA |
This article provides directions for exporting your Azure SQL database or a SQL Server database to a BACPAC file (stored in Azure Blob storage) using PowerShell. For an overview of exporting to a BACPAC file, see Export to a BACPAC.
Note
You can also export your Azure SQL database file to a BACPAC file using the Azure portal, SQL Server Management Studio, or SQLPackage.
To complete this article, you need the following:
- An Azure subscription.
- An Azure SQL database.
- An Azure Standard Storage account, with a blob container to store the BACPAC in standard storage.
[!INCLUDE Start your PowerShell session]
The New-AzureRmSqlDatabaseExport cmdlet submits an export database request to the service. Depending on the size of your database, the export operation may take some time to complete.
Important
To guarantee a transactionally consistent BACPAC file, you should first create a copy of your database, and then export the database copy.
$exportRequest = New-AzureRmSqlDatabaseExport -ResourceGroupName $ResourceGroupName -ServerName $ServerName `
-DatabaseName $DatabaseName -StorageKeytype $StorageKeytype -StorageKey $StorageKey -StorageUri $BacpacUri `
-AdministratorLogin $creds.UserName -AdministratorLoginPassword $creds.Password
After running New-AzureRmSqlDatabaseExport, you can check the status of the request by running Get-AzureRmSqlDatabaseImportExportStatus. Running this immediately after the request usually returns Status : InProgress. When you see Status: Succeeded the export is complete.
Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink
The following example exports an existing 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
Azure SQL Database Automated Export is now in preview and will be retired on March 1, 2017. Starting December 1, 2016, you will no longer be able to configure automated export on any SQL database. All your existing automated export jobs will continue to work until March 1, 2017. After December 1, 2016, you can use long-term backup retention or Azure Automation to archive SQL databases periodically using PowerShell periodically according to a schedule of your choice. For a sample script, you can download the sample script from Github.
- To learn how to import an Azure SQL database by using Powershell, see Import a BACPAC using PowerShell.
- To learn about importing a BACPAC using SQLPackage, see Import a BACPAC to Azure SQL Database using SqlPackage
- To learn about importing a BACPAC using the Azure portal, see Import a BACPAC to Azure SQL Database using the Azure portal
- For a discussion of the entire SQL Server database migration process, including performance recommendations, see Migrate a SQL Server database to Azure SQL Database.
- To learn about long-term backup retention of an Azure SQL database backup as an alternative to exported a database for archive purposes, see Long term backup retention
- To learn about importing a BACPAC to a SQL Server database, see Import a BACPCAC to a SQL Server database