Automated backups - Azure SQL Database & Azure SQL Managed Instance

APPLIES TO: Azure SQL Database Azure SQL Managed Instance

Note

This 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.

What is a database backup?

Database backups are an essential part of any business continuity and disaster recovery strategy, because they 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 for both single and pooled databases.

Backup and restore essentials

Databases in Azure SQL Managed Instance and non-Hyperscale databases in Azure SQL Database use SQL Server engine technology to back up and restore data. Hyperscale databases have a unique architecture and leverage a different technology for backup and restore. To learn more, see Hyperscale backups and storage redundancy.

Backup frequency

Both Azure SQL Database and SQL Managed Instance use SQL Server technology to create full backups every week, differential backups every 12-24 hours, and transaction log backups every 10 minutes. The 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.

Hyperscale databases use snapshot backup technology.

Backup storage redundancy

By default, Azure SQL Database and Azure SQL Managed Instance store data in geo-redundant storage blobs that are replicated to a paired region. Geo-redundancy helps to protect against outages impacting backup storage in the primary region and allows you to restore your server to a different region in the event of a disaster.

To ensure that your data stays within the same region where your database or managed instance is deployed, you can change the default geo-redundant backup storage redundancy. The storage redundancy mechanism stores multiple copies of your data so that it is protected from planned and unplanned events, including transient hardware failure, network or power outages, or massive natural disasters. The configured backup storage redundancy is applied to both short-term backup retention settings that are used for point in time restore (PITR) and long-term retention backups used for long-term backups (LTR).

Backup storage redundancy can be configured when you create your database or instance, and can be updated at a later time; the changes made to an existing database apply to future backups only. After the backup storage redundancy of an existing database is updated, it may take up to 48 hours for the changes to be applied. Geo-restore is disabled as soon as a database is updated to use local or zone redundant storage. For Hyperscale databases, the selected storage redundancy option will be used for the lifetime of the database for both data storage redundancy and backup storage redundancy. Learn more in Hyperscale backups and storage redundancy.

The option to configure backup storage redundancy provides flexibility to choose one of the following storage redundancies for backups:

  • Locally-redundant (LRS): Copies your backups synchronously three times within a single physical location in the primary region. LRS is the least expensive replication option, but isn't recommended for applications requiring high availability or durability.
  • Zone-redundant (ZRS): Copies your backups synchronously across three Azure availability zones in the primary region.
  • Geo-redundant (GRS): Copies your backups synchronously three times within a single physical location in the primary region using LRS, then copies your data asynchronously to a single physical location in the paired secondary region.
  • Geo-zone-redundant (GZRS): (Azure SQL Managed Instance only) Combines the high availability provided by redundancy across availability zones with protection from regional outages provided by geo-replication. Data in a GZRS storage account is copied across three Azure availability zones in the primary region and is also replicated to a secondary geographic region for protection from regional disasters.

To learn more about storage redundancy, see Data redundancy.

Important

Zone-redundant storage is currently only available in certain regions.

Backup usage

You can use these backups to:

  • Point-in-time restore of existing database - Restore an existing database to a point in time in the past within the retention period by using the Azure portal, Azure PowerShell, Azure CLI, or REST API. For SQL Database, this operation creates a new database on the same server as the original database, but uses a different name to avoid overwriting the original database. After restore completes, you can delete the original database. Alternatively, you can rename both the original database, and then rename the restored database to the original database name. Similarly, for SQL Managed Instance, this operation creates a copy of the database on the same or different managed instance in the same subscription and same region.
  • Point-in-time restore of deleted database - Restore a deleted database to the time of deletion or to any point in time within the retention period. The deleted database can be restored only on the same server or managed instance where the original database was created. When deleting a database, the service takes a final transaction log backup before deletion, to prevent any data loss.
  • Geo-restore - Restore a database to another geographic region. Geo-restore allows you to recover from a geographic disaster when you cannot access your database or backups in the primary region. It creates a new database on any existing server or managed instance, in any Azure region.

    Important

    Geo-restore is available only for databases in Azure SQL Database or SQL Managed Instances configured with geo-redundant backup storage. If you are not currently using geo-replicated backups for a database, you can change this by configuring backup storage redundancy.

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

