Backup. Sigh. We need to back up – and we are doing it. However, it is expensive and complicated, and we are often not sure if our backup is useful the day we really need it.
Something like that is how you describe many IT people’s attitude towards backup.
Let’s say you use Public Cloud, then backup is actually quite easy to manage. That’s in a hands-on day-to-day manner. By using Microsoft Azure as an example Netic shows how easily backup is done in a Public Cloud PaaS world.
What are Azure SQL Databases?
Microsoft offers several options if you would like to run one of your databases on the Azure platform. Azure SQL Database is a so-called “fully managed" solution.
With the Azure SQL Database you do not need to worry about operating systems, patch management or development of hardware infrastructure and backups within this solution are partly automatic. The Azure SQL backup solution enables you to restore a database from a specific time to the same database-server.
The Azure SQL Database is usually fully compatible with Microsoft SQL Server. If you like you can read more about choosing between SQL Server and Azure SQL Databases here.
Before we go through the different options of backup in Azure SQL Databases there has to be set upa database and a database-server in the Azure portal.
How to set up a database and database-server
In our examples we use the Azure portal for setup and configuration, but all functionality is also accessible via Azure CLI and PowerShell.
- The first step is to set up a database and database-server. The Azure portal offers to set up a server in case you do not already have a component to contain the database.
- The database is set up as “backup-demo” and the server ”backupdemo1.database.windows.net” as shown below:
- After a while the new database and database-server will appear under “All Resources”.
Backup option 1: Point-in-time recovery (PITR)
Every database in the Azure portal is set up as a standard one with point-in-time recovery (PITR) activated. This type of backup works by Azure backing up the transaction log every 5-10 minute (the frequency is based on the performance level and database activity). By using the PITR backup option you are able to choose what time, within the retention period, you wish to return to in case you need the backup.
The retention period for PITR depends on service tier and therefore varies accordingly:
|Basic (DTU)||1 week|
|Standard (DTU)||5 weeks|
|Premium (DTU)||5 weeks|
|vCore licens||35 days|
Looking closer to your backup-demo database you can see the information about your oldest restore point and you are able to restore the database from a backup.
If you click “restore” two different sources appear. In this example, we’ll go with point-in-time.
Please notice that you cannot roll back the running database. In case you need the backup you always have to set up a new database based on the backup. It is done this way to ensure that you by accident do not overwrite your production database with a backup.
Backup option 2: Long-term backup retention (LTR)
If you need to keep your backup for more than 7-35 days as offered by PITR, you should define a long-term backup policy that enables you to keep your backups for up to 10 years. Depending on the application, there are several reasons why you would want a longer retention period than 35 days. For instance, you would want it because of regulations within the financial statements act or because you need to test an upgrade from an old version of a database schema to the newest. Long-term backup retention is done in the following way:
- First, click on the “SQL Server” tab in the Azure portal. Please notice that this is done by using the SQL server you set up with the database and not the database itself.
- Choose the database(s) you wish to configure with a long-term policy.
- Here you can adjust how far back in time you wish to go with PITR. Typically, there is no need to reduce the standard period. How long time you wish for your long-term retention to cover naturally depends on the data and how you use them. A reasonable starting point for many is to do weekly backups, which are saved for a year, and then monthly backups for the next five years. A yearly backup is rarely useful especially not if you have monthly backups from the last five years.
Backups with long-term retention are restored the same way as PITR backups. The only difference is that you click long-term backup retention as source instead and then choose the specific backup you wish to restore.
Even though the Azure SQL Database automatically generates backups you are neither protected against safety gaps in your applications nor users who delete/edit the wrong data.
This means that you still rely on a good backup strategy even if you have a cloud-based infrastructure. In this article you can read about the 6 most important considerations when choosing backup of your MS Azure application.
Please contact us if you have any questions concerning strategy, choice of technology or simply just need practical guidance with your backup options.