Azure SQL Database Backup and Restore
This document provides comprehensive information on how Azure SQL Database handles backups and restores, including automated backups, point-in-time restore, and long-term retention.
Automated 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 high availability and durability.
- Full Backups: Performed daily.
- Differential Backups: Performed approximately every 12 to 24 hours.
- Transaction Log Backups: Performed every 5 to 10 minutes, depending on the database size and workload.
The retention period for these automated backups can be configured, with default retention policies and options for extending them.
Point-in-Time Restore (PITR)
Point-in-Time Restore allows you to restore your database to any specific point in time within your configured backup retention period. This is crucial for recovering from accidental data corruption or unwanted changes.
How PITR Works
PITR leverages the automated backups. When you initiate a PITR, Azure SQL Database:
- Finds the latest full backup before the specified restore point.
- Applies the subsequent differential backup (if any).
- Applies the transaction log backups up to the exact restore point.
Performing a PITR
You can perform a Point-in-Time Restore using the Azure portal, Azure PowerShell, or Transact-SQL.
Note: A Point-in-Time Restore creates a new database. The original database remains untouched unless you explicitly drop it.
Example using Azure CLI:
az sql db restore --resource-group <your-resource-group> --server <your-server-name> --name <original-database-name> --dest-name <new-restored-database-name> --time "YYYY-MM-DDTHH:MM:SSZ"Long-Term Retention (LTR)
For compliance and archival purposes, Azure SQL Database offers Long-Term Retention (LTR) capabilities. LTR allows you to store backups for an extended period (up to 10 years) beyond the default retention limits of automated backups.
Configuring LTR
LTR policies are configured at the server level or database level. You can specify retention for full backups, differential backups, and transaction log backups.
Tip: LTR backups are stored in geo-redundant storage and are cost-effective for long-term archival.
Restoring from LTR
You can restore a database from an LTR backup to a specific point in time within the LTR retention period, similar to PITR, but using the LTR backup set.
Database Copy
Azure SQL Database also supports creating copies of your databases. This can be useful for creating development/test environments, performing analysis, or migrating data.
- Creating a copy within the same server.
- Creating a copy to a different server.
Database copies can be created using the Azure portal, Azure PowerShell, or T-SQL.
Geo-Restore
If your database is configured with geo-redundant backup storage, you can perform a geo-restore to a different Azure region. This is a disaster recovery strategy to recover your database in another region.