Note

In Azure Storage, the term replication refers to copying blobs from one location to another. In SQL, database replication refers to various technologies used to keep multiple secondary databases synchronized with a primary database.

Restore capabilities and features of Azure SQL Database and Azure SQL Managed Instance

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

Backup Properties  Point in time recovery (PITR)  Geo-restore Long-term backup restore
Types of SQL backup Full, Differential, Log 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.*     One week (or user's policy).
Recovery Time Objective (RTO) Restore usually takes <12 hours, but could take longer dependent on size and activity. See Recovery Restore usually takes <12 hours, but could take longer dependent on size and activity. See Recovery. Restore usually takes <12 hours, but could take longer dependent on size and activity. See Recovery.
Retention 7 days by default, Up to 35 days   Enabled by default, same as source.** Not enabled by default, Retention Up to 10 years.
Azure storage   Geo-redundant by default. Can optionally configure zone or locally redundant storage. Available when PITR backup storage redundancy is set to Geo-redundant. Not available when PITR backup store is zone or locally redundant storage.  Geo-redundant by default. Can configure zone or locally redundant storage.
Use to create new database in same region Supported Supported  Supported
Use to create new database in another region Not Supported Supported in any Azure region Supported in any Azure region
Use to create new database in another Subscription Not Supported Not Supported*** Not Supported***
Restore via Azure portal Yes Yes Yes
Restore via PowerShell Yes Yes Yes
Restore via Azure CLI Yes Yes Yes

* For business-critical applications that require large databases and must ensure business continuity, use Auto-failover groups.

** All PITR backups are stored on geo-redundant storage by default. Hence, geo-restore is enabled by default.

*** Workaround is to restore to a new server and use Resource Move to move the server to another Subscription.

Restoring a database from backups

To perform a restore, see Restore database from backups. You can try backup configuration and restore operations 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
Restore a database from Azure Blob storage
SQL Managed Instance

Backup scheduling

The first full backup is scheduled immediately after a new database is created or restored. This backup usually completes 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 or SQL Managed Instance service as it balances the overall system workload. You cannot change the schedule of backup jobs or disable them. Hyperscale uses a different backup scheduling mechanism, refer to Hyperscale backup scheduling for more details.

Important

For a new, restored, or copied database, point-in-time restore capability becomes available from the time when the initial transaction log backup that follows the initial full backup is created.

Backup storage consumption

With SQL Server backup and restore technology, restoring a database to a point in time requires an uninterrupted backup chain consisting of one full backup, optionally one differential backup, and one or more transaction log backups. Azure SQL Database and Azure SQL Managed Instance backup schedules include one full backup every week. Therefore, 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 is older than the oldest time of the retention period, as well as 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 details see Hyperscale backup scheduling and for more details on how to monitor storage costs see Hyperscale backup storage costs.

Note

To provide PITR, additional backups are stored for up to a week longer than the configured retention period. Backup storage is charged at the same rate for all backups.

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, backups are compressed to reduce backup storage compression and costs. Average backup compression ratio is 3-4 times, however it can be significantly lower or higher depending on the nature of the data and whether data compression is used in the database.

Azure SQL Database and Azure SQL Managed Instance compute your total used backup storage as a cumulative value. Every hour, this value is reported to the Azure billing pipeline, which 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. Once 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. While deleting and re-creating a database may save storage and compute costs, it may increase backup storage costs, because the service retains backups for each deleted database, every time it is deleted.

Monitor consumption

For vCore databases in Azure SQL Database, the storage consumed by each type of backup (full, differential, and log) is reported on the database monitoring pane as a separate metric. The following diagram shows how to monitor the backup storage consumption for a single database. This feature is currently not available for managed instances.

Monitor database backup consumption in the Azure portal

Instructions on how to monitor consumption in Hyperscale can be found in Hyperscale monitor backup consumption

Fine-tune backup storage consumption

Backup storage consumption up to the maximum data size for a database is not charged. Excess backup storage consumption will depend 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 possible for your needs.
  • Avoid doing large write operations, like index rebuilds, more frequently than you need to.
  • For large data load operations, consider using clustered columnstore indexes and following related best practices, and/or reduce the number of non-clustered 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 and/or transient data.
  • Use locally redundant backup storage whenever possible (for example dev/test environments)

Backup retention

Azure SQL Database and Azure SQL Managed Instance provide both short-term and long-term retention of backups. Short-term retention backups allow Point-In-Time-Restore (PITR) within the retention period for the database, while long-term retention provides backups for various compliance requirements.

Short-term retention

For all new, restored, and copied databases, Azure SQL Database and Azure SQL Managed Instance retain sufficient backups to allow PITR within the last seven days by default. Regular full, differential and log backups are taken to ensure databases are restorable to any point-in-time within the retention period defined for the database or managed instance. Short-term back up retention of 1-35 days for Hyperscale databases is now in Preview. To learn more, review Managing backup retention in Hyperscale.

For Azure SQL Database only, differential backups can be configured to either a 12-hour or a 24-hour frequency. A 24-hour differential backup frequency may increase the time required to restore the database.

You can specify your backup storage redundancy option for short-term retention (STR) when you create your Azure SQL resource, and then change it at a later time. If you change your backup redundancy option after your database or instance is created, new backups will use the new redundancy option while backup copies made with the previous STR redundancy option are not moved or copied, but are left in the original storage account until the retention period expires, which can be 7-35 days.

Except for Basic tier databases, you can change backup retention period per each active database in the 1-35 day range. As described in Backup storage consumption, backups stored to enable PITR may be older than the retention period. For Azure SQL Managed Instance only, it is possible to set the PITR backup retention rate once a database has been deleted in the 0-35 days range. 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 it would for an online database with its specific retention period. You cannot change backup retention period for a deleted database.

Important

If you delete a server or a managed instance, all databases on that server or managed instance are also deleted and cannot be recovered. You cannot restore a deleted server or managed instance. But if you had configured long-term retention (LTR) for a database or managed instance, long-term retention backups are not deleted, and can be used to restore databases on a different server or managed instance in the same subscription, to a point in time when a long-term retention backup was taken.

Long-term retention

For both SQL Database and SQL Managed Instance, you can configure full backup long-term retention (LTR) 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 backup retention.

Storage redundancy for long-term retention can be changed after the LTR policy is created for Azure SQL Database, but not Azure SQL Managed Instance.

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.

Important

  • Updating the backup storage redundancy for an existing Azure SQL Database only applies the change to subsequent backups taken in the future for the database. All existing LTR backups for the database will continue to reside in the existing storage blob and new backups will be stored on the newly requested storage blob type.
  • LTR backup storage redundancy in Azure SQL Managed Instance is inherited from the backup storage redundancy used by STR at the time the LTR policy is defined and cannot be changed subsequently, even if the STR backup storage redundancy is changed in the future.
  • Databases in the Hyperscale service tier for Azure SQL Database do not currently support long-term retention.

Backup storage costs

The price for backup storage varies and depends on your purchasing model (DTU or vCore), chosen backup storage redundancy option, and also on your region. The backup storage is charged per GB/month consumed, for pricing see Azure SQL Database pricing page and Azure SQL Managed Instance pricing page.

For more on purchasing models, see Choose between the vCore and DTU purchasing models.

Note

Azure invoice will show only the excess backup storage consumed, not the entire backup storage consumption. For example, in a hypothetical scenario, if you have provisioned 4TB of data storage, you will get 4TB of free backup storage space. In case that you have used the total of 5.8TB of backup storage space, Azure invoice will show only 1.8TB, as only excess backup storage used is charged.

DTU model

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

vCore model

For single databases in SQL Database, a backup storage amount equal to 100 percent of the maximum data storage size for the database is provided at no extra charge. For elastic pools and managed instances, a backup storage amount equal to 100 percent of the maximum data storage for the pool or the maximum instance storage size, respectively, is provided at no extra charge.

For single databases, this 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 pooled databases, the total billable backup storage size 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

For managed instances, the total billable backup storage size is aggregated at the instance level and is calculated as follows:

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

Total billable backup storage, if any, will be charged in GB/month as per the rate of the backup storage redundancy used. This backup storage consumption will depend 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 will have higher backup charges.

Formulae used to calculate backup storage costs for Hyperscale databases can be found in Hyperscale backup storage costs.

Azure SQL Database and Azure SQL Managed Instance compute your total billable backup storage as a cumulative value across all backup files. Every hour, this value is reported to the Azure billing pipeline, which 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. Once all backups are deleted, billing stops.

As a simplified example, assume 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 * 24 hours per day). SQL Database will report to Azure billing pipeline that the database consumed 1 GB of PITR backup each hour, at a constant rate. Azure billing will aggregate this consumption and show a usage of 744 GB for the entire month. The cost will be based on the amount/GB/month rate in your region.

