Muokkaa

Jaa

Automated backups in Azure SQL Database

  • Artikkeli

Applies to: Azure SQL Database

This article describes the automated backup feature for Azure SQL Database.

To change backup settings, see Change settings. To restore a backup, see Recover using automated database backups.

What is a database backup?

Database backups are an essential part of any business continuity and disaster recovery strategy, because they help protect your data from corruption or deletion. These backups enable database restore to a point in time within the configured retention period. If your data protection rules require that your backups are available for an extended time (up to 10 years), you can configure long-term retention (LTR) for both single and pooled databases.

For service tiers other than Hyperscale, Azure SQL Database uses SQL Server engine technology to back up and restore data. Hyperscale databases use backup and restore based on storage snapshots. With traditional SQL Server backup technology, larger databases have long backup/restore times. With the use of snapshots, Hyperscale provides instant backup and fast restore capabilities irrespective of database size. To learn more, see Hyperscale backups.

Backup frequency

Azure SQL Database creates:

The exact frequency of transaction log backups is based on the compute size and the amount of database activity. When you restore a database, the service determines which full, differential, and transaction log backups need to be restored.

The Hyperscale architecture doesn't require full, differential, or log backups. To learn more, see Hyperscale backups.

Backup storage redundancy

The storage redundancy mechanism stores multiple copies of your data so that it's protected from planned and unplanned events. These events might include transient hardware failure, network or power outages, or massive natural disasters.

By default, new databases in Azure SQL Database store backups in geo-redundant storage blobs that are replicated to a paired region. Geo-redundancy helps protect against outages that affect backup storage in the primary region. It also allows you to restore your databases in a different region in the event of a regional outage.

The Azure portal provides a Workload environment option that helps to preset some configuration settings. These settings can be overridden. This option applies to the Create SQL Database portal page only.

  • Choosing the development workload environment sets the Backup storage redundancy option to use locally redundant storage. Locally redundant storage incurs less cost and is appropriate for preproduction environments that don't require the redundance of zone- or geo-replicated storage.
  • Choosing the Production workload environment sets the Backup storage redundancy to geo-redundant storage, the default.
  • The Workload environment option also changes the initial setting for compute, though this can be overridden. Otherwise, the Workload environment option has no impact on licensing or other database configuration settings.

To ensure that your backups stay within the same region where your database is deployed, you can change backup storage redundancy from the default geo-redundant storage to other types of storage that keep your data within the region. The configured backup storage redundancy is applied to both short-term retention (STR) backups and LTR backups. To learn more about storage redundancy, see Data redundancy.

You can configure backup storage redundancy when you create your database, and you can update it at a later time. The changes that you make to an existing database apply to future backups only. After you update the backup storage redundancy of an existing database, the changes might take up to 48 hours to be applied.

You can choose one of the following storage redundancies for backups:

  • Locally redundant storage (LRS): Copies your backups synchronously three times within a single physical location in the primary region. LRS is the least expensive storage option, but we don't recommend it for applications that require resiliency to regional outages or a guarantee of high data durability.

    Diagram showing the locally redundant storage (LRS) option.

  • Zone-redundant storage (ZRS): Copies your backups synchronously across three Azure availability zones in the primary region. It's currently available in only certain regions.

    Diagram showing the zone-redundant storage (ZRS) option.

  • Geo-redundant storage (GRS): Copies your backups synchronously three times within a single physical location in the primary region by using LRS. Then it copies your data asynchronously three times to a single physical location in the paired secondary region.

    The result is:

    • Three synchronous copies in the primary region.
    • Three synchronous copies in the paired region that were copied over from the primary region to the secondary region asynchronously.

    Diagram showing the geo-redundant storage (GRS) option.

  • Geo-Zone redundant storage (GZRS): Geo-zone-redundant storage (GZRS) combines the high availability provided by redundancy across availability zones (ZRS) with protection from regional outages provided by geo-replication (GRS). Copies your backups synchronously across three Azure availability zones in the primary region, and asynchronously three times to a single physical location in the paired secondary region.

    Microsoft recommends using GZRS for applications requiring maximum consistency, durability, and availability, excellent performance, and resilience for disaster recovery.

    The result is:

    • Three synchronous copies across Availability Zones, in the primary region.

    • Three synchronous copies in the paired region, asynchronously copied over from the primary region to the secondary region.

      The following diagram shows how your data is replicated with GZRS or RA-GZRS:

    Diagram showing the geo-zone redundant storage (GZRS) option.

