How to export the Azure SQL database using BACPAC

How to export the Azure SQL database using BACPAC

We have created a single database with sample Adventure Works DB in one of previous articles. We also have created single database empty in Azure SQL and then added schema to it using SSMS.

In this article, we will see how can we export the data using BACPAC.

If you don’t have an Azure subscription, create a free account.

For following all the steps in this article, you need to sign in to the Azure portal.

What is BACPAC ?

A BACPAC is a zip file with extension .bacpac. It contains schema as well as data of the database. The Schema is represented in the form of XML ( same as in DACPAC). The data is represented in the form of JSON.

For detailed information, please check my article: What is the difference between DACPAC and BACPAC ?

Using Azure Portal

Sign in to Azure Portal and then navigate to the SQL database you have already created. Then click on the Export button from the overview blade.

This will bring you to Export database page where you need to specify:

  • File name for the bacpac file
  • Subscription
  • Storage, which is name of container in a storage account where the bacpac file will be saved.
  • SQL Server Admin username
  • And admin user’s password to connect to server.

Clicking on storage will take you to the page where list of storage accounts will be shown. Select any existing storage account. Please note that you cannot create a new storage account from this page. Although there is a link, but it seems to be disabled.

Then you will be asked to select an existing container where bacpac file will be saved. Alternatively you can also create new container.

Then you can click on OK to submit the request of Export.

Once the request is submitted, you can navigate to Import / Export history panel of the respective database server to check the progress of the bacpac creation. As my database was very small, the job completed in almost 4 seconds.

You can see the start time and end time by clicking on the entry from below grid.

Now you can find the bacpac file generated in the provided container.

PowerShell

Below command shows how to issue export request to Azure using Azure PowerShell.

Use the New-AzSqlDatabaseExport cmdlet to submit an export database request to the Azure SQL Database service. The completion of this request may take variable time depending on size of your database.

$exportRequest = New-AzSqlDatabaseExport -ResourceGroupName $ResourceGroupName -ServerName $ServerName `
  -DatabaseName $DatabaseName -StorageKeytype $StorageKeytype -StorageKey $StorageKey -StorageUri $BacpacUri `
  -AdministratorLogin $creds.UserName -AdministratorLoginPassword $creds.Password

Azure CLI

Below commands shows how to export a bacpac file using Azure CLI. We can use az sql db export command for this purpose.

# using Shared Access Key
az sql db export -s myserver -n mydatabase -g mygroup -p password -u login \
    --storage-key "?sr=b&sp=rw&se=2018-01-01T00%3A00%3A00Z&sig=mysignature&sv=2015-07-08" \
    --storage-key-type SharedAccessKey \
    --storage-uri https://myAccountName.blob.core.windows.net/myContainer/myBacpac.bacpac

# using storage access key
az sql db export -s myserver -n mydatabase -g mygroup -p password -u login \
    --storage-key MYKEY== --storage-key-type StorageAccessKey \
    --storage-uri https://myAccountName.blob.core.windows.net/myContainer/myBacpac.bacpac

I hope this article was helpful. Let me know your thoughts.

Leave a Reply