Now, a more complex example. Suppose the same idle database has its retention increased from seven 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/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 vary as well, causing the hourly backup storage consumption to fluctuate accordingly. Furthermore, each differential backup contains all changes made in the database since the last full backup, thus the total size of all differential backups gradually increases over the course of a week, and then drops sharply once an older set of full, differential, and log backups ages out. For example, if a heavy write activity such as index rebuild has been run just after a full backup completed, then the modifications made by the index rebuild will be included in the transaction log backups taken over the duration of rebuild, in the next differential backup, and in every differential backup taken until the next full backup occurs. For the latter 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.

Backup storage redundancy

Backup storage redundancy impacts backup costs in the following way:

  • locally redundant price = x
  • zone-redundant price = 1.25x
  • geo-redundant price = 2x
  • geo-zone redundant price = 3.4x

For more details about backup storage pricing visit Azure SQL Database pricing page and Azure SQL Managed Instance pricing page.

Important

Backup storage redundancy for Hyperscale can only be set during database creation. This setting cannot be modified once the resource is provisioned. Database copy process can be used to update the backup storage redundancy settings for an existing Hyperscale database. Learn more in Hyperscale backups and storage redundancy.

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 as the 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 drop-down list select sql database for a single database or an elastic database pool, or select sql managed instance for managed instance.
  3. Add another filter for Meter subcategory.
  4. To monitor PITR backup costs, in the drop-down list select single/elastic pool pitr backup storage for a single database or an elastic database pool, or select managed instance pitr backup storage for managed instance. Meters will only show up if there exists consumption.
  5. To monitor LTR backup costs, in the drop-down list select ltr backup storage for a single database or an elastic database pool, or select sql managed instance - ltr backup storage for managed instance. Meters will only show up if there exists consumption.

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

