You cannot think of a SQL Server Administration or any kind of relational and non-relational database administration without the two magic words: backup and restore. Backing up a database is a part of an essential operation that can prevent disaster scenarios, like accidental corruption or deletion, and it’s often underlooked when working in a development or staging environment.
If you are working as a DevOps Cloud Architect, a part of your responsibilities you’ll have to came up with a good backup and restore plan. This includes having backups on a cloud storage service, for example: Amazon S3, Azure Blob storage. You might also want to add a cold storage solution for long-term retention like Amazon Glacier and Azure Cool Storage and most likely an on-premises storage solution.
In this post, I will focus on how to backup and restore an Azure SQL Database using the Azure CLI, as a part of the Azure SQL Database Administration series.
Azure SQL Database backups
Azure SQL Database automatically create database backups that can be kept from 7 up to 35 days, which is called a Backup retention period. Azure uses RA-GRS type of storage blobs to ensure redundancy in case if a primary region goes down. There is also a long-term retention policy for storing backups up to 10 years on a single database and elastic pools.
Full backups are made every week, differential backups every 12 hours, and the most important ones, the transaction log backups are made every 5-10 minutes.
Azure SQL Database restore
You can use the backups to restore a database to a point-in-time, to the time it was deleted, or from a specific long-term backup, if only the database has been configured with a long-term retention policy, all within the retention period. Also, you can create a new database to another geographical location from a backup.
Prerequisites
- Azure subscription
- Azure SQL database
- Azure Storage account
Backup an Azure SQL Database
Step 1. Open Terminal and login to the Azure Portal.
az login
It will open a new window using the default browser where you will be prompted for email and password.
Step 2. There are two ways to export a databases, using an SAS key or using a storage account key. I’ll use a storage account key just for the purpose of this demo. Let’s store the storage account key using a variable named accountKey, because we’ll need it for later.
accountKey=`az storage account show-connection-string --name "devcoopsdbaccount" --resource-group "db-backups-storage-rg" --query connectionString --output tsv | grep -oP 'AccountKey=\K.+'`
Step 3. Create a backup file name using a timestamp using a variable named dbbackupFileName. For example:
now=$(date -u +"%Y-%m-%d-%H-%M")
dbbackupFileName"dbbackup-$now
Step 4. Backup the database.
az sql db export --server "devcoopsSQLserver" --name "dbName" --resource-group "db-backups-storage-rg" --admin-user "admin" --admin-password "<insert_password_here>" --storage-key-type StorageAccessKey --storage-key $accountKey --storage-uri "https://devcoopsdbaccount.blob.core.windows.net/db-backups/$dbbackupFileName.bacpac"
Restore an Azure SQL database
Step 5. Import the database.
az sql db import --server "devcoopsSQLserver" --name "dbName" --resource-group "db-backups-storage-rg" --admin-user "admin" --admin-password "<insert_password_here>" --storage-key-type StorageAccessKey --storage-key $accountKey --storage-uri "https://devcoopsdbaccount.blob.core.windows.net/db-backups/$dbbackupFileName.bacpac"
Conclusion
In this post I have described a plain example on how you can backup and restore a database from the Azure CLI. It can be quite useful in situations when you immediately need a backup. In a real world scenario, always use automation which is manageable using the Azure Portal, PowerShell or REST API.
Feel free to leave a comment below and if you find this tutorial useful, follow our official channel on Telegram.