How to Export Azure SQL Database

In this azure tutorial, we will discuss How to Export Azure SQL Database. Apart from this, we will also discuss on below topics

  • Azure storage account
  • How to create a storage account in Azure
  • Create a storage account in Azure using PowerShell
  • How to Delete a storage account using PowerShell
  • Export Azure SQL Database using PowerShell
  • Cancel Azure SQL database export

How to Export Azure SQL Database

Let’s discuss here, how to export the Azure SQL database from the Azure portal. In order to export the Azure SQL database from the Azure portal, you need to save the SQL database into a BACPAC file.

For this, you need to create a storage account first. Before that, We will see what is Azure storage account and the steps to create a storage account and container in the Azure portal.

Azure storage account

An Azure storage account is something that contains all the Azure storage data objects. The Azure storage data objects are like blobs, files, queues, tables, and disks, etc.

Once you store the data inside the Azure storage account that becomes more secure, scalable, durable  and highly available.

Azure storage account helps you providing a unique namespace for your Azure storage data and another important thing is that with the help of the unique namespace your Azure storage data is accessible from anywhere in the world over HTTP or HTTPS.

There are different types of storage accounts provided by Microsoft like General-purpose v2 accounts, General-purpose v1 accounts, BlockBlobStorage accounts, FileStorage accounts, BlobStorage accounts, etc.

How to create a storage account in Azure

