Export Azure SQL Database | Advanced Scenarios

Published Feb 21 2022 04:31 AM 3,252 Views
Microsoft

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 Az...

 

  • 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:

Ahmed_S_Mahmoud_0-1644234395285.png

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/...

 

  • 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 Comm... 

 

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.
abhishekshaha_0-1645200192190.png

 

Select the location to store the BACPAC file 

abhishekshaha_2-1645200707114.png

 

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.

 

abhishekshaha_4-1645200829384.png

 

More information at blog:

Using data-tier applications (BACPAC) to migrate a database from Managed Instance to SQL Server - Mi...

 

  • 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.

Ahmed_S_Mahmoud_1-1644235189436.png

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/71095335-fafc-4b21-a692-5a7723553133/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

 

  • 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 Communit...

 

VideoSQL Insider Series: Exporting Azure SQL DB BACPAC file to Azure with Azure Automation | Data Exposed...

 

Additional References:

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

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 Az...

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

 

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)

1 Comment
Version history
Last update:
‎Jun 10 2022 02:15 AM
Updated by: