Azure SQL Database Backup and Restore

This document provides a comprehensive guide to understanding and utilizing the backup and restore capabilities of Azure SQL Database. Azure SQL Database automatically backs up your data, enabling you to recover your database to any point in time within your configured retention period.

Understanding Automatic Backups

Azure SQL Database performs full, differential, and transaction log backups automatically. You don't need to manage these backups yourself. The service handles the infrastructure, storage, and backup schedule. The retention period for these backups depends on the service tier and configuration of your SQL Database.

Backup Types and Schedules:

Full Backups: Taken regularly (typically weekly).
Differential Backups: Taken frequently (typically daily) to capture changes since the last full backup.
Transaction Log Backups: Taken every 5-10 minutes for most service tiers, allowing for point-in-time restore.

Restore Options

Azure SQL Database offers several restore scenarios:

1. Point-in-Time Restore (PITR)

This is the most common restore operation. You can restore your database to any specific point in time within the available backup retention period. This is invaluable for recovering from accidental data modifications, corruption, or user errors.

Key Features:

Restore to a specific date and time.
Can restore to the same server or a different server.
The restored database becomes a new, independent database.

2. Geo-Restore

If your database is unavailable due to a regional outage, you can restore it from a geo-replicated backup to a database in any available Azure region. This is a crucial part of your disaster recovery strategy.

Prerequisites:

Geo-redundant backup storage must be enabled for your database.
You need to choose a target region that is different from the source region.

3. Long-Term Retention (LTR)

For compliance or archival purposes, you can configure long-term retention policies to store backups for extended periods (up to 10 years). These backups are stored in separate geo-redundant storage and can be accessed for recovery when needed.

How to Perform a Restore

Restoring a database can be accomplished using the Azure portal, Azure PowerShell, or Azure CLI.

Using the Azure Portal:

Navigate to your Azure SQL Database resource in the Azure portal.
In the left-hand menu, under "Data management," select "Restore."
Choose the restore type (Point-in-time restore, Geo-restore).
For Point-in-time restore, select the desired recovery point.
Specify the new database name, server, and other configuration settings.
Click "Review + create," then "Create" to start the restore process.

Using Azure PowerShell:

Example for Point-in-time Restore:


$resourceGroupName = "YourResourceGroupName"
$serverName = "YourServerName"
$databaseName = "YourDatabaseName"
$restorePointDateTime = "2023-10-27T10:30:00Z" # Example: October 27, 2023 at 10:30 UTC
$newDatabaseName = "YourRestoredDatabaseName"
$newServerName = "YourTargetServerName" # Can be the same as $serverName

Restore-AzSqlDatabase -FromPointInTimeBackup -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName -PointInTime $restorePointDateTime -TargetDatabaseName $newDatabaseName -TargetServerName $newServerName

Using Azure CLI:

Example for Point-in-time Restore:


az sql db restore --resource-group YourResourceGroupName --server YourServerName --name YourDatabaseName --dest-name YourRestoredDatabaseName --time "2023-10-27T10:30:00Z" --dest-server YourTargetServerName

Restoring to a Different Server

When performing a restore, you can choose to restore the database to a different Azure SQL Database server. This is useful for creating development/test environments, migrating data, or isolating restored data.

Considerations for Restore Operations

Performance: Large databases can take significant time to restore. Monitor the progress in the Azure portal.
Cost: Restored databases consume storage and compute resources. Ensure you have adequate capacity and budget.
Permissions: You need appropriate Azure RBAC roles (e.g., SQL DB Contributor, Owner) to perform restore operations.
Database Settings: When restoring, you can configure settings like service tier, compute size, and collation for the new database.

Important Note: Always test your restore procedures regularly to ensure they function as expected and to familiarize your team with the process.

Next Steps

Explore the following resources for more detailed information: