Azure SQL Database Documentation

Reference & Troubleshooting

Troubleshooting Azure SQL Database Backup and Restore

This guide provides solutions for common issues encountered when performing backup and restore operations on Azure SQL Database.

Common Issues and Resolutions

Issue 1: Backup Operation Fails or Times Out

Symptoms: Backup jobs complete with errors or run indefinitely without finishing.

  • Check Azure Service Health: Ensure there are no ongoing service incidents affecting Azure SQL Database in your region.
  • Resource Throttling: High database activity might consume available DTUs or vCores, impacting backup performance. Consider temporarily scaling up your database or scheduling backups during off-peak hours.
  • Network Connectivity: For user-initiated backups to Blob Storage (using T-SQL), verify network connectivity between your client and the Azure Storage account. Ensure firewalls or private endpoints are configured correctly.
  • Permissions: For backups to Blob Storage, ensure the SQL Database server has the necessary permissions to write to the specified storage container. Use a Shared Access Signature (SAS) or Managed Identity.
  • Database Size: Very large databases might take a considerable amount of time to back up. Azure SQL Database automatically backs up your database regularly, so verify if the automated backups are successful.

Issue 2: Restore Operation Fails or is Slow

Symptoms: Restore jobs error out, take an exceptionally long time, or the restored database is inaccessible.

  • Point-in-Time Restore (PITR) Availability: Ensure the desired restore point is within your database's retention period. The retention period depends on your service tier and configuration.
  • Restoring to a Different Region: Restoring to a different Azure region is supported. Ensure you have sufficient quota and network connectivity in the target region.
  • Restoring Geo-Backup: If restoring from a geo-replicated backup, ensure the secondary region is healthy and accessible.
  • Resource Limits in Target: The target SQL Database or Serverless SQL pool must have sufficient resources (DTUs/vCores, storage) to accommodate the restored database.
  • Storage Performance: For restores from Blob Storage, the performance of the Azure Storage account can impact restore speed. Ensure appropriate access tiers and redundancy are configured.
  • Concurrent Operations: Avoid performing resource-intensive operations on the target server or database while a restore is in progress.

Issue 3: Restored Database Shows Inconsistent Data

Symptoms: Data in the restored database appears incomplete or corrupted.

  • Incorrect Restore Point: Double-check that you selected the correct restore point for the PITR operation.
  • Corruption in Original Backup: While rare, it's possible the original backup itself was corrupted. Azure SQL Database performs integrity checks on its automated backups. If you suspect corruption in a T-SQL generated backup, consider restoring from a different, earlier point.
  • Application Logic Errors: Ensure that any application logic that runs post-restore (e.g., index rebuilds, statistics updates) is functioning correctly.

Issue 4: Unable to Access Backups in Azure Blob Storage

Symptoms: When trying to perform a T-SQL backup or restore, you receive errors related to accessing the storage container.

  • SAS Token Expiration/Permissions: If using a Shared Access Signature (SAS) token, ensure it hasn't expired and has the necessary read/write permissions for the blob container.
  • Storage Account Firewall: Verify that the Azure Storage account firewall is not blocking access from the Azure SQL Database service. You might need to allow Azure services to access the storage account.
  • Managed Identity Configuration: If using a Managed Identity, ensure it's correctly assigned to the Azure SQL Server and has the appropriate role (e.g., Storage Blob Data Contributor) on the storage account.
  • Container/Blob Name Typos: Ensure the container name and blob path are spelled correctly.
Pro Tip: Regularly monitor your automated backup successes and failures through the Azure portal or Azure Monitor. For critical databases, consider setting up alerts for backup failures.

Troubleshooting Steps Checklist

  1. Verify Azure Service Health.
  2. Check resource utilization of your SQL Database.
  3. Confirm network connectivity and firewall rules.
  4. Review required permissions (SAS tokens, Managed Identities).
  5. Ensure the target environment has sufficient resources.
  6. Validate the chosen restore point and database retention.
  7. Check storage account access and configuration.
  8. Consult Azure SQL Database logs for detailed error messages.

Further Reading