Lue englanniksi Muokkaa

Jaa

Change automated backup settings for Azure SQL Database

  • Artikkeli

Applies to: Azure SQL Database

This article provides examples to modify automated backup settings for Azure SQL Database, such as the short-term retention policy and the backup storage redundancy option that's used for backups. For Azure SQL Managed Instance, see Change automated backup settings for Azure SQL Managed Instance.

Huomautus

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 short-term retention policy

You can change the default point-in-time recovery (PITR) backup retention period and the differential backup frequency by using the Azure portal, the Azure CLI, PowerShell, or the REST API. The following examples illustrate how to change the PITR retention to 28 days and the differential backups to a 24-hour interval.

Varoitus

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 don't 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 periods.

Huomautus

  • These APIs will affect only the PITR retention period. If you configured long-term retention (LTR) for your database, it won't be affected. For information about how to change long-term retention periods, see Long-term retention.
  • Hyperscale databases don't support configuring the differential backup frequency because differential backups don't apply to Hyperscale databases.

Prepare your environment for the Azure CLI:

Change the PITR backup retention and differential backup frequency for active databases by using the following example:

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

Configure backup storage redundancy

You can configure backup storage redundancy for databases in Azure SQL Database when you create your database. You can also change the storage redundancy after the database is already created.

Backup storage redundancy changes made to existing databases 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, see the SQL Database pricing page.

Storage redundancy for Hyperscale databases is unique. To learn more, review Hyperscale backup storage redundancy.

To configure backup storage redundancy when you're 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 locally redundant or zone-redundant backup storage.

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

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

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

This example code changes the backup storage redundancy to Local:

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

Hyperscale

Carefully consider the configuration option for --backup-storage-redundancy when you're creating a Hyperscale database. The storage redundancy can be specified only during the database creation process for Hyperscale databases. You can't update it later. 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 backup storage redundancy.

Existing Hyperscale databases can migrate to different storage redundancy through active geo-replication, which causes minimal downtime. Alternatively, you can migrate to a different storage redundancy by using database copy or point-in-time restore. This example creates a database in the Hyperscale service tier with zone redundancy:

Azure CLI 
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.

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

Azure CLI 
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, see Copy a transactionally consistent copy of a database in Azure SQL Database.

Next steps