Azure SQL Database Backup: A Complete Guide—
Backing up your databases is essential for protecting data, meeting recovery objectives, and maintaining business continuity. Azure SQL Database provides built-in backup mechanisms, flexible retention options, and tools to restore data in a variety of failure scenarios. This guide explains backup types, default behaviors, configuration options, restore strategies, cost considerations, and best practices.
What Azure SQL Database backs up automatically
Azure SQL Database automatically performs backups for most database tiers. Automated backups include full, differential, and transaction log backups and are stored in Azure Blob Storage managed by the platform. These backups are used for point-in-time restore (PITR) and long-term retention (LTR) if configured.
- Full backups: taken periodically (typically weekly) and capture the entire database.
- Differential backups: captured more frequently (typically every few hours) and store changes since the last full backup.
- Transaction log backups: captured frequently (typically every 5–10 minutes) to enable PITR to any point in time within the retention window.
Backup retention and default windows
- Point-in-time Restore (PITR) retention depends on the service tier:
- Basic: 7 days
- Standard: 35 days
- Premium / Business Critical / Hyperscale: 35 days (can vary by region or service level)
- Long-Term Retention (LTR): configurable policies allow you to keep weekly, monthly, and yearly full backups for up to 10 years.
Backup types and use cases
- Point-in-time Restore (PITR)
- Use to recover from accidental data modifications or logical corruption by restoring the database to a specific time within the retention window.
- Long-Term Retention (LTR)
- Use for compliance and archival purposes where you must retain backups for months or years.
- Geo-restore / Geo-redundant backups
- For disaster recovery across regions, Azure stores backups geo-redundantly when the server is configured for geo-redundant storage, enabling restore in another region if the primary region fails.
- Copy-only backups (via Export)
- Use a BACPAC export for moving or archiving schema and data; not ideal for PITR or LTR replacements.
How automated backups work
The platform schedules full, differential, and transaction log backups. Transaction log backups enable PITR within the retention window by replaying logs onto the latest full/differential backups. Backups are encrypted at rest and in transit. The backup storage is managed by Azure, so you do not need to configure storage accounts for standard automated backups.
Configuring long-term retention (LTR)
- In the Azure portal, navigate to your SQL server or logical server and open the target database.
- Under “Manage backups” or “Backups”, select “Configure retention” / “Long-term retention”.
- Define policies for weekly, monthly, and yearly retention, specifying the number of copies to keep and their schedule.
- Save the policy — Azure will copy full backups to the LTR store according to the policy.
Note: LTR uses full backups only. When you set LTR, copies of full backups are stored independently from the automated PITR backups.
Point-in-time restore (PITR) — step-by-step
- In the Azure portal, open the SQL database to restore.
- Choose “Restore” > “Point-in-time restore”.
- Select a restore point by date/time within the retention window.
- Choose a target server and provide a name for the restored database.
- Start the restore. Azure creates a new database that you can swap or redirect applications to after validation.
Alternatively, use PowerShell (Az.Sql) or Azure CLI:
- PowerShell example:
Restore-AzSqlDatabase -FromPointInTimeBackup ` -ResourceGroupName "rg" -ServerName "sqlserver" ` -TargetDatabaseName "restored-db" -PointInTime "2025-08-30T12:34:00Z"
- Azure CLI example:
az sql db restore --dest-name restored-db --name original-db --server sqlserver --resource-group rg --time "2025-08-30T12:34:00Z"
Restoring from LTR
- Go to the database’s Long-term retention blade in the Azure portal.
- Select a backup (weekly/monthly/yearly) from the list.
- Click “Restore” and specify a target server and database name.
- Start the restore — this creates a new database from the selected full backup.
LTR restores can take longer than PITR restores because full backup copies are retrieved from the LTR store.
Geo-restore and disaster recovery
- Geo-restore uses geo-redundant backups to restore a database in another region without any replication setup. Use when your primary region is unavailable and you need to recover to the most recent geo-replicated backup.
- For lower RTO/RPO and near-real-time failover, configure Active Geo-Replication or Auto-Failover Groups, which maintain readable secondary replicas in another region and can fail over with minimal downtime.
Exporting and manual backups
-
BACPAC export creates a schema+data file for portability. Use when migrating between servers or subscriptions. It does not replace PITR/LTR since it’s a one-time snapshot.
-
You can export via portal, SqlPackage, or PowerShell:
# Export with SqlPackage SqlPackage /Action:Export /SourceServerName:tcp:myserver.database.windows.net /SourceDatabaseName:mydb /TargetFile:mydb.bacpac /SourceUser:admin /SourcePassword:***
Monitoring and verifying backups
- Use Azure Monitor and diagnostic settings to collect backup/restore logs and metrics.
- Periodically test restores (PITR and LTR) to verify backup integrity and recovery procedures.
- Track backup storage and costs via Cost Management and billing reports.
Security and encryption
- Backups are encrypted at rest using Azure-managed keys by default. You can use customer-managed keys (CMK) in Azure Key Vault for added control.
- Control access to restore operations with role-based access control (RBAC) and Azure Active Directory.
- Audit restore and export operations with Auditing & Diagnostic settings.
Cost considerations
- Automated backups are included with the service, but backup storage beyond the allocated free amount (based on database size and service tier) is billed.
- LTR backups incur additional storage costs for retained full backups.
- Geo-redundant backups can increase storage cost compared to locally-redundant storage.
- Active Geo-Replication and failover groups incur compute and storage costs for secondary replicas.
Use Cost Management to estimate and monitor.
Best practices
- Keep PITR retention aligned with business requirements; extend retention for critical systems.
- Configure LTR for compliance requirements; test retrieval regularly.
- Use Geo-redundancy or Geo-Replication for critical cross-region DR.
- Use CMK if you require control over encryption keys.
- Automate regular restore testing and include RTO/RPO verification in runbooks.
- Monitor backup health and storage usage proactively.
Troubleshooting common issues
- “Restore fails due to insufficient backup retention”: check retention window and select a restore point within it.
- “Cannot restore to target server”: verify firewall rules, permissions (RBAC), and whether the target server is in the same subscription/region where allowed.
- “Slow restores”: consider network throughput, size of database, and whether restoring from LTR (slower) or PITR (faster).
Quick checklist before an incident
- Confirm PITR and LTR retention meet requirements.
- Ensure geo-redundancy or replication for critical databases.
- Validate access controls and key management.
- Schedule and automate restore drills.
- Monitor costs and backup storage limits.
Azure SQL Database offers robust built-in backups with flexible retention and restore options suitable for most recovery and compliance scenarios. Proper configuration, testing, and monitoring will ensure you can recover quickly and meet business requirements.