Automated Backups

In this tutorial we will learn about automated backups.

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. However, if your data protection rules require that your backups are available for an extended time, you can configure long-term retention for both single and pooled databases.

Backup frequency

Both 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 5 to 10 minutes. However, the frequency of transaction log backups is based on the compute size and the amount of database activity.

Backup storage redundancy

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

However, the option to configure backup storage redundancy provides the flexibility to choose between locally-redundant, zone-redundant, or geo-redundant storage blobs for a SQL Managed Instance or a SQL Database. Further, to ensure that your data stays within the same region where your managed instance or SQL database is deployed, you can change the default geo-redundant backup storage redundancy and configure either locally-redundant or zone-redundant storage blobs for backups. Storage redundancy mechanisms store 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.

Backup usage

You can use these backups to:

Firstly, 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 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. 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.
Secondly, 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.
Lastly, 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.

Backup 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 7 days by default. With the exception of Hyperscale 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. Further, 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.

However, 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.

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 configuration, full backups are automatically copies to a different storage container weekly. However, to meet various compliance requirements, you can select different retention periods for weekly, monthly, and/or yearly full backups. 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.

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.

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. However, 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.

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. However, if the default retention doesn’t meet your compliance requirements, you can change the PITR retention period.

Change the PITR backup retention period

You can change the default PITR backup retention period by using the Azure portal, PowerShell, or the REST API. The following examples illustrate how to change the PITR retention to 28 days.

Change the PITR backup retention period by using the Azure portal

To change the PITR backup retention period 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.

SQL Database

Firstly, changes to PITR backup retention for SQL Database are done on the server page in the portal.
Secondly, to change PITR retention for databases on a server, go to the server overview blade.
Then, select Manage Backups in the left pane.
After that, select the databases in scope of your change, and then select Configure retention at the top of the screen.

Change the PITR backup retention period by using PowerShell

SQL Database

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

PowerShell

# 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

Change the PITR backup retention period by using the REST API

Sample request

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

Request body

JSON
{
“properties”:{
“retentionDays”:28
}
}

Sample response
Status code: 200

JSON
{
“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
}
}

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. Then, 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. Moreover, Azure Policy helps you to keep these resources compliant with your corporate standards and service level agreements.