Microsoft Azure Documentation

Azure SQL Database Backup and Restore

This section provides comprehensive reference information on backup and restore capabilities for Azure SQL Database. Understanding these features is crucial for data protection, disaster recovery, and managing your database lifecycle effectively.

Key Takeaway: Azure SQL Database provides automatic backups with point-in-time restore (PITR) capabilities, allowing you to recover your database to any point in time within the defined retention period.

Automatic Backups

Azure SQL Database automatically performs full, differential, and transaction log backups for all databases. These backups are stored in Azure Blob Storage, which is geo-replicated by default for disaster recovery purposes.

  • Full Backups: Taken weekly.
  • Differential Backups: Taken approximately every 12 to 24 hours.
  • Transaction Log Backups: Taken every 5 to 10 minutes. The frequency depends on the database size and activity.

The automatic backup process ensures high availability and durability for your data without requiring manual intervention.

Backup Retention

The default retention period for automatic backups varies based on the service tier:

  • General Purpose & Business Critical: Default 7 days, configurable up to 35 days.
  • Hyperscale: Default 7 days, configurable up to 35 days.
  • Basic & Standard: Default 7 days, configurable up to 35 days.

Long-Term Retention (LTR) is available for General Purpose, Business Critical, and Hyperscale tiers, allowing you to store backups for longer periods (up to 10 years).

Point-in-Time Restore (PITR)

Point-in-Time Restore allows you to restore a database to a specific point in time within the defined backup retention period. This is invaluable for recovering from accidental data deletion, data corruption, or applying schema changes.

Restoring via Azure Portal

  1. Navigate to your SQL server in the Azure portal.
  2. Under "Data management," select "Backups."
  3. Choose the database you want to restore and click "Restore."
  4. Select the desired "Restore point" (date and time).
  5. Configure the new database name, service tier, and other settings.
  6. Click "Review + create" and then "Create."

The restore operation creates a new database, leaving the original database intact.

Restoring via Azure CLI

Use the az sql db restore command:


az sql db restore \
    --dest-name <new-database-name> \
    --ids <resource-id-of-original-database> \
    --time <YYYY-MM-DDTHH:MM:SSZ> \
    --resource-group <resource-group-name> \
    --server <sql-server-name>

Replace placeholders with your actual values.

Restoring via Azure PowerShell

Use the Restore-AzSqlDatabase cmdlet:


Restore-AzSqlDatabase `
    -FromPointInTimeBackup `
    -Name <new-database-name> `
    -PointInTime <YYYY-MM-DDTHH:MM:SSZ> `
    -ResourceId <resource-id-of-original-database> `
    -ResourceGroupName <resource-group-name> `
    -ServerName <sql-server-name>

Replace placeholders with your actual values.

Geo-Restore

Geo-restore is a disaster recovery feature that allows you to restore a database from a geo-replicated backup to any Azure region. This is essential for recovering your database in a different region if your primary region becomes unavailable.

  • Geo-restore requires a recovery point objective (RPO) of 1 hour for databases with geo-replication enabled.
  • The process involves restoring from the secondary geo-replicated backup.

To perform a geo-restore, you typically initiate a restore operation from the geo-replicated backups available in the secondary region.

Long-Term Retention (LTR)

Long-Term Retention allows you to configure backups to be stored for extended periods, up to 10 years, in geographically redundant storage. This is often a requirement for regulatory compliance or archival purposes.

  • LTR can be configured at the server or database level.
  • You define backup policies specifying the retention period for full backups (e.g., weekly, monthly, yearly).

LTR backups are stored separately from the standard PITR backups.

Important Note: While Azure SQL Database manages the backup infrastructure, it's your responsibility to understand RPO (Recovery Point Objective) and RTO (Recovery Time Objective) requirements and configure backup retention and disaster recovery strategies accordingly.

Backup Management and Monitoring

You can monitor backup status, configure retention policies, and manage LTR backups through the Azure portal, Azure CLI, and Azure PowerShell. Key metrics to watch include backup completion times and storage utilization.