Backup storage cost analysis

Important

Meters are only visible for counters that are currently in use. If a counter is not available, it is likely that the category is not currently being used. For example, managed instance counters will not be present for customers who do not have a managed instance deployed. Likewise, storage counters will not be visible for resources that are not consuming storage. For example, if there is no PITR or LTR backup storage consumption, these meters won't be shown.

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 & SQL Managed Instance.

Backup integrity

On an ongoing basis, the Azure SQL engineering team automatically tests the restore of automated database backups. (This testing is not currently available in SQL Managed Instance. You should schedule DBCC CHECKDB on your databases in SQL Managed Instance, scheduled around on your workload.)

Upon point-in-time restore, databases also receive DBCC CHECKDB integrity checks.

Any issues found during the integrity check will 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

This 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.

Change the short-term retention policy

You can change the default PITR backup retention period and the differential backup frequency by using the Azure portal, PowerShell, or the REST API. The following examples illustrate how to change the PITR retention to 28 days and the differential backups to 24 hour interval.

Warning

If you reduce the current retention period, you lose the ability to restore to points in time older than the new retention period. Backups that are no longer needed to provide PITR within the new retention period are deleted. If you increase the current retention period, you do not immediately gain the ability to restore to older points in time within the new retention period. You gain that ability over time, as the system starts to retain backups for longer.

Note

These APIs will affect only the PITR retention period. If you configured LTR for your database, it won't be affected. For information about how to change LTR retention periods, see Long-term retention.

Change the short-term retention policy using the Azure portal

To change the PITR backup retention period or the differential backup frequency for active databases by using the Azure portal, go to the server or managed instance with the databases whose retention period you want to change. Select Backups in the left pane, then select the Retention policies tab. Select the database(s) for which you want to change the PITR backup retention. Then select Configure retention from the action bar.

Change the short-term retention policy using Azure CLI

