Export Azure SQL Database | Advanced Scenarios

Introduction:

Export Azure SQL Database is a common request for Azure SQL DB customers, in this article we are going to list down some advanced scenarios, on how this can be achievable through various tools not limited to Azure Portal, Azure CLI and PowerShell. In addition, this article will provide alternative methods when it comes to private endpoints and deny public access.

 

Scenarios:

In this section, we are going through the scenarios and provide a thoughtful insight on each one. 

 

Note: - Import Export using Private Link now in public review, more information at blog article:

Import Export using Private Link now in Preview - Microsoft Tech Community

 

• Export via Azure Portal to Storage Account

This can be a seamless solution to do the database export when the SQL server allows the public access, untoggled the Deny public access option on SQL DB Azure portal, otherwise you might get error like:

 

An unexpected error was returned by the SQL engine while preparing the operation inputs. Error: 47073, State: 1.

To overcome such error, you can TEMPORARY set deny public access to NO during the export operation. 
 
Note: - You don’t need to worry, if you set “Deny public access” to “No” it doesn’t mean that everyone will be able to connect from outside; you still can restrict the access using the database firewall. You can find more information at:  

Connectivity settings for Azure SQL Database and Azure Synapse Analytics - Azure SQL Database and Azure Synapse Analytics | Microsoft Docs

 

• Export via REST API

You can use Export REST API  to export the database, this can be done programmatically, or from tools like Postman, Also you can try this from Azure Documentation using the >try it button, More information can be found at: Databases - Export - REST API (Azure SQL Database) | Microsoft Docs

 

Here is an example using postman:

Request Body:

 

 

{
  "storageKeyType": "StorageAccessKey",
  "storageKey": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx= =",
  "storageUri": https://xxxxxxxxxxxxxxxxx.blob.core.windows.net/testc,
  "administratorLogin": "xxxxxxxxxxxx",
  "administratorLoginPassword": "xxxxxxxxxxxxxx",
  "authenticationType": "Sql",
  "networkIsolation": {
    "sqlServerResourceId": "/subscriptions/xxxxxxxxxxxxx/resourceGroups/customer/providers/Microsoft.Sql/servers/xxxxxxxxx",
"storageAccountResourceId": "/subscriptions/xxxxxxxxxxx/resourceGroups/customer/providers/Microsoft.Storage/storageAccounts/xxxxxxxxx"
  }
}

 

 

Below error may occur if the Deny public access is enabled, the solution is to enable the public access temporarily
 

{"error":{"code":"ResourceNotFound","message":"The specified resource 'https://management.northeurope.control.database.windows.net/modules/AzureResourceManager.dsts/subscriptions/<yoursubscriptionid>/resourceGroups/customer/providers/Microsoft.Sql/servers/<servername>/databases/<dbname>/export?api-version=2021-02-01-preview ' was not found."}}

 

Note:- networkisolation setting, this feature is currently under development and not ready for public consumption. More information can be found at:

New-AzSqlDatabaseExport with network isolation · Discussion #13937 · Azure/azure-powershell · GitHub

Error when calling New-AzSqlDatabaseExport with UseNetworkIsolation on $true · Issue #13964 · Azure/azure-powershell · GitHub

 

• Export via SQLPackage

This can be a best bet solution for many scenarios to overcome limitations on the database size and also to export SQL DB via private endpoint through a VM running in the same VNET. 

 

Note:- you can export to local disk or Azure File Share, but you cannot use Azure Blob, for details can be found at Lesson Learned #25: Export/Import Azure SQL Database using Azure File Service? - Microsoft Tech Community 

 

Therefore, you can export the .bacpac locally/File share on the VM in the same VNET as the private endpoint of the SQL Server using SQLPackage.exe/SSMS then copy the bacpac to Azure blob (if required).

 

For example: Using SQLPackage to import or export SQL Server and Azure SQL DB - Microsoft Tech Community

 

• Export via SQL server Management Studio : Export using SSMS from the VM running in the same VNET as a private endpoint from SQL to blob storage/ file share

You can make use SQL Server Management Studio Export data-tier application wizard to export the Azure SQL database to a .bacpac file. The .bacpac can be stored into Azure blob storage or file share.

 

Right click on the SQL Database on logical SQL Server from SSMS --> Tasks --> Select 'Export data-tier application' wizard.

 

Select the location to store the BACPAC file 

 

You can select the subset of the tables from export setting in Advance tab --> Click Next to view the summary of export. One you click finish. And up on completion of the process you will be able to view the BACPAC file in the specified destination.

 

 

More information at blog:

Using data-tier applications (BACPAC) to migrate a database from Managed Instance to SQL Server - Microsoft Tech Community

 

• Export via Powershell/ CLI

The New-AzSqlDatabaseExport cmdlet can be used to export database request to the Azure SQL Database service. Make a note that you have to enable public access to export the database via this method. With Deny public access set to YES, you might encounter below error.

PowerShell command to export the database. 

 

 

Command to export the database via PS : 
New-AzSqlDatabaseExport -ResourceGroupName "customer" -ServerName "<your server name>" -DatabaseName "<your db name>" -StorageKeyType "StorageAccessKey" -StorageKey "<your storage access key>" -StorageUri "https://xxxxxxxxxxxxxxxxx.blob.core.windows.net/testc/database01.bacpac"  -AdministratorLogin "<your login name>"

 

 

To check the status of the export request, use the Get-AzSqlDatabaseImportExportStatus cmdlet.

 

 

Get-AzSqlDatabaseImportExportStatus -OperationStatusLink https://management.azure.com/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxx/providers/Microsoft.Sql/locations/northeurope/importExportOperationResults/xxxxxxx-xxxxxxx?api-version=2021-02-01-preview

 

 

Use the Database Operations - Cancel API or the PowerShell Stop-AzSqlDatabaseActivity command to cancel an export request.

 

 

Stop-AzSqlDatabaseActivity -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName -OperationId $Operation.OperationId

 

 

- Please make a note of some of the considerations when using PowerShell method. Also check Azure SQL CLI at: az sql db | Microsoft Docs

- Check out: How to cancel Azure SQL Database Import or Export operation - Microsoft Tech Community

 

• Database Copy 

You can use copy database from Azure portal to copy the database to the different server, then perform the export to Azure Blob, later on you can clean up the copied database 

 

The database export can also be done via automation, more information can be found at Blog: How to automate Export Azure SQL DB to blob storage use Automation account - Microsoft Tech Community

 

Video: SQL Insider Series: Exporting Azure SQL DB BACPAC file to Azure with Azure Automation | Data Exposed - YouTube

 

Additional References:

Export a database to a BACPAC file - Azure SQL Database & Azure SQL Managed Instance | Microsoft Docs

Using Azure Import/Export to transfer data to and from Azure Storage | Microsoft Docs

Configure Azure Storage firewalls and virtual networks | Microsoft Docs

Connectivity settings for Azure SQL Database and Azure Synapse Analytics - Azure SQL Database and Azure Synapse Analytics | Microsoft Docs

Automate native database backup of Azure SQL Managed instance to Azure blob storage - Microsoft Tech Community

 

Disclaimer

Please note that products and options presented in this article are subject to change. This article reflects the database export options available for Azure SQL database in February, 2022.

 

Closing remarks

We hope you find this article helpful. If you have any feedback, please do not hesitate to provide it in the comment section below.

 

Abhishek Shaha (Author)

Ahmed Mahmoud (Co-Author)