Azure SQL Database Backup

1. Scope of This Article

In this article, we will explore the backup capabilities of Azure SQL Database and provide a detailed explanation of BACPAC file export. By leveraging these features, you can minimize the risk of data loss while enhancing system availability and reliability.

2. Content

Azure SQL Database has automatic backups enabled by default, simplifying data protection. The backup functionality is not only a safeguard against data loss but also a crucial measure for maintaining application and system availability.

For example, backups enable quick recovery in case of accidental data deletion or system failures. Additionally, they can be utilized for data migration to test or development environments. Moreover, leveraging backups for long-term data retention ensures compliance with regulatory and audit requirements.

2–1. Features

Automatic Backups

Azure SQL Database’s backup functionality is designed to ensure database availability. It automatically performs full backups, differential backups, and transaction log backups, retaining them for up to 35 days.

• Transaction Log Backups: Taken every 5 to 10 minutes, enabling point-in-time recovery.
• Full Database Backups: Taken weekly, capturing the entire database.
• Differential Backups: Taken daily, including only the changes made since the last full backup.

Backup Storage Redundancy

Backups are stored in Azure Storage, with geo-redundancy options available to enhance data resilience. This ensures recovery even in the event of a regional outage. Additionally, Azure Storage supports local and zone-redundant storage, allowing flexible backup configurations tailored to operational needs.

• Geo-Redundant Storage (GRS): Stores backups in a secondary region for disaster recovery.
• Local-Redundant Storage (LRS): Maintains backups within the same region.
• Zone-Redundant Storage (ZRS): Distributes backups across availability zones.

Long-Term Backups

By configuring Long-Term Retention (LTR), backups can be stored for up to 10 years. When LTR is enabled, a weekly full backup is automatically copied to a separate storage container, meeting compliance and archival requirements for long-term data preservation.

BACPAC Files

For saving both database schema and data, the export feature allows backups in BACPAC file format. This format is ideal for migrating data to other environments or maintaining snapshot-style backups.

2–2. How to Check Backup Settings

To check the backup status, you can use either the Azure Portal or the Azure CLI. Below is an example using the Azure CLI:

az sql db show --name <database_name> --resource-group <resource_group_name> --server <server_name>

The following is a sample output, highlighting key backup-related details:

{
  "currentBackupStorageRedundancy": "Geo",  // Current backup storage redundancy level
  "requestedBackupStorageRedundancy": "Geo",  // Requested storage redundancy
  "earliestRestoreDate": "2024-11-16T07:04:31.261360+00:00",  // Earliest restore point
  "defaultSecondaryLocation": "japanwest",  // Default secondary region
  "creationDate": "2018-10-31T01:11:51+00:00"  // Database creation date
}

• The current backup storage redundancy is set to "Geo", meaning backups are stored across multiple regions.
• The earliest restore date is November 16, 2024, allowing point-in-time recovery up to that point.
• The default secondary region is "japanwest", ensuring disaster recovery through geo-redundant backups.

These details help evaluate whether the backup configuration aligns with operational requirements and assist in disaster recovery planning.

2–3. Exporting a BACPAC File

When exporting a BACPAC file from Azure SQL Database, you can choose to export it to Azure Blob Storage or to a local environment (on-premises or a local machine). Each method has its own advantages and considerations, making it important to select the appropriate option based on your use case and environment.

When exporting to Azure Blob Storage, data is securely stored in the cloud, making backup and data migration more convenient. Additionally, it enables access across multiple regions and leverages scalability for improved flexibility. However, it is important to consider network bandwidth usage and storage costs, as they can impact performance and expenses.

When exporting to a local environment, you can manage the data directly without relying on network connectivity. This is especially useful for on-premises operations or when access to the cloud is restricted. However, for large datasets, the export process may take longer, and sufficient disk space must be ensured.

Regardless of the chosen method, it is crucial to prepare the export destination in advance and ensure data integrity and security throughout the process.

2–3–1. Exporting to Azure Blob Storage

