Learn about automatic SQL Database backups

SQL Database automatically creates database backups and uses Azure read-access geo-redundant storage (RA-GRS) to provide geo-redundancy. These backups are created automatically and at no additional charge. You don't need to do anything to make them happen. Database backups are an essential part of any business continuity and disaster recovery strategy because they protect your data from accidental corruption or deletion. If your security rules require that your backups are available for an extended period of time, you can configure a long-term backup retention policy. For more information, see Long-term retention.

Note

This article provides steps for how to delete personal data from the device or service and can be used to support your obligations under the GDPR. If you’re looking for general info about GDPR, see the GDPR section of the Service Trust portal.

What is a SQL Database backup?

SQL Database uses SQL Server technology to create full, differential, and transaction log backups for the purposes of Point-in-time restore (PITR). The transaction log backups generally happen every 5 - 10 minutes, with the frequency based on the performance level and amount of database activity. Transaction log backups, with full and differential backups, allow you to restore a database to a specific point-in-time to the same server that hosts the database. When you restore a database, the service figures out which full, differential, and transaction log backups need to be restored.

You can use these backups to:

  • Restore a database to a point-in-time within the retention period. This operation will create a new database in the same server as the original database.
  • Restore a deleted database to the time it was deleted or any time within the retention period. The deleted database can only be restored in the same server where the original database was created.
  • Restore a database to another geographical region. This allows you to recover from a geographic disaster when you cannot access your server and database. It creates a new database in any existing server anywhere in the world.
  • Restore a database from a specific long-term backup if the database has been configured with a long-term retention policy (LTR). This allows you to restore an old version of the database to satisfy a compliance request or to run an old version of the application. See Long-term retention.
  • To perform a restore, see restore database from backups.

Note

In Azure storage, the term replication refers to copying files from one location to another. SQL's database replication refers to keeping multiple secondary databases synchronized with a primary database.

How long are backups kept?

Each SQL Database backup has a default retention period that is based on the service tier of the database, and differs between the DTU-based purchasing model and the vCore-based purchasing model (preview). You can update the backup retention period for a database. See Change Backup Retention Period for more details.

If you delete a database, SQL Database will keep the backups in the same way it would for an online database. For example, if you delete a Basic database that has a retention period of seven days, a backup that is four days old is saved for three more days.

If you need to keep the backups for longer than the maximum PITR retention period, you can modify the backup properties to add one or more long-term retetion periods to your database. See Long-term backup retention for more details.

Important

If you delete the Azure SQL server that hosts SQL databases, all elastic pools and databases that belong to the server are also deleted and cannot be recovered. You cannot restore a deleted server. But if you configured long-term retention, the backups for the databases with LTR will not be deleted and these databases can be restored.

PITR Retention for DTU-based service tiers

The default retention period for a database created using the DTU-based purchasing model depends on the service tier:

  • Basic service tier is 1 week.
  • Standard service tier is 5 weeks.
  • Premium service tier is 5 weeks.

If you reduce the current PITR retention period, all existing backups older than the new retention period will no longer be available.

If you increase the current PITR retention period, SQL Database will keep the existing backups until the longer retention period is reached.

PITR Retention for the vCore-based service tiers (preview)

During preview, the PITR retention period for databases created using the vCore-based purchasing model is set to 7 days. The associated storage is included for free.

How often do backups happen?

Backups for point-in-time restore

SQL Database supports self-service for point-in-time restore (PITR) by automatically creating full backup, differential backups, and transaction log backups. Full database backups are created weekly, differential database backups are created every few hours, and transaction log backups are created every 5 - 10 minutes. The first full backup is scheduled immediately after a database is created. It usually completes within 30 minutes, but it can take longer when the database is of a significant size. For example, the initial backup can take longer on a restored database or a database copy. After the first full backup, all further backups are scheduled automatically and managed silently in the background. The exact timing of all database backups is determined by the SQL Database service as it balances the overall system workload.

The PITR backups are geo-redundant and protected by Azure Storage cross-regional replication

For more information, see Point-in-time restore

Backups for long-term retention

SQL Database offers the option of configuring long-term retention (LTR) of full backups for up to 10 years. If LTR policy is enabled, the weekly full backups are automatically copied to a different RA-GRS storage container. To meet different compliance requirement, you can select different retention periods for weekly, monthly and/or yearly backups. The storage consumption depends on the selected frequency of backups and the retention period(s). You can use the LTR pricing calculator to estimate the cost of LTR storage.

Like PITR, the LTR backups are geo-redundant and protected by Azure Storage cross-regional replication.

For more information, see Long-term backup retention.

Are backups encrypted?

If your database is encrypted with TDE, the backups are automatically encrypted at rest, including LTR backups. When TDE is enabled for an Azure SQL database, backups are also encrypted. All new Azure SQL databases are configured with TDE enabled by default. For more information on TDE, see Transparent Data Encryption with Azure SQL Database.

How do automated backups impact my compliance?

When you migrate your database from a DTU-based service tier with the default PITR retention of 35 days, to a vCore-based service tier, the PITR retention is preserved to ensure that your application's data recovery policy is not compromized. If the default retention doesn't meet your compliance requirements, you can change the PITR retention period using PowerShell or REST API. See Change Backup Retention Period for more details.

Note

This article provides steps for how to delete personal data from the device or service and can be used to support your obligations under the GDPR. If you’re looking for general info about GDPR, see the GDPR section of the Service Trust portal.

How to change backup retention period

You can change the default retention using REST API or PowerShell. The supported values are: 7, 14, 21, 28 or 35 days.The following examples illustrate how to change PITR retention to 28 days.

Note

Thes APIs will only impact the PITR retention period. If you configured LTR for your database, it will not be impacted. See Long-term backup retention for details of how to change the LTR retention period(s).

Change PITR backup retention period using PowerShell

Set-AzureRmSqlDatabaseBackupShortTermRetentionPolicy -ResourceGroupName resourceGroup -ServerName testserver -DatabaseName testDatabase -RetentionDays 28

Important

This API is included in AzureRM.Sql PowerShell Module starting from version 4.7.0-preview.

Change PITR retention period using REST API

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=2017-10-01-preview

Request Body

{
  "properties":{  
      "retentionDays":28
   }
}

Sample Response

Status code: 200

{
  "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
  }
}

See Backup Retention REST API for more details.

Next steps