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 insights on each one.
- 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:
- 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
- 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).
- 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:
- 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/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
This can also be automated as well, more information can be found at Blog: How to automate Export Azure SQL DB to blob storage use Automation account - Microsoft Tech Community
Additional References:
Using Azure Import/Export to transfer data to and from Azure Storage | Microsoft Docs
Configure Azure Storage firewalls and virtual networks | Microsoft Docs
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)
Posted at https://sl.advdat.com/3gZOqCYhttps://sl.advdat.com/3gZOqCY