Warning

  • Geo-restore is disabled as soon as a database is updated to use locally redundant or zone-redundant storage.
  • The storage redundancy diagrams all show regions with multiple availability zones (multi-az). However, there are some regions which provide only a single availability zone and do not support ZRS.
  • Backup storage redundancy for Hyperscale databases can be set only during creation. You can't modify this setting after the resource is provisioned. To update backup storage redundancy settings for an existing Hyperscale database with minimum downtime, use active geo-replication. Alternatively, you can use database copy. Learn more in Hyperscale backups and storage redundancy.

Backup usage

You can use automatically created backups in the following scenarios:

  • Restore an existing database to a point in time within the retention period by using the Azure portal, Azure PowerShell, the Azure CLI, or the REST API. This operation creates a new database on the same server as the original database, but it uses a different name to avoid overwriting the original database.

    After restore finishes, you can optionally delete the original database and rename the restored database to the original database name. Alternatively, instead of deleting the original database, you can rename it, and then rename the restored database to the original database name.

  • Restore a deleted database to a point in time within the retention period, including the time of deletion. The deleted database can be restored only on the same server where you created the original database. Before you delete a database, the service takes a final transaction log backup to prevent any data loss.

  • Restore a database to another geographic region. Geo-restore allows you to recover from a regional outage when you can't access your database or backups in the primary region. It creates a new database on any existing server in any Azure region.

    Important

    Geo-restore is available only for databases that are configured with geo-redundant backup storage. If you're not currently using geo-replicated backups for a database, you can change this by configuring backup storage redundancy.

  • Restore a database from a specific long-term backup of a single or pooled database, if the database has been configured with an LTR policy. LTR allows you to restore an older version of the database by using the Azure portal, the Azure CLI, or Azure PowerShell to satisfy a compliance request or to run an older version of the application. For more information, see Long-term retention.

Warning

When restoring a database and the source backup storage redundancy is configured as Geo-Zone Redundant Storage (GZRS), the source backup storage configuration is inherited by the new database if the backup storage redundancy configuration for the target database is not specified explicitly. This includes any restore operation, such as point-in-time restore, database copy, geo-restore, restore from a long-term backup. During this operation, if the target Azure region does not support the specific backup storage redundancy, the restore operation will fail with appropriate error message. This can be mitigated by explicitly specifying the available storage options for the region.

Automatic backups on secondary replicas

Automatic backups are now taken from a secondary replica in the Business Critical service tier. Since data is replicated between SQL Server processes on each node, the backup service takes the backup from the non-readable secondary replicas. This design ensures the primary replica remains dedicated to your main workload, and the readable secondary replica is dedicated to read-only workloads. Automatic backups in the Business Critical service tier are taken from a secondary replica most of the time. If an automatic backup fails on a secondary replica, then the backup service takes the backup from the primary replica.

Automatic backups on secondary replicas:

  • Are enabled by default.
  • Are included at no additional cost beyond the price of the service tier.
  • Bring improved performance and predictability to the Business Critical service tier.

Note

  • Create a Microsoft support ticket to disable the feature for your instance.

Restore capabilities and features

This table summarizes the capabilities and features of point-in-time restore (PITR), geo-restore, and long-term retention.

