Restore a database from a backup in Azure SQL Database

Applies to: Azure SQL Database

This article provides steps to recover any database from a backup in Azure SQL Database, including Hyperscale databases.

Overview

Automated database backups help protect your databases from user and application errors, accidental database deletion, and prolonged outages. This built-in capability is available for all service tiers and compute sizes. The following options are available for database recovery through automated backups:

• Create a new database on the same server, recovered to a specified point in time within the retention period with Point-in-time restore.
• Create a database on the same server, recovered to the deletion time for a deleted database.
• Create a new database on any server in the same region, recovered to the time of a recent backup with long-term retention restore or geo-restore.
• Create a new database on any server in any other region, recovered to the point of the most recent replicated backups with geo-restore.

If you configured long-term retention (LTR), you can also create a new database from any long-term retention backup on any server.

When you're using the Standard or Premium service tier in the DTU purchasing model, your database restore might incur an extra storage cost. The extra cost happens when the maximum size of the restored database is greater than the amount of storage included with the target database's service tier and service objective.

For pricing details of extra storage, see the SQL Database pricing page. If the actual amount of used space is less than the amount of storage included, you can avoid this extra cost by setting the maximum database size to the included amount.

Recovery time

Several factors affect the recovery time to restore a database through automated database backups:

• The size of the database
• The compute size of the database
• The number of transaction logs involved
• The amount of activity that needs to be replayed to recover to the restore point
• The network bandwidth if the restore is to a different region
• The number of concurrent restore requests that are processed in the target region

For a large or very active database, the restore might take several hours. A prolonged outage in a region might cause a high number of geo-restore requests for disaster recovery. When there are many requests, the recovery time for individual databases can increase. Most database restores finish in less than 12 hours.

For a single subscription, you have the following limitations on the number of concurrent restore requests. These limitations apply to any combination of point-in-time restores, geo-restores, and restores from long-term retention backup.

Deployment option	Max # of concurrent requests being processed	Max # of concurrent requests being submitted
Single database (per subscription)	30	100
Elastic pool (per pool)	4	2,000

Permissions

To recover by using automated backups, you must be either:

• A member of the Contributor role or the SQL Server Contributor role in the subscription or resource group that contains the logical server
• The subscription or resource group owner

For more information, see Azure RBAC: Built-in roles.

You can recover by using the Azure portal, PowerShell, or the REST API. You can't use Transact-SQL.

Point-in-time restore

You can restore any database to an earlier point in time within its retention period. The restore request can specify any service tier or compute size for the restored database. When you're restoring a database into an elastic pool, ensure that you have sufficient resources in the pool to accommodate the database.

When the restore is complete, it creates a new database on the same server as the original database. The restored database is charged at normal rates, based on its service tier and compute size. You don't incur charges until the database restore is complete.

You generally restore a database to an earlier point for recovery purposes. You can treat the restored database as a replacement for the original database or use it as a data source to update the original database.

• Database replacement

  If you want the restored database to be a replacement for the original database, you should specify the original database's compute size and service tier. You can then rename the original database and give the restored database the original name by using the ALTER DATABASE command in T-SQL.

• Data recovery

  If you plan to retrieve data from the restored database to recover from a user or application error, you need to write and run a data recovery script that extracts data from the restored database and applies to the original database. Although the restore operation might take a long time to complete, the restoring database is visible in the database list throughout the restore process.

  If you delete the database during the restore, the restore operation is canceled. You won't be charged for the database that didn't complete the restore.

Long-term backup restore

To perform a restore operation on a long-term backup, you can use the Azure portal, the Azure CLI, Azure PowerShell, or the REST API. For more information, see Restore a long-term backup.

Restore deleted database

You can restore a deleted database to the deletion time, or an earlier point in time, on the same server by using the Azure portal, the Azure CLI, Azure PowerShell, and the REST API.

Geo-restore

You can use geo-restore to restore a deleted database by using the Azure portal, the Azure CLI, Azure PowerShell, and the REST API.

Geo-restore uses geo-replicated backups as the source. You can restore a database on any logical server in any Azure region from the most recent geo-replicated backups. You can request a geo-restore even if an outage has made the database or the entire region inaccessible.

Geo-restore is the default recovery option when your database is unavailable because of an incident in the hosting region. You can restore the database to a server in any other region.

There's a delay between when a backup is taken and when it's geo-replicated to an Azure blob in a different region. As a result, the restored database can be up to one hour behind the original database. The following illustration shows a database restore from the last available backup in another region.

Geo-restore considerations

For more information on using geo-restore, see Recovery using Geo-restore.

Geo-restore is the most basic disaster-recovery solution available in SQL Database. It relies on automatically created geo-replicated backups with a recovery point objective (RPO) of up to 1 hour and an estimated recovery time objective (RTO) of up to 12 hours. It doesn't guarantee that the target region will have the capacity to restore your databases after a regional outage, because a sharp increase of demand is likely. If your application uses relatively small databases and isn't critical to the business, geo-restore is an appropriate disaster-recovery solution.

For business-critical applications that require large databases and must ensure business continuity, use failover groups. That feature offers a much lower RPO and RTO, and the capacity is always guaranteed.

For more information about business continuity choices, see Overview of business continuity.

Restore database to another server

You can use the following methods to restore a database to another server:

• Long-term backup restore
• Geo-restore
• Database copy
• Active geo-replication

Related content

• Automated backups
• Long-term retention
• Active geo-replication
• Failover groups overview & best practices