Export (0) Print
Expand All

Azure SQL Database Backup and Restore

Updated: June 12, 2014

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

Azure SQL Database service has built-in backups to support Self-Service point in time restore. Point in time restore is enabled for Basic, Standard, and Premium service tiers. Each service tier has a different backup retention period described later in this topic.

If you have Web or Business databases, you must create your own backup strategy. You can use database copy and Import and Export services to create copies of the data and export the file to an 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 service tiers will be retired in 12 months from April 24, 2014. For more information, see Web and Business Edition Sunset FAQ.

Built-in Automatic Backup in SQL Azure Database

Windows 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. In addition, premium database offers the ability to create secondary databases in different regions to protect against datacenter disasters.

Point in Time Restore for Azure SQL Database

Point in Time Restore is designed to recover a database after human errors or accidental programmatic data modification. It allows you to create a new database to a specific point in time that you specify. The new database is created on the same logical server as the source database. The restored database will have the service tier that the source database had at the specified point in time and will be billed accordingly. However, the performance level of the restored database will be set to the lowest level for the service tier. 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.

 

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

Web

Not supported

n/a

n/a

Business

Not supported

n/a

n/a

Basic

Supported

7 days

Standard

Supported

14 days

Premium

Supported

35 days

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

You can monitor the restore operation using sys.dm.operation_status.

Restoring an Active Database

When restoring an active database, a new database is created on the same logical server. It is created using the same service tier the original database was set to at the specified point in time, and the lowest performance level for that service 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. 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

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.Similar to the case with active database, the new database is created on the same logical server, with the same service tier as the deleted database had at the specified point in time and the lowest performance level for that service tier.

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. Also, The backups of the database are not directly accessible or need to move to different environments. Point in time today only supports restoring a copy to the same server.

For the above scenarios, as a workaround, you can follow the steps described for CASE B in the Copying Databases in Azure SQL Database topic.

Protection against Data Loss – Beyond Backup and Recovery

Protection against data loss requires other strategies and tools beyond backup and recovery. These strategies and options are explained in 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 in the restore command or Portal. This may be different than the current service tier of the original database if your database has been upgraded or downgraded since then. Following examples helps illustrate this further:

  • 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: No. Cross server restores are not currently supported.

Q: Can I submit restore requests programmatically?

A: Yes. You can submit a restore request using PowerShell or REST API.

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

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

Show:
© 2014 Microsoft