Database Backups and High Availability in Azure

How automated backups work

All three major Azure database services (SQL Database, MySQL Flexible Server, PostgreSQL Flexible Server) use the same basic backup approach:

• Full backup — taken once per week. Captures the entire database.
• Differential backup — taken every 12 hours. Captures changes since the last full backup.
• Transaction log backup — taken every 5-12 minutes. Captures every committed transaction.

The transaction log backups are what enable point-in-time restore at nearly any second within the retention window. Azure can reconstruct the database state at any given time by replaying the full backup, then the differential, then the transaction logs up to the target timestamp.

Backups are stored in paired Azure regions by default (for geo-redundant storage). If you’re using LRS or ZRS for backup storage, geo-restore won’t be available.

Backup retention and configuration

# Set backup retention to 14 days for Azure SQL Database
az sql db update \
  --resource-group my-storage-rg \
  --server my-sql-server-demo \
  --name myappdb \
  --backup-storage-redundancy Geo \
  --short-term-retention-days 14

# Set backup retention for MySQL Flexible Server
az mysql flexible-server update \
  --resource-group my-storage-rg \
  --name my-mysql-server-demo \
  --backup-retention 14

# Set backup retention for PostgreSQL Flexible Server
az postgres flexible-server update \
  --resource-group my-storage-rg \
  --name my-postgres-demo \
  --backup-retention 14

Point-in-time restore walkthrough

Imagine this scenario: a developer accidentally runs DELETE FROM orders without a WHERE clause at 14:23 UTC on March 19th. You need to recover the orders table as it was at 14:22 UTC.

The restore process creates a new database server — it doesn’t overwrite the current one. You restore to a new server, verify the data, then either redirect your application to the restored server or copy the missing data back to the original.

# Point-in-time restore for Azure SQL Database
az sql db restore \
  --resource-group my-storage-rg \
  --server my-sql-server-demo \
  --name myappdb-restored-1419 \
  --source-database myappdb \
  --time "2026-03-19T14:19:00Z"

# Point-in-time restore for MySQL Flexible Server
az mysql flexible-server restore \
  --resource-group my-storage-rg \
  --name my-mysql-restored-1419 \
  --source-server my-mysql-server-demo \
  --restore-time "2026-03-19T14:19:00Z"

# Point-in-time restore for PostgreSQL Flexible Server
az postgres flexible-server restore \
  --resource-group my-storage-rg \
  --name my-postgres-restored-1419 \
  --source-server my-postgres-demo \
  --restore-time "2026-03-19T14:19:00Z"

After the restore completes, connect to the restored server with the same credentials and verify the data. When you’re confident the restore is good, redirect your application’s connection string to the restored server, or export/import the affected tables.

High availability options

High availability ensures your database stays accessible when infrastructure fails. Azure offers several HA approaches depending on the service and your requirements:

Zone-redundant HA (within one region)

Azure maintains a synchronous standby replica in a different availability zone. If the primary zone has a failure, Azure automatically fails over to the standby. Failover typically completes in under 2 minutes. Applications experience a brief connection interruption but no data loss.

# Enable zone-redundant HA for PostgreSQL Flexible Server
az postgres flexible-server update \
  --resource-group my-storage-rg \
  --name my-postgres-demo \
  --high-availability ZoneRedundant \
  --standby-zone 2

# Enable zone-redundant HA for MySQL Flexible Server
az mysql flexible-server update \
  --resource-group my-storage-rg \
  --name my-mysql-server-demo \
  --high-availability ZoneRedundant \
  --standby-zone 2

Geo-replication for Azure SQL Database

Azure SQL Database supports active geo-replication — a readable secondary in another region that your application can query. If the primary region goes down, you can fail over to the secondary manually or automatically using Auto-failover groups.

# Create a geo-replica in a secondary region
az sql db replica create \
  --resource-group my-storage-rg \
  --server my-sql-server-demo \
  --name myappdb \
  --partner-server my-sql-server-westus \
  --partner-resource-group my-storage-rg-westus

# Create an auto-failover group (handles failover automatically)
az sql failover-group create \
  --resource-group my-storage-rg \
  --server my-sql-server-demo \
  --name my-failover-group \
  --partner-server my-sql-server-westus \
  --failover-policy Automatic \
  --grace-period 1 \
  --add-db myappdb

Long-term backup retention for compliance

The 35-day maximum retention covers most operational needs but isn’t enough for compliance requirements like HIPAA (6 years) or financial regulations (7+ years). Azure SQL Database supports Long-Term Retention (LTR) for keeping weekly or monthly backups for years.

# Configure long-term retention: keep weekly backups for 4 weeks,
# monthly for 12 months, yearly for 5 years
az sql db ltr-policy set \
  --resource-group my-storage-rg \
  --server my-sql-server-demo \
  --database myappdb \
  --weekly-retention P4W \
  --monthly-retention P12M \
  --yearly-retention P5Y \
  --week-of-year 1

# List available LTR backups
az sql db ltr-backup list \
  --location eastus \
  --server my-sql-server-demo \
  --database myappdb \
  --output table