Configuring Azure SQL Long-term Backup Retention

Backup is the most important case for database administration because it provides business continuity solutions for high availability and disaster recovery. For this reason, Azure SQL database (PaaS: platform as a service) can make a backup automatically and restore it at a point in time. However, the retention period of backup files changes according to the service tiers.

This chart illustrates retention periods of Azure SQL service tiers backup.

According to the application or business requirements, we can downgrade or upgrade the service tiers because they may affect the cost of Azure bills. In this case, the retention periods of backup files will be changed. If you downgrade your service tiers to a basic tier, you will lose the backups that are out of the basic tier retention period. If you upgrade your service tier, your Azure SQL will keep existing backups until we set a longer retention period.

Azure SQL database uses the full recovery model. In this way, we can restore our database at a point time. Azure SQL makes a backup within these periods. Weekly full backups, differential backups that usually take a few hours, and log backups will be performed for about 5-10 minutes. The first full backup will be made immediately after the database is created.

Long-term Backup Retention

In some cases, we need a much longer retention period for the backup availability. To meet this requirement, Azure SQL suggests using the Long-term Backup Retention feature. It allows us to store full backups for an extended period of time up to 10 years in geo-redundant storage designed to provide at least 99.99999999999999% (16 9’s) durability of objects over a given year by replicating your data to a secondary region that is located hundreds of miles away from the primary region.

Now, we will configure long-term backup retention.

1. In the Azure Portal, select SQL Servers and then click Long-term backup retention.
   
2. On the Configure policies tab, select your database you want to configure retention for and click Configure retention.
   
   This tab is a little bit complicated because it requires from you to specify the periods the backup files will be kept and expired. First, we will set a weekly backup policy for the long-term retention period.
   
   This policy defines that every weekly backup will be kept for 5 weeks.
   The diagram below illustrates how long database backups will be retained.
   
   The above image defines that we set 2 years to back up every 15 weeks of the year. A backup we make on 5/16/2018 will expire on 5/13/2020. When we navigate to Activity log and click Apply, we will receive some information on log activity including long-term backup retention activities.

How to restore Azure SQL to point time?

To begin with, create a little demo.

1. Navigate to SQL databases, click Query editor (preview) and then log into the database.
2. Create a demo table in the query editor.
   Note: Query editor is a tool that allows us to write and execute simple queries in Azure Portal.

   CREATE TABLE TestAzureRestore(ID int IDENTITY(1,1) PRIMARY KEY ,
   Dt DATETIME)
   

   

3. Populate a test table with some data using the query below. It will insert a record with a one-minute interval.

   DECLARE @K AS INT =1
   WHILE @K<=5
   BEGIN
   INSERT INTO TestAzureRestore
   VALUES(GETDATE())
   WAITFOR DELAY '00:00:10'
   SET @K=@K+1
   END
   

   

4. Restore the database before showing the record time.

   SELECT * FROM TestAzureRestore ORDER BY DT ASC

   

5. Navigate to Overview and then click Restore.
   
   At this step, we cannot restore the backup to the original database. Instead, we have to restore a new database and then rename it.
   
   Once done, a message about database recovery will appear in the notification tab. The restore time may impact the following values:
   • Database size
   • The number of transaction logs
   • The database performance level

   Performance level: Azure SQL offers a different type of service tier including different performance levels that may affect Azure SQL performance and provide different storage sizes. For example, Premium service levels include Premium P1, Premium P2, Premium P6 etc.

   • The network bandwidth if the recovery process refers to a different region

   Microsoft notes that Azure Cloud services may cause a long time for a restore process to be performed.

   • The number of concurrent restore requests being processed in the target region.

   However, there is an interesting thing that was stated by Microsoft. They said that “Most databases restore complete within 12 hours.”
   

6. We created a new database that can be found under the SQL databases panel.
   
7. Check the records in the database. I would like to add that we cannot see the records that were added after the recovery time.

   SELECT * FROM TestAzureRestore ORDER BY DT ASC

   

In this article, we explored Azure SQL automatic backup, long-term backup retention, and the way to restore Azure SQL database. Azure SQL offers the great feature – the long-term backup retention that may restore your backup for an extended period of time up to 10 years. It is an exceptional feature and you don’t have to take care of the backup maintenance plans and backup process. On the other hand, restore options have some limitations. You cannot estimate the exact recovery time because it depends on the Azure services. Microsoft recommends avoiding this long-time estimated recovery time using Failover groups and active geo-replication.

In brief, Azure SQL automatic backup and long-term backup retention features have both advantages and disadvantages. Depending on your business or application requirements, you can decide how to use these features and how to gain much benefit.

References

Store Azure SQL Database backups for up to 10 years

Learn about automatic SQL Database backups

SQL Database Query Editor available in Azure Portal