Backup and Restore a SQL Database with the Azure CLI

Last time in my series on the Azure CLI, we saw how to create a SQL Database and connect it to a web app. But lets see now how we can automate backing up to a .bacpac file in blob storage, and how we can restore from a .bacpac.

Gathering Backup Parameters

To perform a backup we need details of how to connect to our SQL database and to the target storage account.

I’ll assume we’ve already got some variables set up containing the SQL database connection information. Here’s the values we used in our last demo:

sqlServerName="azclidemo"
sqlServerUsername="mheath"
sqlServerPassword='!SecureP@assword1'
databaseName="SnippetsDatabase"

We also need to get hold of some connection details of the target storage account. This may well be in another resource group (and its a good idea if it is, as that allows you to easily delete the database independently of its backups).

We need just the AccountKey part of the connection string to the storage account, and the Azure CLI only gives us the whole connection string with az storage account show-connection-string, so in bash we can pipe that into grep to pick out just the AccountKey part at the end (obviously if you’re using PowerShell you can use the string manipulation / regex features it provides instead):

storageAccount="assetswe"
storageResourceGroup="SharedAssets"
storageConnectionString=`az storage account show-connection-string -n $storageAccount -g $storageResourceGroup --query connectionString -o tsv`

# extract just the storage key
storageKey=`echo $storageConnectionString | grep -oP "AccountKey=\K.+"`

And it would be a good idea if we gave our backup a unique filename, which we can do with a bit more bash:

now=$(date +"%Y-%m-%d-%H-%M")
backupFileName="backup-$now.bacpac"

Performing the Database Backup

Now we’ve gathered all the necessary information, we can perform the backup with a single az sql db export command, providing details of how to connect to both the database to be backed up, and the storage account to back up to. Here we’re backing up into a bacpacs container in our storage account:

az sql db export -s $sqlServerName -n $databaseName -g $resourceGroup \
-u $sqlServerUsername -p $sqlServerPassword \
--storage-key-type StorageAccessKey --storage-key $storageKey \
--storage-uri "https://$storageAccount.blob.core.windows.net/bacpacs/$backupFileName"

If you’re wondering whether it’s possible to use SAS tokens instead of providing the storage account storage key, the answer is yes you can, although I had troubles when I used this command from PowerShell not interpreting the SAS token correctly. So I found the storage key option more reliable.

Restoring a Backup

One thing that may take you by surprise is that you can’t just restore over the top of an existing database. You have to create a new empty database and restore into that. Presumably this is just to stop people accidentally destroying live data.

It’s easy enough to create a new database on the same server as the original:

databaseName2="SnippetsDatabase2"
az sql db create -g $resourceGroup -s $sqlServerName -n $databaseName2 \
--service-objective Basic

And now we can use the az sql db import command to import from our .bacpac file into the new database. The arguments are almost identical to the export command:

az sql db import -s $sqlServerName -n $databaseName2 -g $resourceGroup \
-u $sqlServerUsername -p $sqlServerPassword \
--storage-key-type StorageAccessKey --storage-key $storageKey \
--storage-uri "https://$storageAccount.blob.core.windows.net/bacpacs/$backupFileName"

Now we can generate a new connection string, and update our web app to point at the restored database:

connectionString2="Server=tcp:$sqlServerName.database.windows.net;Database=$databaseName2;User ID=$sqlServerUsername@$sqlServerName;Password=$sqlServerPassword;Trusted_Connection=False;Encrypt=True;"

az webapp config connection-string set \
    -n $appName -g $resourceGroup \
    --settings "SnippetsContext=$connectionString2" \
    --connection-string-type SQLAzure

Taking it Further

In this post we saw how to create and restore our own .bacpac files on demand, but you can also take advantage of the automatic backups that Azure SQL Database creates on your behalf. How far back these backups go depends on the pricing tier you choose, but if you want to restore from one of these, take a look at the az sql db restore command.

Previous installments in my Azure CLI tutorial series:

Gathering Backup Parameters

Performing the Database Backup

Restoring a Backup

Taking it Further

Comments