Backup your Azure SQL Database

This is a part of series “Stairway to being an Azure SQL DBA“, where I will be covering all the topics that an Azure SQL DBA should know about.

In our last blog you saw how Azure maintains SQL backups and what are different options available to increasing the PITR or long term backups.

Now let’s assume a scenario where you need to need to have the data stored in your Azure SQL database to be moved to the on-prem environment.

There are automated backups but as they are managed by Azure you don’t have access to it.

There isn’t any option to take backup of the Azure SQL Database.

And if you try to manually run the backup command below is the error that you get.

In this case the way to export the data out from your Azure SQL Database is a BACPAC file.

A BACPAC file is a ZIP file with an extension of BACPAC containing the metadata and data from the database. A BACPAC file can be stored in Azure Blob storage or in local storage in an on-premises location and later imported back into Azure SQL Database, Azure SQL Managed Instance, or a SQL Server instance. – ms docs

There are some limitation with it, which can be checked here.

There are different methods for creating a BACPAC file they are also listed on the above article only. But to give a quick snippet where you can see them.

It is available from the portal itself. When you click on Export it will ask for the location which should be a Storage account.

2. When connected to the Azure SQL Database in SSMS, you can right click on the database go to Tasks and choose Export Data-Tier Application.

Once you have the BACPAC file you can import it in any SQL Server.

That is all for today, in the next blog we’ll see the different HA options available in Azure SQL Database.