Export (0) Print
Expand All

Azure SQL Database Backup and Restore

Updated: September 10, 2014

Microsoft Azure SQL Database has built-in backups to support self-service Point in Time Restore and Geo-Restore for Basic, Standard, and Premium service tiers.

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: Use the Import and Export Service in Azure SQL Database.

ImportantImportant
Web and Business Editions will be retired September 2015. For more information, see Web and Business Edition Sunset FAQ.

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.

Backup Storage

Backup storage is the storage associated with your automated database backups that are used for Point in Time Restore and Geo-Restore. Azure SQL Database provides up to 200% of your maximum provisioned database storage of backup storage at no additional cost. For example, if you have a database in the Standard service tier with a provisioned size of 250 GB, you will be provided with 500 GB of backup storage at no additional charge. If your database exceeds the provided backup storage, you can choose to reduce the retention period by contacting Azure Support or pay for the extra backup storage billed at standard Read-Access Geographically Redundant Storage (RA-GRS) rate. For more information on RA-GRS billing, see Storage Pricing Details.

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. Make sure you have available quota on the logical server to create another database. If you would like to request an increased quota, please contact Azure 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.

 

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

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.

You can restore a database that was deleted during its retention period to the point at which it was deleted. The retention period is determined by the service tier of the database while it existed or the number of days where the database exists, whichever is less.

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.

For a complete walkthrough, see Submit a Database Restore Request. You can automate this operation using the PowerShell cmdlet <name> or REST API <operation name>.

For more information, see Managing Azure SQL Databases with PowerShell and Managing Azure SQL Databases with REST API.

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 archiving the database using alternative methods such as automated export.

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.

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

Show:
© 2014 Microsoft