Prepare your environment for the Azure CLI.

Change the PITR backup retention and differential backup frequency for active Azure SQL Databases by using the following example.

# Set new PITR differential backup frequency on an active individual database
# Valid backup retention must be between 1 and 35 days
# Valid differential backup frequency must be ether 12 or 24
az sql db str-policy set \
    --resource-group myresourcegroup \
    --server myserver \
    --name mydb \
    --retention-days 28 \
    --diffbackup-hours 24

Change the short-term retention policy using PowerShell

Note

This article uses the Azure Az PowerShell module, which is the recommended PowerShell module for interacting with Azure. To get started with the Az PowerShell module, see Install Azure PowerShell. To learn how to migrate to the Az PowerShell module, see Migrate Azure PowerShell from AzureRM to Az.

Important

The PowerShell AzureRM module is still supported by SQL Database and SQL Managed Instance, but all future development is for the Az.Sql module. For more information, see AzureRM.Sql. The arguments for the commands in the Az module are substantially identical to those in the AzureRm modules.

To change the PITR backup retention and differential backup frequency for active Azure SQL Databases, use the following PowerShell example.

# SET new PITR backup retention period on an active individual database
# Valid backup retention must be between 1 and 35 days
Set-AzSqlDatabaseBackupShortTermRetentionPolicy -ResourceGroupName resourceGroup -ServerName testserver -DatabaseName testDatabase -RetentionDays 28
# SET new PITR differential backup frequency on an active individual database
# Valid differential backup frequency must be ether 12 or 24. 
Set-AzSqlDatabaseBackupShortTermRetentionPolicy -ResourceGroupName resourceGroup -ServerName testserver -DatabaseName testDatabase -RetentionDays 28 -DiffBackupIntervalInHours 24

Change the short-term retention policy using the REST API

The below request updates the retention period to 28 days and also sets the differential backup frequency to 24 hours.

Sample Request

PUT https://management.azure.com/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/resourceGroup/providers/Microsoft.Sql/servers/testserver/databases/testDatabase/backupShortTermRetentionPolicies/default?api-version=2021-02-01-preview

Request Body

{ 
    "properties":{
        "retentionDays":28,
        "diffBackupIntervalInHours":24
  }
}

Sample Response:

{ 
  "id": "/subscriptions/00000000-1111-2222-3333-444444444444/providers/Microsoft.Sql/resourceGroups/resourceGroup/servers/testserver/databases/testDatabase/backupShortTermRetentionPolicies/default",
  "name": "default",
  "type": "Microsoft.Sql/resourceGroups/servers/databases/backupShortTermRetentionPolicies",
  "properties": {
    "retentionDays": 28,
    "diffBackupIntervalInHours":24
  }
}

For more information, see Backup Retention REST API.

Hyperscale backups and storage redundancy

Hyperscale databases in Azure SQL Database use a unique architecture with highly scalable storage and compute performance tiers.

Hyperscale backups are snapshot based and are nearly instantaneous. Log generated is stored in long term Azure storage for the backup retention period. Hyperscale architecture does not use full database backups or log backups and hence the backup frequency, storage costs, scheduling, storage and redundancy and restore capabilities described in the previous sections of this article do not apply.

Hyperscale backup and restore performance

Storage and compute separation enables Hyperscale to push down backup and restore operation to the storage layer to reduce the processing burden on the primary compute replica. As a result, database backups don't impact performance of the primary compute node.

Backup and restore operations for Hyperscale databases are fast regardless of data size due to the use of storage snapshots. A database can be restored to any point in time within its backup retention period. Point in time recovery (PITR) is achieved by reverting to file snapshots, and as such is not a size of data operation. Restore of a Hyperscale database within the same Azure region is a constant-time operation, and even multiple-terabyte databases can be restored in minutes instead of hours or days. Creation of new databases by restoring an existing backup or copying the database also takes advantage of this feature: creating database copies for development or testing purposes, even of multi-terabyte databases, is doable in minutes within the same region when the same storage type is used.

Hyperscale backup retention

Default short-term backup retention (STR) for Hyperscale databases is 7 days; long-term retention (LTR) policies aren't currently supported.

Note