Backup property PITR Geo-restore LTR
Types of SQL backup Full, differential, log. Most recent geo-replicated copies of PITR backups. Only the full backups.
Recovery point objective (RPO) 10 minutes, based on compute size and amount of database activity. Up to 1 hour, based on geo-replication. 1 One week (or user's policy).
Recovery time objective (RTO) Restore usually takes less than 12 hours but could take longer, depending on size and activity. See Recovery. Restore usually takes less than 12 hours but could take longer, depending on size and activity. See Recovery. Restore usually takes less than 12 hours but could take longer, depending on size and activity. See Recovery.
Retention 7 days by default, configurable between 1 and 35 days (except Basic databases, which are configurable between 1 and 7 days). Enabled by default, same as source.2 Not enabled by default. Retention is up to 10 years.
Azure Storage Geo-redundant by default. You can optionally configure zone-redundant or locally redundant storage. Available when PITR backup storage redundancy is set to geo-redundant. Not available when PITR backup storage is zone-redundant or locally redundant. Geo-redundant by default. You can configure zone-redundant or locally redundant storage.
Configure backups as immutable Not supported Not supported Not supported
Restoring a new database in the same region Supported Supported Supported
Restoring a new database in another region Not supported Supported in any Azure region Supported in any Azure region
Restoring a new database in another subscription Not supported Not supported3 Not supported3
Restoring via Azure portal Yes Yes Yes
Restoring via PowerShell Yes Yes Yes
Restoring via Azure CLI Yes Yes Yes

1 For business-critical applications that require large databases and must ensure business continuity, use failover groups.
2 All PITR backups are stored on geo-redundant storage by default, so geo-restore is enabled by default.
3 The workaround is to restore to a new server and use Resource Move to move the server to another subscription, or use a cross-subscription database copy.

Restore a database from backup

To perform a restore, see Restore a database from backups. You can explore backup configuration and restore operations by using the following examples.

Operation Azure portal Azure CLI Azure PowerShell
Change backup retention SQL Database
SQL Managed Instance		 SQL Database
SQL Managed Instance		 SQL Database
SQL Managed Instance
Change long-term backup retention SQL Database
SQL Managed Instance		 SQL Database
SQL Managed Instance		 SQL Database
SQL Managed Instance
Restore a database from a point in time SQL Database
SQL Managed Instance		 SQL Database
SQL Managed Instance		 SQL Database
SQL Managed Instance
Restore a deleted database SQL Database
SQL Managed Instance		 SQL Database
SQL Managed Instance		 SQL Database
SQL Managed Instance

Export a database

Automatic backups taken by the Azure service are not available to download or access directly. They can only be used for restore operations through Azure.

There are alternatives to export an Azure SQL Database. When you need to export a database for archiving or for moving to another platform, you can export the database schema and data to a BACPAC file. A BACPAC file is a ZIP file with an extension of BACPAC containing the metadata and data from the database. A BACPAC file can be stored in Azure Blob storage or in local storage in an on-premises location and later imported back into Azure SQL Database, Azure SQL Managed Instance, or a SQL Server instance.

You can also Import or export an Azure SQL Database using private link or Import or export an Azure SQL Database without allowing Azure services to access the server.

Backup scheduling

The first full backup is scheduled immediately after a new database is created or restored. This backup usually finishes within 30 minutes, but it can take longer when the database is large. For example, the initial backup can take longer on a restored database or a database copy, which would typically be larger than a new database.

After the first full backup, all further backups are scheduled and managed automatically. The exact timing of all database backups is determined by the SQL Database service as it balances the overall system workload. You can't change the schedule of backup jobs or disable them.

Important

  • For a new, restored, or copied database, the point-in-time restore capability becomes available when the initial transaction log backup that follows the initial full backup is created.
  • Hyperscale databases are protected immediately after creation, unlike other databases where the initial backup takes time. The protection is immediate even if the Hyperscale database was created with a large amount of data via copy or restore. To learn more, review Hyperscale automated backups.

Backup storage consumption

With SQL Server backup and restore technology, restoring a database to a point in time requires an uninterrupted backup chain. That chain consists of one full backup, optionally one differential backup, and one or more transaction log backups.

Azure SQL Database schedules one full backup every week. To provide PITR within the entire retention period, the system must store additional full, differential, and transaction log backups for up to a week longer than the configured retention period.

In other words, for any point in time during the retention period, there must be a full backup that's older than the oldest time of the retention period. There must also be an uninterrupted chain of differential and transaction log backups from that full backup until the next full backup.

Hyperscale databases use a different backup scheduling mechanism. For more information, see Hyperscale backup scheduling.

Backups that are no longer needed to provide PITR functionality are automatically deleted. Because differential backups and log backups require an earlier full backup to be restorable, all three backup types are purged together in weekly sets.

For all databases, including TDE-encrypted databases, all full and differential backups are compressed, to reduce backup storage compression and costs. Average backup compression ratio is 3 to 4 times. However, it can be lower or higher depending on the nature of the data and whether data compression is used in the database.

Important

For TDE-encrypted databases, log backups files are not compressed for performance reasons. Log backups for non-TDE-encrypted databases are compressed.

Azure SQL Database computes your total used backup storage as a cumulative value. Every hour, this value is reported to the Azure billing pipeline. The pipeline is responsible for aggregating this hourly usage to calculate your consumption at the end of each month. After the database is deleted, consumption decreases as backups age out and are deleted. After all backups are deleted and PITR is no longer possible, billing stops.

Important

Backups of a database are retained to provide PITR even if the database has been deleted. Although deleting and re-creating a database might save storage and compute costs, it might increase backup storage costs. The reason is that the service retains backups for each deleted database, every time it's deleted.

Monitor consumption

For vCore databases in Azure SQL Database, the storage that each type of backup (full, differential, and log) consumes is reported on the database monitoring pane as a separate metric. The following screenshot shows how to monitor the backup storage consumption for a single database.

Screenshot that shows selections for monitoring database backup consumption in the Azure portal.

For instructions on how to monitor consumption in Hyperscale, see Monitor Hyperscale backup consumption.

Fine-tune backup storage consumption

Backup storage consumption up to the maximum data size for a database isn't charged. Excess backup storage consumption depends on the workload and maximum size of the individual databases. Consider some of the following tuning techniques to reduce your backup storage consumption:

  • Reduce the backup retention period to the minimum for your needs.
  • Avoid doing large write operations, like index rebuilds, more often than you need to.
  • For large data load operations, consider using clustered columnstore indexes and following related best practices. Also consider reducing the number of nonclustered indexes.
  • In the General Purpose service tier, the provisioned data storage is less expensive than the price of the backup storage. If you have continually high excess backup storage costs, you might consider increasing data storage to save on the backup storage.
  • Use tempdb instead of permanent tables in your application logic for storing temporary results or transient data.
  • Use locally redundant backup storage whenever possible (for example, dev/test environments).

Backup retention

Azure SQL Database provides both short-term and long-term retention of backups. Short-term retention allows PITR within the retention period for the database. Long-term retention provides backups for various compliance requirements.

Short-term retention

For all new, restored, and copied databases, Azure SQL Database retains sufficient backups to allow PITR within the last 7 days by default. The service takes regular full, differential, and log backups to ensure that databases are restorable to any point in time within the retention period that's defined for the database.

Differential backups can be configured to occur either once in 12 hours or once in 24 hours. A 24-hour differential backup frequency might increase the time required to restore the database, compared to the 12-hour frequency. In the vCore model, the default frequency for differential backups is once in 12 hours. In the DTU model, the default frequency is once in 24 hours.

You can specify your backup storage redundancy option for STR when you create your database, and then change it at a later time. If you change your backup redundancy option after your database is created, new backups will use the new redundancy option. Backup copies made with the previous STR redundancy option aren't moved or copied. They're left in the original storage account until the retention period expires, which can be 1 to 35 days.

You can change the backup retention period for each active database in the range of 1 to 35 days, except for Basic databases, which are configurable from 1 to 7 days. As described in Backup storage consumption, backups stored to enable PITR might be older than the retention period. If you need to keep backups for longer than the maximum short-term retention period of 35 days, you can enable long-term retention.

If you delete a database, the system keeps backups in the same way for an online database with its specific retention period. You can't change the backup retention period for a deleted database.

Important

If you delete a server, all databases on that server are also deleted and can't be recovered. You can't restore a deleted server. But if you've configured long-term retention for a database, LTR backups are not deleted. You can then use those backups to restore databases on a different server in the same subscription, to a point in time when an LTR backup was taken. To learn more, review Restore long-term backup.

Long-term retention

For SQL Database, you can configure full long-term retention (LTR) backups for up to 10 years in Azure Blob Storage. After the LTR policy is configured, full backups are automatically copied to a different storage container weekly.

To meet various compliance requirements, you can select different retention periods for weekly, monthly, and/or yearly full backups. The frequency depends on the policy. For example, setting W=0, M=1 would create an LTR copy monthly. For more information about LTR, see Long-term retention.

Updating the backup storage redundancy for an existing database applies the change only to subsequent backups taken in the future and not for existing backups. All existing LTR backups for the database continue to reside in the existing storage blob. New backups are replicated based on the configured backup storage redundancy.

Storage consumption depends on the selected frequency and retention periods of LTR backups. You can use the LTR pricing calculator to estimate the cost of LTR storage.

When restoring a Hyperscale database from an LTR backup, the read scale property is disabled. To enable, read scale on the restored database, update the database after it has been created. You need to specify the target service level objective when restoring from an LTR backup.

Long-term retention can be enabled for Hyperscale databases created or migrated from other service tiers. If you attempt to enable LTR for a Hyperscale database where it isn't yet supported, you receive the following error: "An error has occurred while enabling Long-term backup retention for this database. Please reach out to Microsoft support to enable long-term backup retention." In this case, reach out to Microsoft support and create a support ticket to resolve.

Backup storage costs

The price for backup storage varies and depends on your purchasing model (DTU or vCore), chosen backup storage redundancy option, and region. Backup storage is charged based on gigabytes consumed per month, at the same rate for all backups.

Backup storage redundancy affects backup costs in the following way:

  • Locally redundant price = published price
  • Zone-redundant price = published price x 1.25
  • Geo-redundant price = published price x 2

For pricing, see the Azure SQL Database pricing page.

Note

An Azure invoice shows only the excess backup storage consumption, not the entire backup storage consumption. For example, in a hypothetical scenario, if you have provisioned 4 TB of data storage, you'll get 4 TB of free backup storage space. If you use a total of 5.8 TB of backup storage space, the Azure invoice shows only 1.8 TB, because you're charged only for excess backup storage that you've used.

DTU model

In the DTU model, for databases and elastic pools there's no additional charge for PITR backup storage for default retention of 7 days and beyond. The price of PITR backup storage is a part of the database or pool price.

Important

In the DTU model, databases and elastic pools are charged for the LTR backup storage based on the actual storage consumed by LTR backups.

vCore model

Azure SQL Database computes your total billable backup storage as a cumulative value across all backup files. Every hour, this value is reported to the Azure billing pipeline. The pipeline aggregates this hourly usage to get your backup storage consumption at the end of each month.

If a database is deleted, backup storage consumption will gradually decrease as older backups age out and are deleted. Because differential backups and log backups require an earlier full backup to be restorable, all three backup types are purged together in weekly sets. After all backups are deleted, billing stops.

Backup storage cost is calculated differently for Hyperscale databases. For more information, see Hyperscale backup storage costs.

For single databases, a backup storage amount equal to 100 percent of the maximum data storage size for the database is provided at no extra charge. The following equation is used to calculate the total billable backup storage usage:

Total billable backup storage size = (size of full backups + size of differential backups + size of log backups) – maximum data storage

For elastic pools, a backup storage amount equal to 100 percent of the maximum data storage for the pool storage size is provided at no extra charge. For pooled databases, the total size of billable backup storage is aggregated at the pool level and is calculated as follows:

Total billable backup storage size = (total size of all full backups + total size of all differential backups + total size of all log backups) - maximum pool data storage

Total billable backup storage, if any, is charged in gigabytes per month according to the rate of the backup storage redundancy that you've used. This backup storage consumption depends on the workload and size of individual databases, elastic pools, and managed instances. Heavily modified databases have larger differential and log backups, because the size of these backups is proportional to the amount of changed data. Therefore, such databases have higher backup charges.

As a simplified example, assume that a database has accumulated 744 GB of backup storage and that this amount stays constant throughout an entire month because the database is completely idle. To convert this cumulative storage consumption to hourly usage, divide it by 744.0 (31 days per month times 24 hours per day). SQL Database reports to the Azure billing pipeline that the database consumed 1 GB of PITR backup each hour, at a constant rate. Azure billing aggregates this consumption and show a usage of 744 GB for the entire month. The cost is based on the rate for gigabytes per month in your region.

Here's another example. Suppose the same idle database has its retention increased from 7 days to 14 days in the middle of the month. This increase results in the total backup storage doubling to 1,488 GB. SQL Database would report 1 GB of usage for hours 1 through 372 (the first half of the month). It would report the usage as 2 GB for hours 373 through 744 (the second half of the month). This usage would be aggregated to a final bill of 1,116 GB per month.

Actual backup billing scenarios are more complex. Because the rate of changes in the database depends on the workload and is variable over time, the size of each differential and log backup will also vary. The hourly consumption of backup storage fluctuates accordingly.

Each differential backup also contains all changes made in the database since the last full backup. So, the total size of all differential backups gradually increases over the course of a week. Then it drops sharply after an older set of full, differential, and log backups ages out.

For example, assume that a heavy write activity, such as an index rebuild, runs just after a full backup is completed. The modifications that the index rebuild makes will then be included:

  • In the transaction log backups taken over the duration of the rebuild.
  • In the next differential backup.
  • In every differential backup taken until the next full backup occurs.

For the last scenario in larger databases, an optimization in the service creates a full backup instead of a differential backup if a differential backup would be excessively large otherwise. This reduces the size of all differential backups until the following full backup.

You can monitor total backup storage consumption for each backup type (full, differential, transaction log) over time, as described in Monitor consumption.

Monitor costs

To understand backup storage costs, go to Cost Management + Billing in the Azure portal. Select Cost Management, and then select Cost analysis. Select the desired subscription for Scope, and then filter for the time period and service that you're interested in as follows:

  1. Add a filter for Service name.

  2. In the dropdown list, select sql database for a single database or an elastic database pool.

  3. Add another filter for Meter subcategory.

  4. To monitor PITR backup costs, in the dropdown list, select single/elastic pool pitr backup storage for a single database or an elastic database pool. Meters show up only if backup storage consumption exists.

    To monitor LTR backup costs, in the dropdown list, select ltr backup storage for a single database or an elastic database pool. Meters show up only if backup storage consumption exists.

The Storage and compute subcategories might also interest you, but they're not associated with backup storage costs.

Screenshot that shows an analysis of backup storage costs.

Important

Meters are visible only for counters that are currently in use. If a counter is not available, it's likely that the category is not currently being used. For example, storage counters won't be visible for resources that are not consuming storage. If there is no PITR or LTR backup storage consumption, these meters won't be visible.

For more information, see Azure SQL Database cost management.

Encrypted backups

If your database is encrypted with TDE, backups are automatically encrypted at rest, including LTR backups. All new databases in Azure SQL are configured with TDE enabled by default. For more information on TDE, see Transparent data encryption with SQL Database.

Backup integrity

On an ongoing basis, the Azure SQL engineering team automatically tests the restore of automated database backups. Upon point-in-time restore, databases also receive DBCC CHECKDB integrity checks.

Any issues found during an integrity check result in an alert to the engineering team. For more information, see Data integrity in SQL Database.

All database backups are taken with the CHECKSUM option to provide additional backup integrity.

Compliance

When you migrate your database from a DTU-based service tier to a vCore-based service tier, the PITR retention is preserved to ensure that your application's data recovery policy isn't compromised. If the default retention doesn't meet your compliance requirements, you can change the PITR retention period. For more information, see Change the PITR backup retention period.

Note

The Change automated backup settings article provides steps about how to delete personal data from the device or service and can be used to support your obligations under the GDPR. For general information about GDPR, see the GDPR section of the Microsoft Trust Center and the GDPR section of the Service Trust portal.

Use Azure Policy to enforce backup storage redundancy

If you have data residency requirements that require you to keep all your data in a single Azure region, you might want to enforce zone-redundant or locally redundant backups for your SQL database by using Azure Policy.

Azure Policy is a service that you can use to create, assign, and manage policies that apply rules to Azure resources. Azure Policy helps you to keep these resources compliant with your corporate standards and service-level agreements. For more information, see Overview of Azure Policy.

Built-in backup storage redundancy policies

To enforce data residency requirements at an organizational level, you can assign policies to a subscription by using the Azure portal or Azure PowerShell.

For example, if you enable the policy "Azure SQL DB should avoid using GRS backup", databases cannot be created with the default storage as globally redundant storage, and users would be prevented from using GRS with the error message "Configuring backup storage account type to 'Standard_RAGRS' failed during Database create or update."

For a full list of built-in policy definitions for SQL Database, review the policy reference.

Important

Azure policies are not enforced when you're creating a database via T-SQL. To specify data residency when you're creating a database by using T-SQL, use LOCAL or ZONE as input to the BACKUP_STORAGE_REDUNDANCY parameter in the CREATE DATABASE statement.

Related content