Recover using automated database backups - Azure SQL Database & SQL Managed Instance
APPLIES TO: Azure SQL Database Azure SQL Managed Instance
The following options are available for database recovery by using automated database backups. You can:
- Create a new database on the same server, recovered to a specified point in time within the retention period.
- 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 point of the most recent backups.
- Create a new database on any server in any other region, recovered to the point of the most recent replicated backups.
If you configured backup long-term retention, you can also create a new database from any long-term retention backup on any server.
You can't overwrite an existing database during restore.
When you're using the Standard or Premium service tier, your database restore might incur an extra storage cost. The extra cost is incurred when the maximum size of the restored database is greater than the amount of storage included with the target database's service tier and performance level. 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.
The recovery time to restore a database by using automated database backups is affected by several factors:
- 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 being processed in the target region.
For a large or very active database, the restore might take several hours. If there is a prolonged outage in a region, it's possible that a high number of geo-restore requests will be initiated 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, there are 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||2000|
There isn't a built-in method to restore the entire server. For an example of how to accomplish this task, see Azure SQL Database: Full server recovery.
To recover by using automated backups, you must be a member of the SQL Server Contributor role or SQL Managed Instance Contributor role (depending on the recovery destination) in the subscription, or you must be the subscription 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.
You can restore a standalone, pooled, or instance database to an earlier point in time by using the Azure portal, PowerShell, or the REST API. The request can specify any service tier or compute size for the restored database. Ensure that you have sufficient resources on the server to which you are restoring the database.
When complete, the restore 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.
You can only run restore on the same server, cross-server restoration is not supported by Point-in-time restore.
If you intend 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.
If you plan to retrieve data from the restored database to recover from a user or application error, you need to write and execute a data recovery script that extracts data from the restored database and applies to the original database. Although the restore operation may 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 will be canceled and you will not be charged for the database that did not complete the restore.
Point-in-time restore by using Azure portal
You can recover a single or instance database to a point in time from the overview blade of the database you want to restore in the Azure portal.
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. Choose the backup source, and select the point-in-time backup point from which a new database will be created.
SQL Managed Instance
To recover a managed instance database to a point in time by using the Azure portal, open the database overview page, and select Restore on the toolbar. Choose the point-in-time backup point from which a new database will be created.
To programmatically restore a database from a backup, see Programmatic recovery using automated backups.
Deleted database restore
You can restore a deleted database to the deletion time, or an earlier point in time, on the same server or the same managed instance. You can accomplish this through the Azure portal, PowerShell, or the REST (createMode=Restore). You restore a deleted database by creating a new database from the backup.
If you delete a server or managed instance, all its databases are also deleted and can't be recovered. You can't restore a deleted server or managed instance.
Deleted database restore by using the Azure portal
You restore deleted databases from the Azure portal from the server or managed instance resource.
It may take several minutes for recently deleted databases to appear on the Deleted databases page in Azure portal, or when displaying deleted databases programmatically.
To recover a deleted database to the deletion time by using the Azure portal, open the server overview page, and select Deleted databases. Select a deleted database that you want to restore, and type the name for the new database that will be created with data restored from the backup.
SQL Managed Instance
To recover a managed database by using the Azure portal, open the managed instance overview page, and select Deleted databases. Select a deleted database that you want to restore, and type the name for the new database that will be created with data restored from the backup.
Deleted database restore by using PowerShell
Use the following sample scripts to restore a deleted database for either SQL Database or SQL Managed Instance by using PowerShell.
For a sample PowerShell script showing how to restore a deleted database in Azure SQL Database, see Restore a database using PowerShell.
SQL Managed Instance
For a sample PowerShell script showing how to restore a deleted instance database, see Restore deleted instance database using PowerShell
To programmatically restore a deleted database, see Programmatically performing recovery using automated backups.
Geo-restore is available only for SQL databases or managed instances configured with geo-redundant backup storage.
You can restore a database on any SQL Database server or an instance database on any managed instance in any Azure region from the most recent geo-replicated backups. Geo-restore uses a geo-replicated backup as its source. You can request geo-restore even if the database or datacenter is inaccessible due to an outage.
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 is a delay between when a backup is taken and when it is 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 by using the Azure portal
From the Azure portal, you create a new single or managed instance 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:
From Dashboard, select Add > Create SQL Database. On the Basics tab, enter the required information.
Select Additional settings.
For Use existing data, select Backup.
For Backup, select a backup from the list of available geo-restore backups.
Complete the process of creating a new database from the backup. When you create a database in Azure SQL Database, it contains the restored geo-restore backup.
SQL Managed Instance
To geo-restore a managed instance database from the Azure portal to an existing managed instance in a region of your choice, select a managed instance on which you want a database to be restored. Follow these steps:
Select New database.
Type a desired database name.
Under Use existing data, select Backup.
Select a backup from the list of available geo-restore backups.
Complete the process of creating a new database. When you create the instance database, it contains the restored geo-restore backup.
Geo-restore by using PowerShell
For a PowerShell script that shows how to perform geo-restore for a single database, see Use PowerShell to restore a single database to an earlier point in time.
SQL Managed Instance
For a PowerShell script that shows how to perform geo-restore for a managed instance database, see Use PowerShell to restore a managed instance database to another geo-region.
You can't perform a point-in-time restore on a geo-secondary database. You can do so only on a primary database. For detailed information about using geo-restore to recover from an outage, see Recover from an outage.
Geo-restore is the most basic disaster-recovery solution available in SQL Database and SQL Managed Instance. It relies on automatically created geo-replicated backups with a recovery point objective (RPO) up to 1 hour and an estimated recovery time 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 is not 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 Auto-failover groups. It offers a much lower RPO and recovery time objective, and the capacity is always guaranteed.
For more information about business continuity choices, see Overview of business continuity.
Programmatic recovery using automated backups
You can also use Azure PowerShell or the REST API for recovery. The following tables describe the set of commands available.
This article uses the Azure Az PowerShell module, which is the recommended PowerShell module for interacting with Azure. To get started with the Az PowerShell module, see Install Azure PowerShell. To learn how to migrate to the Az PowerShell module, see Migrate Azure PowerShell from AzureRM to Az.
The PowerShell Azure Resource Manager module is still supported by SQL Database and SQL Managed Instance, but all future development is for the Az.Sql module. For these cmdlets, see AzureRM.Sql. Arguments for the commands in the Az module and in Azure Resource Manager modules are to a great extent identical.
Restore points represent a period between the earliest restore point and the latest log backup point. Information on latest restore point is currently unavailable on Azure PowerShell.
To restore a standalone or pooled database, see Restore-AzSqlDatabase.
|Get-AzSqlDatabase||Gets one or more databases.|
|Get-AzSqlDeletedDatabaseBackup||Gets a deleted database that you can restore.|
|Get-AzSqlDatabaseGeoBackup||Gets a geo-redundant backup of a database.|
|Restore-AzSqlDatabase||Restores a database.|
For a sample PowerShell script that shows how to perform a point-in-time restore of a database, see Restore a database by using PowerShell.
SQL Managed Instance
To restore a managed instance database, see Restore-AzSqlInstanceDatabase.
|Get-AzSqlInstance||Gets one or more managed instances.|
|Get-AzSqlInstanceDatabase||Gets an instance database.|
|Restore-AzSqlInstanceDatabase||Restores an instance database.|
To restore a database by using the REST API:
|REST (createMode=Recovery)||Restores a database.|
|Get Create or Update Database Status||Returns the status during a restore operation.|
To restore a database by using the Azure CLI, see az sql db restore.
SQL Managed Instance
To restore a managed instance database by using the Azure CLI, see az sql midb restore.
Automatic backups 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.