Short-term backup retention up to 35 days for Hyperscale databases is now in preview.

Hyperscale backup scheduling

There are no traditional full, differential, and transaction log backups for Hyperscale databases. Instead, regular storage snapshots of data files are taken. The generated transaction log is retained as-is for the configured retention period. At restore time, relevant transaction log records are applied to the restored storage snapshot, resulting in a transactionally-consistent database without any data loss as of the specified point in time within the retention period.

Hyperscale backup storage costs

Hyperscale backup storage cost depends on the choice of region and backup storage redundancy. It also depends on the workload type. Write-heavy workloads are more likely to change data pages frequently, which results in larger storage snapshots. Such workloads also generate more transaction log, contributing to the overall backup costs. Backup storage is charged per GB/month consumed, for pricing details see the Azure SQL Database pricing page.

For Hyperscale, billable backup storage is calculated as follows:

Total billable backup storage size = (Data backup storage size + Log backup storage size)

Data storage size is not included in the billable backup as it is already billed as allocated database storage.

Deleted Hyperscale databases incur backup costs to support recovery to a point in time before deletion. For a deleted Hyperscale database, billable backup storage is calculated as follows:

Total billable backup storage size for deleted Hyperscale database = (Data storage size + Data backup size + Log backup storage size) * (remaining backup retention period after deletion/configured backup retention period)

Data storage size is included in the formula because allocated database storage is not billed separately for a deleted database. For a deleted database, data is stored post deletion to enable recovery during the configured backup retention period. Billable backup storage for a deleted database reduces gradually over time after it is deleted. It becomes zero when backups are no longer retained, and recovery is no longer possible. However if it is a permanent deletion and backups are no longer needed, to optimize costs you can reduce retention before deleting the database.

Hyperscale monitor backup consumption

In Hyperscale, data backup storage size (snapshot backup size), data storage size(database size) and log backup storage size(transactions log backup size) are reported via Azure Monitor metrics.

To view backup and data storage metrics in the Azure portal, follow these steps: :

  1. Go to the Hyperscale database for which you'd like to monitor backup and data storage metrics.
  2. Select the Metrics page in the Monitoring section.

Screenshot of the Azure portal showing the Hyperscale Backup storage metrics

  1. From the Metric drop-down list, select the Data backup Storage and Log Backup Storage metrics with an appropriate aggregation rule.

Reduce backup storage consumption

Backup storage consumption for a Hyperscale database depends on the retention period, choice of region, backup storage redundancy and workload type. Consider some of the following tuning techniques to reduce your backup storage consumption for a Hyperscale database:

  • Reduce the backup retention period to the minimum possible for your needs.
  • Avoid doing large write-operations, such as index maintenance, more frequently than you need to. For index maintenance recommendations, see Optimize index maintenance to improve query performance and reduce resource consumption.
  • For large data-load operations, consider using data compression when appropriate.
  • Use the tempdb database instead of permanent tables in your application logic to store temporary results and/or transient data.
  • Use locally-redundant or zone-redundant backup storage when geo-restore capability is unnecessary (for example: dev/test environments).

Hyperscale storage redundancy applies to both data storage and backup storage

Hyperscale supports configurable storage redundancy. When creating a Hyperscale database, you can choose your preferred storage type: read-access geo-redundant storage (RA-GRS), zone-redundant storage (ZRS), or locally redundant storage (LRS) Azure standard storage. The selected storage redundancy option is used for the lifetime of the database for both data storage redundancy and backup storage redundancy.

Consider storage redundancy carefully when you create a Hyperscale database

Backup storage redundancy for Hyperscale databases can only be set during database creation. This setting cannot be modified once the resource is provisioned. Geo-restore is only available when geo-redundant storage (RA-GRS) has been chosen for backup storage redundancy. The database copy process can be used to update the storage redundancy settings for an existing Hyperscale database. Copying a database to a different storage type will be a size-of-data operation. Find example code in configure backup storage redundancy.

Important

Zone-redundant storage is currently only available in certain regions.

Restoring a Hyperscale database to a different region

