Export (0) Print
Expand All

Azure SQL Database Backup and Restore

Updated: July 31, 2014

[The feature described in this topic is available only in preview. This topic is pre-release documentation and is subject to change.]

Microsoft Azure SQL Database service has built-in backups to support self-service Point in Time Restore and Geo-Restore. Point in Time Restore and Geo-Restore are enabled for Basic, Standard, and Premium service tiers. For Point in Time Restore, each service tier has a different backup retention period described later in this topic.

If you have Web or Business Edition databases, you must create your own backup strategy. You can use database copy or Import and Export services to create copies of the data and export the file to an Microsoft Azure storage account. For more information, see How to: Use Database Copy (Azure SQL Database) and How to: Import and Export a Database (Azure SQL Database).

CautionCaution
Business and web editions will be retired 12 months from April 24, 2014. For more information, see Web and Business Edition Sunset FAQ.

 

Service Tier Geo-Restore Self-Service Point in Time Restore Backup Retention Period Restore a Deleted Database

Web

Not supported

Not supported

n/a

n/a

Business

Not supported

Not supported

n/a

n/a

Basic

Supported

Supported

7 days

Standard

Supported

Supported

14 days

Premium

Supported

Supported

35 days

Built-in Automatic Backup in Azure SQL Database

Azure SQL Database automatically creates backups of every active database using the following schedule: Full database backup once a week, differential database backups once a day, and transaction log backups every 5 minutes. The full and differential backups are replicated across regions to ensure availability of the backups in the event of a disaster.

Point in Time Restore for Azure SQL Database

Point in Time Restore is designed to recover a database to a specific point in time within the backup retention period supported by the service tier of the database. Restoring creates a new database with the same service tier that was in use at the chosen restore point and the lowest performance level supported by that tier. For example, if you restore a database which was set to Premium at the specified restore point in time, the new database will also be a Premium database and the performance level is set to P1. If you are restoring a Premium database, make sure you have available quota on the logical server to create another Premium database. Premium database quota is limited to two per server by default. If you need additional quota, please call Customer Support.

The time taken to complete a restore depends on the size of the database and the length of the log chain. To get a good estimate of the restore time, you should periodically test restoring the database.

You can monitor the restore operation using sys.dm_operation_status.

Restoring an Active Database to a Point in Time

For a database that is currently active, the earliest restore point available for the database is displayed in the Quick Glance section of the Dashboard for the database on the Azure Management Portal.

For a complete walkthrough of restoring a database, see Submit a Database Restore Request.

Restoring a Deleted Database to a Point in Time

You can restore a database that was deleted during its retention period to the point at which it was deleted or an earlier point in time within the retention period. The retention period is determined by the service tier of the database while it existed.

Geo-Restore

Geo-Restore is the most basic disaster recovery option available in Azure SQL Database. It is available with Basic, Standard, and Premium service tiers. The weekly full backup and at least one daily differential backup are stored in a Geo-redundant storage to protect against region wide failures. When you submit a restore request, the database will be restored to the most recent daily backup.

There are no charges for the additional backups that are stored, but if you use Geo-Restore, you will be charged for the restored database at normal rates once the restore is complete.

Geo-Restore is currently only supported by the Azure portal and REST. PowerShell does not support this option.

For a complete walkthrough, see Submit a Database Restore Request

Archiving

Point in Time Restore enables restoring a database to a point in time in the past within the retention period for the given service tier. However, the retention period for the given service tier may not be long enough to meet business needs. In this case, consider creating an archived database using alternative methods such as automated export.

In addition, can follow the steps described for CASE B in the Copying Databases in Azure SQL Database topic.

Protection against Data Loss – Beyond Backup and Restore

For other advanced disaster recovery options, see Azure SQL Database Business Continuity.

Frequently Asked Questions

Q: When the restore operation is complete and a new database is created what service tier is the new database set to?

A: The new database is set to the same service tier as the original database at the point in time you specified. As shown in the example below, this may be different than the current service tier of the original database if your database has been upgraded or downgraded since then.

  • Database “mydb” was set to Standard tier as of May 19, 2013.

  • Database “mydb” was upgrade to Premium service tier on May 20th, 10:00AM

  • Database “mydb” is restored to a new database “mynewdb” using point in time: May 19th. 5:00PM.

  • “mynewdb” will be set to Standard service tier and S1 performance level.

Q: Can I restore to a different database server than my original database?

A: Yes. You can use the Geo-Restore feature to restore to a different target server. However you cannot specify a point in time for this restore. The database is restored to the latest differential backup available.

Q: Can I submit restore requests programmatically?

A: Yes. You can submit a Point in Time Restore request using PowerShell or REST API. Cross server geo-restores are only supported by REST.

Q: My Premium database has Active-Geo replication? Can I restore from any of the databases – primary and secondary?

A: Yes. You can submit a restore request from either a primary or a secondary database.

See Also

Community Additions

ADD
Show:
© 2014 Microsoft