Suitable Scenarios

Saving a BACPAC file to Azure Blob Storage allows for easy access from different Azure subscriptions or regions, enabling efficient data sharing across environments. Additionally, BACPAC files stored in Azure Storage are useful for disaster recovery and archival requirements. By leveraging Azure Storage, backup data can be securely and reliably retained for extended periods, making it a valuable component of a Business Continuity Plan (BCP).

Export Procedure

You can export a BACPAC file to Azure Blob Storage using either the Azure Portal or the Azure CLI.

    az sql db export \
        --admin-password <AdminPassword> \
        --admin-user <AdminUsername> \
        --storage-key <StorageAccountKey> \
        --storage-key-type StorageAccessKey \
        --storage-uri <BlobStorageUri> \
        --name <DatabaseName> \
        --server <ServerName> \
        --resource-group <ResourceGroupName>

2–3–2. Exporting to a Local Environment

Suitable Scenarios

Exporting a BACPAC file to a local environment is ideal for small databases. This method allows for easy import into an on-premises SQL Server, making it particularly useful for test and development environments. Additionally, locally exported BACPAC files can be used as snapshots, enabling quick data restoration and replication for testing purposes.

Export Procedure

Export to a local environment using the SqlPackage utility. SqlPackage is a command-line tool provided by Microsoft, designed to efficiently manage SQL Server and Azure SQL Database. With this tool, you can perform various database management tasks from the command line, including:

• Exporting and importing databases
• Deploying databases
• Comparing and synchronizing schemas

SqlPackage supports parallel execution, allowing multiple commands to run simultaneously, which improves the speed of exporting and importing large datasets. Additionally, this tool is multi-platform compatible, supporting Windows, macOS, and Linux. As a result, it can be utilized across various development and production environments, making it a versatile solution for a wide range of systems.

SqlPackage offers flexible authentication options, supporting SQL authentication, Azure Active Directory (AAD) authentication, and Universal authentication. This ensures secure connections while performing database operations. To connect to Azure SQL Database, an access token for AAD authentication must be obtained.

ACCESS_TOKEN=$(az account get-access-token --resource https://database.windows.net/ --query accessToken -o tsv)

Use the following command to export a database from Azure SQL Database to a BACPAC file. Specify the obtained access token using the /AccessToken parameter.

SqlPackage /Action:Export \  // データベースをエクスポート
           /SourceServerName:"<server-name>.database.windows.net" \  // SQL ServerまたはAzure SQL Database のサーバー名
           /SourceDatabaseName:"<database-name>" \  // エクスポートするデータベース名
           /TargetFile:"<path-to-export>/ExportedDatabase.bacpac" \  // BACPAC ファイルを保存するローカルパス
           /AccessToken:"<AAD-access-token>" //Azure Active Directory ユニバーサル認証トークン

Example Output

Connecting to database 'p1db' on server 'p1.database.windows.net'.
Extracting schema
Extracting schema from database
Resolving references in schema model
Validating schema model
Validating schema model for data package
Validating schema
Exporting data from database
Exporting data
Processing Export.
Processing Table '[dbo].[SampleTable]'.
Successfully exported database and saved it to file '/mnt/c/Users/2024-/sample/ExportedDatabase.bacpac'.
Changes to connection setting default values were incorporated in a recent release.  More information is available at https://aka.ms/dacfx-connection
Time elapsed 0:00:26.90

3. Conclusion

The backup capabilities of Azure SQL Database and BACPAC file export are essential for data protection and operational efficiency. By leveraging these tools effectively, you can minimize the risk of data loss while enhancing system availability and reliability.

Selecting the appropriate export method based on operational requirements and data size ensures efficient database management and streamlined operations.

References

• SqlPackage — SQL Server | Microsoft Learn
• Import a BACPAC File to Create a Database in Azure SQL Database & Azure SQL Managed Instance | Microsoft Learn

These resources provide detailed guidance on SqlPackage usage and importing BACPAC files into Azure SQL Database.