If you need to restore a Hyperscale database in Azure SQL Database to a region other than the one it's currently hosted in, as part of a disaster recovery operation or drill, relocation, or any other reason, the primary method is to do a geo-restore of the database. This involves exactly the same steps as what you would use to restore any other database in SQL Database to a different region:

  1. Create a server in the target region if you don't already have an appropriate server there. This server should be owned by the same subscription as the original (source) server.
  2. Follow the instructions in the geo-restore section of the page on restoring a database in Azure SQL Database from automatic backups.

Note

Because the source and target are in separate regions, the database cannot share snapshot storage with the source database as in non-geo restores, which complete quickly regardless of database size. In the case of a geo-restore of a Hyperscale database, it will be a size-of-data operation, even if the target is in the paired region of the geo-replicated storage. Therefore, a geo-restore will take time proportional to the size of the database being restored. If the target is in the paired region, data transfer will be within a region, which will be significantly faster than a cross-region data transfer, but it will still be a size-of-data operation.

If you prefer, you can copy the database to a different region as well. Learn about Database Copy for Hyperscale.

Configure backup storage redundancy

Backup storage redundancy for databases in Azure SQL Database can be configured at the time of database creation or can be updated for an existing database; the changes made to an existing database apply to future backups only. The default value is geo-redundant storage. For differences in pricing between locally redundant, zone-redundant and geo-redundant backup storage visit managed instance pricing page. Storage redundancy for Hyperscale databases is unique: learn more in Hyperscale backups and storage redundancy.

For Azure SQL Managed Instance, backup storage redundancy is set at the instance level, and it is applied for all belonging managed databases. It can be configured at the time of an instance creation or updated for existing instances; the backup storage redundancy change would trigger then a new full backup per database and the change will apply for all future backups. The default storage redundancy type is geo-redundancy (RA-GRS).

Configure backup storage redundancy by using the Azure portal

In Azure portal, you can configure the backup storage redundancy on the Create SQL Database pane. The option is available under the Backup Storage Redundancy section.

Open Create SQL Database pane

Configure backup storage redundancy by using the Azure CLI

To configure backup storage redundancy when creating a new database, you can specify the --backup-storage-redundancy parameter with the az sql db create command. Possible values are Geo, Zone, and Local. By default, all databases in Azure SQL Database use geo-redundant storage for backups. Geo-restore is disabled if a database is created or updated with local or zone redundant backup storage.

This example creates a database in the General Purpose service tier with local backup redundancy:

az sql db create \
    --resource-group myresourcegroup \
    --server myserver \
    --name mydb \
    --tier GeneralPurpose \
    --backup-storage-redundancy Local

Carefully consider the configuration option for --backup-storage-redundancy when creating a Hyperscale database. Storage redundancy can only be specified during the database creation process for Hyperscale databases. The selected storage redundancy option will be used for the lifetime of the database for both data storage redundancy and backup storage redundancy. Learn more in Hyperscale backups and storage redundancy.

Existing Hyperscale databases can migrate to different storage redundancy using database copy or point in time restore: sample code to copy a Hyperscale database follows in this section.

This example creates a database in the Hyperscale service tier with Zone redundancy:

az sql db create \
    --resource-group myresourcegroup \
    --server myserver \
    --name mydb \
    --tier Hyperscale \
    --backup-storage-redundancy Zone

For more information, see az sql db create and az sql db update.

Except for Hyperscale and Basic tier databases, you can update the backup storage redundancy setting for an existing database with the --backup-storage-redundancy parameter and the az sql db update command. It may take up to 48 hours for the changes to be applied on the database. Switching from geo-redundant backup storage to local or zone redundant storage disables geo-restore.

This example code changes the backup storage redundancy to Local.

az sql db update \
    --resource-group myresourcegroup \
    --server myserver \
    --name mydb \
    --backup-storage-redundancy Local

You cannot update the backup storage redundancy of a Hyperscale database directly. However, you can change it using the database copy command with the --backup-storage-redundancy parameter. This example copies a Hyperscale database to a new database using Gen5 hardware and two vCores. The new database has the backup redundancy set to Zone.

az sql db copy \
    --resource-group myresourcegroup \ 
    --server myserver 
    --name myHSdb 
    --dest-resource-group mydestresourcegroup 
    --dest-server destdb 
    --dest-name myHSdb 
    --service-objective HS_Gen5_2 
    --read-replicas 0 
    --backup-storage-redundancy Zone

