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.
Important
- You can't overwrite an existing database during restore.
- Database restore operations don't restore the tags of the original database.
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.
Important
- You can perform a point-in-time restore of a database to the same server. Cross-server, cross-subscription and cross-geo point-in-time restore is not currently supported. To restore a database to a different region using geo-replicated backups see Geo-restore.
- You can't perform a point-in-time restore on a geo-secondary database. You can do so only on a primary database.
- The
BackupFrequencyparameter isn't supported for Hyperscale databases. - Database restore operations are resource-intensive and might require a service tier of S3 or greater for the restoring (target) database. Once restore completes, the database or elastic pool might be scaled down, if required.
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.
To recover a database to a point in time by using the Azure portal, open the database overview page and select Restore on the toolbar to open the Create SQL Database - Restore database page:
On the Create SQL Database - Restore database page, specify the source for the backup and then select the point-in-time backup point from which a new database will be created. Since the chosen database is to be restored to the current server, the source database and target server are grayed out.
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.
To recover a long-term backup by using the Azure portal, go to your logical server. Select Backups under Data Management, and then select Manage under Available LTR backups for the database you're trying to restore.
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.
Important
If you delete a server, all of its databases and their PITR backups are also deleted. You can't restore a deleted server, and you can't restore the deleted databases from PITR backups. If you had configured LTR backups for those databases, you can use those backups to restore the databases to a different server.
To recover a deleted database to the deletion time by using the Azure portal, open the server's overview page and select Deleted databases. Select a deleted database that you want to restore, and then enter the name for the new database that will be created with data restored from the backup.
Tip
It might take several minutes for recently deleted databases to appear on the Deleted databases page in the Azure portal, or when you want to display deleted databases programmatically.
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.
Important
- Geo-restore is available only for databases configured with geo-redundant backup storage. If you're not currently using geo-replicated backups for a database, you can change this by configuring backup storage redundancy.
- You can perform geo-restore only on databases that reside in the same subscription.
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.
From the Azure portal, you create a new single database and select an available geo-restore backup. The newly created database contains the geo-restored backup data.
To geo-restore a single database from the Azure portal in the region and server of your choice, follow these steps:
- Open the Create SQL Database pane in the Azure portal. On the Basics tab, enter the required information.
- Select Additional settings.
- For Use existing data, select Backup.
- Select a backup from the list of available geo-restore backups.
Complete the process of creating a database from the backup. When you create a database in Azure SQL Database, it contains the restored geo-restore backup.
Geo-restore considerations
For more information on using geo-restore, see Recovery using Geo-restore.
Note
For detailed information about recover from an outage, see disaster recovery guidance and the high availability and disaster recovery checklist.
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.
Note
If you plan to use geo-restore as disaster-recovery solution, we recommend that you conduct periodic drills to verify application tolerance to any loss of recent data modifications, along with all operational aspects of the recovery procedure.
Restore database to another server
You can use the following methods to restore a database to another server: