Design for Azure SQL Backup and Recovery

Azure SQL Database, our Platform as a Service (PaaS) offering, leverages robust backup solutions to safeguard your data. This guide explains the various backup types available for Azure SQL Database, outlines recovery scenarios, and demonstrates how to configure backup settings in the Azure portal.

Backup Types in Azure SQL Database

Azure SQL Database categorizes backups into three main types:

1. Full Backup
   Performed weekly, this backup includes both data and transaction log files. It serves as the primary data restore point.

2. Differential Backup
   Conducted every 12 to 24 hours, this backup captures the changes (delta) since the last full backup, including both data and transaction log files.

3. Transaction Log Backup
   Executed every 5 to 10 minutes, this backup includes only transaction log files, enabling administrators to revert to a previous log position if necessary.

Use Cases for SQL Database Backup

Azure SQL Database backups support a variety of critical use cases:

• Point-in-Time Restore (PITR):
  Restore your database to any specific point within the configured retention period. During this process, a new database is created on the same server with a different name, ensuring that the original database remains intact.

  Note

  The restored database can be deleted later if it's no longer necessary.

• Restoring a Deleted Database:
  Recover a accidentally deleted database to its state at the time of deletion within the configured retention period. The restoration must occur on the same server or managed instance where the database was originally housed.

• Geo-Restore:
  If the primary region becomes unavailable, use the geo-restore option to create a new database on an existing server or managed instance in any Azure region.

• Long-Term Backup Restoration:
  For retention needs beyond the default 35 days, configure Long-Term Retention (LTR) backups to store data for up to 10 years. This capability enables restoration to older versions of your database.

Configuring Backup for Your SQL Database

Follow these steps in the Azure portal to configure your SQL Database backups:

1. Navigate to SQL Databases:
   Open the database that you used earlier for migration.

2. Access the Server:
   Click on the server that hosts your database.

3. View Backups:
   In the server settings, select "Backups." Here you will see entries for backups, including the most recent backup (e.g., taken at 09:00 UTC).

4. Manage Deleted Backups:
   The portal allows you to view deleted backup entries and initiate restore operations as necessary.

5. Adjust Retention Policies:
   Click on the current default retention policy to modify the following settings:

   • Point-in-time restore (PITR) retention period (e.g., 7 days)
   • Differential backup duration
   • Weekly, monthly, and yearly long-term retention settings

These configurations also apply to SQL Managed Instance, ensuring a consistent backup strategy across all your Azure SQL deployments.

Up next, we will explore disaster recovery strategies, focusing on Azure Site Recovery, which complements the backup mechanisms described here.

Additional Resources

• Azure SQL Database Documentation
• Azure Site Recovery Overview
• Understanding Backups and Recovery for SQL Database

Stay informed and secure your data with these comprehensive backup and recovery strategies for Azure SQL Database.