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
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='[email protected]' 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 [email protected]$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:
Great article. #1Can we use windows auth ? #2. How to backup to a recovery services vault.Ranga Narasimhan