A script for exporting transactionally consistent bacpac from Azure SQL Database

As stated by official documentation, exporting to Bacpac is a process not guaranteed to be transactionally consistent. To have a consistent bacpac you can either stop all writing activities on the database or export the bacpac from a copy - not used by others - of the database. The former is not acceptable in 99% of the case so I focused on the latter.

My target was to create a script, to be scheduled on cron on Linux, performing these operations:

Create a transactionally consistent copy of the database

Export to bacpac file on a Storage Account

Copy the file to local Linux file system

Remove the temporary database

The last point is very important to reduce the cost. See "Pricing cosiderations".

The script works using Azure CLI commands to interact with Azure infrastructure:

az login for logging in using a service-principal

az sql db copy for creating a transactionally consistent copy

az sql db export for exporting database to bacapc file

az storage blob download for downloading to local hard-disk

Look at the documentation for more details about the commands, how to install Azure CLI, creating a service-principal, etc.

The script requires many parameters to work: server name, sql username and password, storage account, key, etc. Pay particular attention to the 2 database names:

mydatabase='originalDB...'

mytempdatabase='tempDBbackup...'

*** Do Not Swap Them: the script would delete your original database ***

Pricing considerations

The temporary database copy is a normal database from the Azure platform point of view. It is created with the same size of the original and billed accordingly. As far as I know, Azure Sql databases are billed on per-hours basis. So, it's important to remove the temporary database when not needed any more, that is after creating the bacpac file.

The copy and export steps require less than an hour, if the database is not too big. If you run the script once per day, you will incur in about 5% increase of Sql Azure costs (1 extra hour every 24 hours, 5%).

Full script

https://github.com/fhtino/azure/blob/master/sql_bacpac/sqlazure_create_bacpac.sh