For syntax details, see az sql db copy. For an overview of database copy, visit Copy a transactionally consistent copy of a database in Azure SQL Database.

Configure backup storage redundancy by using PowerShell

To configure backup storage redundancy when creating a new database, you can specify the -BackupStorageRedundancy parameter with the New-AzSqlDatabase cmdlet. Possible values are Geo, Zone, and Local. By default, all databases in Azure SQL Database use geo-redundant storage for backups. Geo-restore is disabled if a database is created with local or zone redundant backup storage.

This example creates a database in the General Purpose service tier with local backup redundancy:

# Create a new database with geo-redundant backup storage.  
New-AzSqlDatabase -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -DatabaseName "Database03" -Edition "GeneralPurpose" -Vcore 2 -ComputeGeneration "Gen5" -BackupStorageRedundancy Local

Carefully consider the configuration option for --backup-storage-redundancy when creating a Hyperscale database. Storage redundancy can only be specified during the database creation process for Hyperscale databases. The selected storage redundancy option will be used for the lifetime of the database for both data storage redundancy and backup storage redundancy. Learn more in Hyperscale backups and storage redundancy.

Existing databases can migrate to different storage redundancy using database copy or point in time restore: sample code to copy a Hyperscale database follows in this section.

This example creates a database in the Hyperscale service tier with Zone redundancy:

# Create a new database with geo-redundant backup storage.  
New-AzSqlDatabase -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -DatabaseName "Database03" -Edition "Hyperscale" -Vcore 2 -ComputeGeneration "Gen5" -BackupStorageRedundancy Zone

For syntax details visit New-AzSqlDatabase.

Except for Hyperscale and Basic tier databases, you can use the -BackupStorageRedundancy parameter with the Set-AzSqlDatabase cmdlet to update the backup storage redundancy setting for an existing database. Possible values are Geo, Zone, and Local. It may take up to 48 hours for the changes to be applied on the database. Switching from geo-redundant backup storage to local or zone redundant storage disables geo-restore.

This example code changes the backup storage redundancy to Local.

# Change the backup storage redundancy for Database01 to zone-redundant. 
Set-AzSqlDatabase -ResourceGroupName "ResourceGroup01" -DatabaseName "Database01" -ServerName "Server01" -BackupStorageRedundancy Local

For details visit Set-AzSqlDatabase

Backup storage redundancy of an existing Hyperscale database cannot be updated. However, you can use the database copy command to create a copy of the database and use the -BackupStorageRedundancy parameter to update the backup storage redundancy. This example copies a Hyperscale database to a new database using Gen5 hardware and two vCores. The new database has the backup redundancy set to Zone.

# Change the backup storage redundancy for Database01 to zone-redundant. 
New-AzSqlDatabaseCopy -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -DatabaseName "HSSourceDB" -CopyResourceGroupName "DestResourceGroup" -CopyServerName "DestServer" -CopyDatabaseName "HSDestDB" -Vcore 2 -ComputeGeneration "Gen5" -ComputeModel Provisioned -BackupStorageRedundancy Zone

For syntax details, visit New-AzSqlDatabaseCopy.

For an overview of database copy, visit Copy a transactionally consistent copy of a database in Azure SQL Database.

Note

To use -BackupStorageRedundancy parameter with database restore, database copy or create secondary operations, use Azure PowerShell version Az.Sql 2.11.0.

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 may want to enforce zone-redundant or locally redundant backups for your SQL Database or Managed Instance 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

Following new built-in policies are added, which can be assigned at the subscription or resource group level to block creation of new database(s) or instance(s) with geo-redundant backup storage.

SQL Database should avoid using GRS backup redundancy

SQL Managed Instances should avoid using GRS backup redundancy

A full list of built-in policy definitions for SQL Database and Managed Instance can be found here.

To enforce data residency requirements at an organizational level, these policies can be assigned to a subscription. After these policies are assigned at a subscription level, users in the given subscription will not be able to create a database or a managed instance with geo-redundant backup storage via Azure portal or Azure PowerShell.

Important

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

Learn how to assign policies using the Azure portal or Azure PowerShell

Next steps