Azure SQL Database Backup and Restore
This document provides comprehensive information on backup and restore operations for Azure SQL Database. Learn how to protect your data, recover from accidental deletions, or revert to a previous state.
Table of Contents
Introduction to Backups
Azure SQL Database provides automated backups for all databases. These backups are essential for data protection and disaster recovery. You can restore your database to any point in time within your configured retention period.
Understanding the backup strategy is crucial for maintaining data integrity and ensuring business continuity. Azure SQL Database uses a robust storage system that ensures data durability.
Automated Backups
Azure SQL Database automatically performs full, differential, and transaction log backups. The frequency of these backups depends on the service tier and database size.
- Full Backups: Taken daily.
- Differential Backups: Taken at least once a day, capturing changes since the last full backup.
- Transaction Log Backups: Taken every 5-10 minutes.
These backups are stored securely and are highly available.
Restore Operations
Azure SQL Database offers several restore capabilities to meet various recovery needs.
Point-in-Time Restore (PITR)
PITR allows you to restore your database to any specific point in time within the automated backup retention period. This is useful for recovering from accidental data modifications or deletions.
The default retention period for PITR is typically 7 days, but can be extended up to 35 days for certain service tiers.
Long-Term Retention (LTR)
For compliance or regulatory requirements, you can configure Long-Term Retention policies to retain full backups for longer periods (up to 10 years). These backups are stored in Azure Blob Storage.
Note: Long-Term Retention is available for General Purpose, Business Critical, and Hyperscale tiers. It incurs additional storage costs.
Geo-Restore
Geo-restore allows you to restore a database from a geo-replicated backup to any Azure region. This is a disaster recovery mechanism to recover your database in a different geographic location if your primary region becomes unavailable.
Backup Storage and Retention
Automated backups are stored in geographically redundant storage (GRS) by default, providing data durability. You can choose locally redundant storage (LRS) for cost savings if geo-redundancy is not required for your automated backups.
Long-Term Retention backups are stored in GRS, LRS, or RA-GRS (Read-Access Geo-Redundant Storage) depending on your configuration.
The retention period for PITR backups is configurable. For LTR, you define specific retention periods for weekly, monthly, and yearly backups.
Restoring from Backups
You can restore your Azure SQL Database using various methods:
Using the Azure Portal
Navigate to your SQL database resource in the Azure portal. Under the 'Data management' section, select 'Restore'. You can choose the restore point, target server, and other options.
Using Azure PowerShell
Use the Restore-AzSqlDatabase cmdlet. For example:
# Restore to a specific point in time
Azure PowerShell
$ResourceGroupName = "YourResourceGroupName"
$ServerName = "your-sql-server-name"
$DatabaseName = "your-database-name"
$TargetServerName = "your-target-sql-server-name"
$RestorePoint = "2023-10-27T10:00:00Z"
Restore-AzSqlDatabase -FromPointInTime -ResourceGroupName $ResourceGroupName -ServerName $ServerName `
-DatabaseName $DatabaseName -TargetServerName $TargetServerName -RestorePointInTime $RestorePoint `
-ResourceId "/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/databases/{databaseName}" `
-Name "${DatabaseName}_restored" -Edition "Standard" -ServiceObjectiveName "S0"
Using Azure CLI
Use the az sql db restore command. For example:
# Restore to a specific point in time
Azure CLI
az sql db restore --resource-group YourResourceGroupName --server your-sql-server-name --name your-database-name \
--dest-name your-database-name-restored --time "2023-10-27T10:00:00Z" --dest-resource-group YourResourceGroupName \
--dest-server your-target-sql-server-name
Using Transact-SQL
You can initiate a restore operation using T-SQL by creating a new database from a backup copy. This typically involves using commands like CREATE DATABASE ... AS COPY OF ..., though direct point-in-time restore via T-SQL is more limited compared to portal/CLI methods for automated backups.
For LTR restores, you often retrieve the backup from Azure Blob Storage and then use T-SQL to restore it to a new database.
-- Example of restoring a database from a backup file (e.g., LTR backup)
-- This is a conceptual example and might require specific parameters based on the backup source.
-- You would typically restore from a backup file stored in blob storage.
-- This command might not directly apply to automated PITR restore without specific context.
-- CREATE DATABASE YourRestoredDatabase AS COPY OF YourOriginalDatabase
-- AT 'YYYY-MM-DD HH:MM:SS.ffffff' -- For specific point in time, if supported by the context.
-- For LTR, you'd typically restore from a stored backup file in Azure Blob Storage.
Important Considerations
- Restore Time: The time it takes to restore depends on the database size and the chosen service tier.
- Target Server: You can restore a database to the same logical server or a different one within the same Azure region.
- Naming: The restored database will have a new name. You cannot overwrite an existing database directly with a restore operation.
- Service Tiers and Compute: The restored database will inherit the service tier and compute size of the source database at the time of the backup, unless you specify otherwise during the restore process.
- Permissions: Ensure you have the necessary permissions to perform restore operations.
For more detailed information and advanced scenarios, please refer to the official Azure SQL Database documentation.