Recover an Azure SQL database using automated database backups

SQL Database provides these options for database recovery using automated database backups and backups in long-term retention. You can restore from a database backup to:

  • A new database on the same logical server recovered to a specified point in time within the retention period.
  • A database on the same logical server recovered to the deletion time for a deleted database.
  • A new database on any logical server in any region recovered to the point of the most recent daily backups in geo-replicated blob storage (RA-GRS).

You can also use automated database backups to create a database copy on any logical server in any region.

Recovery time

The recovery time to restore a database using automated database backups is impacted by several factors:

  • The size of the database
  • The performance level 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 very large, and/or active database, the restore may take several hours. If there is prolonged outage in a region, it is possible that there are large numbers of Geo-Restore requests being processed by other regions. When there are many requests, the recovery time may increase for databases in that region. Most database restores complete within 12 hours.

    There is no built-in functionality to do bulk restore. The Azure SQL Database: Full Server Recovery script is an example of one way of accomplishing this task.

Important

To recover using automated backups, you must be a member of the SQL Server Contributor role in the subscription or be the subscription owner. You can recover using the Azure portal, PowerShell, or the REST API. You cannot use Transact-SQL.

Point-In-Time Restore

You can restore an existing database to an earlier point in time as a new database on the same logical server using the Azure portal, PowerShell, or the REST API.

Important

You cannot overwrite the existing database during restore.

The database can be restored to any service tier or performance level, and as a single database or into an elastic pool. Ensure you have sufficient resources on the logical server or in the elastic pool to which you are restoring the database. Once complete, the restored database is a normal, fully accessible, online database. The restored database is charged at normal rates based on its service tier and performance level. You do not incur charges until the database restore is complete.

You generally restore a database to an earlier point for recovery purposes. When doing so, you can treat the restored database as a replacement for the original database or use it to retrieve data from and then update the original database.

  • Database replacement: If the restored database is intended as a replacement for the original database, you should verify the performance level and/or service tier are appropriate and scale the database if necessary. You can rename the original database and then give the restored database the original name 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 execute the necessary data recovery scripts to extract data from the restored database 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 is canceled and you are not charged for the database that did not complete the restore.

Azure portal

To recover to a point in time using the Azure portal, open the page for your database and click Restore on the toolbar.

point-in-time-restore

Deleted database restore

You can restore a deleted database to the deletion time for a deleted database on the same logical server using the Azure portal, PowerShell, or the REST (createMode=Restore).

Important

If you delete an Azure SQL Database server instance, all its databases are also deleted and cannot be recovered. There is currently no support for restoring a deleted server.

Azure portal

To recover a deleted database during its retention period using the Azure portal, open the page for your server and in the Operations area, click Deleted databases.

deleted-database-restore-1

deleted-database-restore-2

Geo-Restore

You can restore a SQL database on any server in any Azure region from the most recent geo-replicated full and differential backups. Geo-Restore uses a geo-redundant backup as its source and can be used to recover a database 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 region where the database is hosted. If a large-scale incident in a region results in unavailability of your database application, you can restore a database from the geo-replicated backups to a server in any other region. There is a delay between when a differential backup is taken and when it is geo-replicated to an Azure blob in a different region. This delay can be up to an hour, so, if a disaster occurs, there can be up to one hour data loss. The following illustration shows restore of the database from the last available backup in another region.

geo-restore

For detailed information about using Geo-Restore to recover from an outage, see Recover from an outage

Important

Recovery from backups is the most basic of the disaster recovery solutions available in SQL Database with the longest RPO and Estimate Recovery Time (ERT). For Basic databases with maximum size of 2 GB Geo-Restore, provides a reasonable DR solution with an ERT of 12 hours. For larger Standard or Premium databases, if shorter recovery times are desired, or to reduce the likelihood of data loss you should consider using Active Geo-Replication. Active Geo-Replication offers a much lower RPO and ERT as it only requires you initiate a failover to a continuously replicated secondary. For details, see Active Geo-Replication.

Azure portal

To geo-restore a database during its retention period using the Azure portal, open the SQL Databases page and then click Add. In the Select source text box, select Backup. Specify the backup from which to perform the recovery in the region and on the server of your choice.

Programmatically performing recovery using automated backups

As previously discussed, in addition to the Azure portal, database recovery can be performed programmatically using Azure PowerShell or the REST API. The following tables describe the set of commands available.

PowerShell

Cmdlet Description
Get-AzureRmSqlDatabase Gets one or more databases.
Get-AzureRMSqlDeletedDatabaseBackup Gets a deleted database that you can restore.
Get-AzureRmSqlDatabaseGeoBackup Gets a geo-redundant backup of a database.
Restore-AzureRmSqlDatabase Restores a SQL database.

REST API

API Description
REST (createMode=Recovery) Restores a database
Get Create or Update Database Status Returns the status during a restore operation

Summary

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 performance levels.

Next steps