Privacy and cookie policy


2017-11-09 18:02

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




DISCLAIMER: Content and opinions are my own. None of the ideas expressed in this web-site are shared, supported, or endorsed in any manner by my current or former employers. Nothing here should be taken seriously and you understand and accept that you can use any suggestions, ideas, techincal solutions on this web-site only at your own risk. All trademarks are property of their respective owners.