Different database restore methods in Azure SQL MI

by

In the world of cloud-native databases, “Backup” is only half the story. The true value lies in Restoring the database as to recover data quickly, accurately, and with minimal downtime.

As SQL MI is a PaaS service offered by Microsoft, the SQL MI user database backups are automatically handled by Azure. Choosing the right backup option is important depending on the business requirements.

This post explores the four pillars of recovery in Azure SQL MI: Point-in-Time Restore (PITR), Long-Term Retention (LTR), Geo-Restore (GRS), and the “ultimate” recovery scenario when your SQL MI instance is deleted.

Look for three major factors before deciding which solution is best suitable for your application.

  1. RPO (The affordable data loss time)
  2. Recovery time
  3. Recovered to same instance or different instance and also into same region and subscription or different.

Table of Contents

Point-in-Time Restore (PITR):

PITR is the first and the default restore option available in SQL MI. The default retention period for any database is 7 days.

If you want to restore the database using PITR, by default SQL MI allows you to restore to last 7 days (any time) from the current moment. The retention period can be increased up to 35 days and not beyond that. Azure automatically takes transaction log backups every 5-10 min, differential backups (every 12hrs) and full backup (every week).

Remember that Azure only takes user created database backups only. System databases are never backed and you need to have a plan in place for this.

A. Restoring an Existing Database

The most common scenario is recovering from a data corruption event or incorrect db update:

  • Mechanism: Azure creates a new database on the target instance. It does not overwrite the existing one.
  • Target: You can restore to the same instance or a different instance within the same subscription and region.
  • Workflow: In the Azure Portal, navigate to your database, select Restore, and pick your “Golden Moment” in time within the retention period.

Note: I think, here we need to double the db size temporarily so that we can restore the db to the existing instance with new name compared to the corrupted original db. Once the db is restored, we will delete the old database and rename the newly restored database. Finally, we will scale down the database size.

B. Restoring a Deleted Database

What happens if someone accidentally drops a production database? Azure SQL MI has a built-in “soft-protection” mechanism.

  • Retention: Even if a database is deleted, its backups are preserved until the end of the PITR retention period (typically 7 to 35 days).
  • The “Final Log”: Before a database is deleted, Azure attempts to take a tail log backup and stores it for later restoration.
  • How to Restore: You won’t find this under the active databases list. Instead, go to the Managed Instance level in the portal and look for the Deleted Databases tab under databases.

All the above methods are explained clearly in Automatic, Geo-Redundant Backups – Azure SQL Managed Instance | Microsoft Learn.

There is a table in the above Microsoft documentation which lists all the possibilities of restoring user databases using the three methods which I discussed here.

Long Term Retention (LTR)

While PITR is great for operational recovery, it isn’t designed for compliance. If a regulatory body asks for data from five years ago, PITR can’t help you. This is where Long-Term Retention (LTR) comes in.

Policy Configuration

LTR works by “promoting” certain PITR backups to long-term storage (Azure Blob Storage). You can configure:

  • W (Weekly): The first full backup of the week.
  • M (Monthly): The first full backup of the month.
  • Y (Yearly): A specific week’s backup (e.g., Week 52) held for up to 10 years.

Restoring from LTR

Unlike PITR, LTR backups are full backups only.

  • Speed: Because it involves pulling data from long-term cold storage, the RTO (Recovery Time Objective) might be slightly longer than a standard PITR.
  • Flexibility: You can restore an LTR backup to any Managed Instance in any region, provided you have the necessary permissions.

Geo-Restore (GRS): The Disaster Recovery Powerhouse

In the rare event of an entire Azure region going offline, your local and zone-redundant backups might be inaccessible. Geo-Restore is the default recovery option for regional disasters.

How GRS Works

By default, Azure SQL MI backups are Geo-Redundant (GRS). This means your backups are asynchronously replicated to a “paired” region (e.g., East US to West US).

  • RPO (Recovery Point Objective): Usually within 1 hour. This represents the lag in geo-replication.
  • RTO: Typically, under 12 hours, depending on database size. The restore will be completed mostly within 10 -20 min for db sizes ranging from 1 to 100GB.

When to use GRS?

Use Geo-Restore when the primary region is unavailable and you need to bring your business back online in a secondary region. It creates a new database on a new or existing Managed Instance in the target region.

So, the first option is to choose PITR. Then, GRS for Geo restore. Later LRS for long term restore option.

Conclusion

Understanding the differences between these restore methods is what separates a standard DBA from an Azure Data Architect. For daily operations, PITR is your best friend. For the long haul, LTR keeps the auditors happy. And for the “unthinkable” regional outage, Geo-Restore ensures your business keeps running.

Leave a Comment