Step-1: Login to the Azure portal (https://portal.azure.com/)

Step-2: Search for the Storage accounts (classic) and select it.

how to create a storage account in Azure

Step-3: Click on the + Add button from the Storage accounts (classic) window.

Create a storage account in Azure

Step-4: On the Create storage account window, Provide the below details in the Basics tab.

  • Subscription: Select your subscription details.
  • Resource group: Select your existing resource group or you can create a new one by clicking on the Create new link.
  • Storage account name: Provide a valid name for your storage account.
  • Location: Select the location you belong to.
  • Performance: Select the Standard option.
  • Account kind: You can use the default option or you can choose the appropriate option.
  • Replication: You can use the default option or you can choose the appropriate option.
  • Access tier(default): You can select the default Hot option or Cool option.

Click on the Next : Networking > button to go the Networking tab.

How to create a storage account Azure
How to create a storage account Azure portal

On the networking tab keep the default option as it is. Click on the Next : Tags > button to go to the Tags tab

storage account in Azure

On the Tags tab no need to change anything click on the Next : Review + create > button.

Storage account in Azure portal

Click on the Create button to create the storage account.

Create storage account

Now you can see it is deployed successfully

Create storage account in Azure

Now to export the Azure SQL database from the Azure portal, click on your database that you want to export and then click on the Export button.

How to Export Azure SQL Database from Azure Portal

From the Export database window, Click on the Storage (Configure required settings) link

Export Azure SQL Database from Azure Portal

From the Storage accounts window, select the storage account you created above.

how to cofigure Export Azure SQL Database from Azure portal

Now it will ask to create a container since you do not have a container created as of now. To create a container click on the + Container button.

Configure Export Azure SQL Database from Azure portal

On the New container window, provide a valid name for your container and keep the Public access level as it is. Now click on the Create button to create the container.

Configure Export Azure SQL Database from Azure

You can see the container created successfully. Click on the Select button.

How to Export Azure SQL Database from Azure

On the Export database window provide the below details

File name: provide a file name or else you can use the default one.

Subscription: Your subscription details will load by default. Cross check if it is correct or else you can choose the correct one.

Storage: You need to configure and select the container as per the above mentioned steps.

Server admin login: Provide the username for the admin login.

Password: Provide the password for the admin login.

Click on the Ok button

How to Export Azure SQL Database using Azure portal

Now you can see the Request submitted to export database. You can check the Progress on the Import/Export History tab.

export Azure SQL Database using Azure portal

If you want to check the bacpac file created, click on the container just created, you can able to see a file with bacpac extension.

To download the bacpac file, you can click on the … (three dots) button or else you can right-click on the file and then select the download option from the popup.

Azure SQL Database export using Azure portal

Verify the bacpac file downloaded successfully in your local machine. Later on, you can import it back to Azure or to a SQL Server on-premises installation

Azure SQL database export option

How to create a storage account in Azure using PowerShell

We saw above How to create a storage account in Azure using the Azure portal. Now let’s discuss how can we create a storage account in Azure using PowerShell cmdlet.

You can use the below PowerShell cmdlet to create a storage account using PowerShell.

$resourceGroup = 'newresgroup'
$location = 'East US'

New-AzStorageAccount -ResourceGroupName $resourceGroup `
  -Name mynewstroageaccount890 `
  -Location $location `
  -SkuName Standard_RAGRS `
  -Kind StorageV2

Name should be a unique name with all lower case letter.

How to create a storage account in Azure using PowerShell

The SkuName parameter can be anything from below based on your business needs

Locally redundant storage as Standard_LRS, Zone-redundant storage (ZRS) as the Standard_ZRS, Geo-redundant storage (GRS) as the Standard_GRS, Read-access geo-redundant storage (GRS) as the Standard_RAGRS, etc

Now you can login to the Azure portal and verify if the storage account got created

create a storage account in Azure using PowerShell

How to Delete a storage account using PowerShell

You can delete a storage account using PowerShell which deletes the entire account with all data in the account.

You can use the below cmdlet to delete the Storage account.

Remove-AzStorageAccount -Name mynewstroageaccount890 -ResourceGroupName newresgroup

Once you will run the above cmdlet, you will get the below Confirm popup, click on the Yes button to confirm for deletion.

How to Delete a storage account using PowerShell

Now you can see the storage account got deleted and it is no longer available in the Azure portal.

Delete a storage account using PowerShell

How to Export Azure SQL Database using PowerShell

We can use the New-AzSqlDatabaseExport command to submit the Export request to the Azure SQL database service.

We can use the below cmdlet. Set all the variable values as per your database configuration.

$mysubscriptionId = "XXXXX"
$myserverAdmin = 'XXXX'
$myserverPassword = 'XXXX'
$mysecurePassword = ConvertTo-SecureString $myserverPassword -AsPlainText -Force
$creds = New-Object System.Management.Automation.PSCredential($myserverAdmin, $mysecurePassword)
$MyResourceGroupName = 'Your resource group name'
$MyServerName = 'Your server name'
$MyDatabaseName = 'Your database name'
$MyStorageKeytype = 'Your storage key type'
$MyStorageKey = 'YourStorageAccountKey'
$bacpacFilename = $DatabaseName + (Get-Date).ToString("yyyyMMddHHmm") + ".bacpac"
$BaseStorageUri = "https://XXXXX.blob.core.windows.net/XXXXX"
$BacpacUri = $BaseStorageUri + $bacpacFilename

$exportRequest = New-AzSqlDatabaseExport -ResourceGroupName $MyResourceGroupName -ServerName $MyServerName `
  -DatabaseName $MyDatabaseName -StorageKeytype $MyStorageKeytype -StorageKey $MyStorageKey -StorageUri $BacpacUri `
  -AdministratorLogin $creds.UserName -AdministratorLoginPassword $creds.Password
How to Export Azure SQL Database using PowerShell

How to cancel Azure SQL database export

In case the the export process is getting stuck in between or due to some reason you want to cancel the Azure SQL database export export operation, you need to do the below things

Probably what you need to do is delete the storage account/container where it is exporting the data. Then after

We can set Allow Azure Services to No. We can get this option under Manage Allowed IP Addresses page.

You can try out in this way if still you are facing the same issue then you need to talk to the Azure support guys.

You may like following Azure tutorials:

Conclusion

This tutorial, we learned the below things:

  • How to Export Azure SQL Database
  • Azure storage account
  • How to create a storage account in Azure
  • Create a storage account in Azure using PowerShell
  • How to Delete a storage account using PowerShell
  • Export Azure SQL Database using PowerShell
  • How to cancel Azure SQL database export

Leave a Comment