Azure SQL Database Backup and Restore

Introduction to Backup and Restore

Azure SQL Database provides automatic backups for all databases. You can leverage these backups to restore your database to a specific point in time. This section covers how these backups work and how you can perform restore operations.

Automatic Backups

Azure SQL Database automatically performs the following types of backups:

• Full Backups: Taken once a week.
• Differential Backups: Taken approximately every 12 to 24 hours.
• Transaction Log Backups: Taken every 5 to 10 minutes, depending on the database size and workload.

These backups are stored in Azure Blob Storage, which is geo-redundant by default, providing durability and availability. The retention period for automatic backups depends on the service tier and configuration of your Azure SQL Database.

Point-in-Time Restore (PITR)

Point-in-Time Restore allows you to restore your database to any specific point in time within the retention period of your automatic backups. This is crucial for recovering from accidental data deletion, corruption, or application errors.

Performing a Point-in-Time Restore using Azure Portal

1. Navigate to your Azure SQL Database resource in the Azure portal.
2. In the database menu, under "Data management," select "Restore."
3. In the "Restore" pane, select "Point in time restore."
4. Choose the restore point (date and time) from the dropdown.
5. Specify a new name for the restored database.
6. Select the target server and other configuration options as needed.
7. Click "Review + create," then "Create" to start the restore process.

Performing a Point-in-Time Restore using PowerShell

You can use Azure PowerShell to automate restore operations:

# Connect to your Azure account
Connect-AzAccount

# Select your subscription
Set-AzContext -SubscriptionId "your-subscription-id"

# Define variables
$resourceGroupName = "YourResourceGroupName"
$serverName = "YourServerName"
$databaseName = "YourDatabaseName"
$restorePointDateTime = "2023-10-27T10:30:00Z" # UTC time
$newDatabaseName = "YourRestoredDatabaseName"

# Get the source database object
$sourceDatabase = Get-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName

# Restore the database to a specific point in time
Restore-AzSqlDatabase -FromPointInTimeBackup `
    -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -ResourceId $sourceDatabase.ResourceId `
    -TargetDatabaseName $newDatabaseName `
    -PointInTime $restorePointDateTime `
    -Edition $sourceDatabase.Edition `
    -ServiceObjectiveName $sourceDatabase.ServiceObjectiveName
            

Long-Term Retention (LTR)

For compliance and disaster recovery requirements, Azure SQL Database supports Long-Term Retention (LTR) policies. LTR allows you to store full backups for longer periods (up to 10 years) in geo-redundant storage.

Configuring Long-Term Retention

1. Navigate to your Azure SQL Database in the Azure portal.
2. Under "Data management," select "Backup storage."
3. Go to the "Long term retention" tab.
4. Configure LTR policies for different retention periods (e.g., weekly, monthly, yearly backups) and their corresponding retention durations.

Restoring from Long-Term Retention Backups

Restoring from an LTR backup follows a similar process to PITR, but you select an LTR backup as the source:

1. In the "Restore" pane, choose "Long term backup restore."
2. Select the desired LTR backup based on its timestamp.
3. Specify the target server and new database name.
4. Initiate the restore.

Geo-Restore

If your database is configured with geo-redundant backup storage, you can restore your database to any region supported by Azure SQL Database. This is a critical capability for disaster recovery scenarios where your primary region might be unavailable.

Performing a Geo-Restore

The process for geo-restore is similar to PITR, but you select a different region for the restored database:

1. Navigate to your Azure SQL Database in the Azure portal.
2. Select "Restore."
3. Choose "Point in time restore" or "Long term backup restore."
4. During the configuration of the new database, select a different Azure region from the "Region" dropdown.
5. Complete the restore process.

Best Practices

• Regularly test your restore procedures to ensure they work as expected.
• Understand your RPO (Recovery Point Objective) and RTO (Recovery Time Objective) requirements to select appropriate backup and retention strategies.
• Consider using Geo-Restore for disaster recovery.
• Configure Long-Term Retention for compliance and archival needs.
• Monitor backup status and alerts through Azure Monitor.