Let's play with Azure SQL Database backup and the Point in Time Restore

Labels: , , , , , ,
Did you know that you can have: a full database backup once a week, a differential database backups once a day, and a transaction log backups every 5 minutes of your Azure SQL Database? Did you know that all this is done automatically when you are using the new Azure SQL Basic, Standard or Premier service tiers? Even more, you will have access to the Point in Time Restore self-service. In this post, I will show how to "configure" the database to get the automatic backups, and how to do a restore.

Setup the automatic backup


If you are like me, your Azure SQL Databases are set to Web or Business edition.  The first thing to do will be to change that. You will need to do it anyway since the Web and Business service tiers will be retired in September 2015[1].  That the only required since backup service are built-in the Basic, Standard and Premium tiers.
For the demo purpose, I will use the Basic tier. To change the tier of the database, go on the Azure Portal. In the left panel click on the SQL Databases et select the database that you want to update (ie: FrankDemo). Once the right section is updated, select the Scale tab and change the Service Tiers for: Basic.

Tiers_Basic

Now, if you return in the Dashboard tab, a new option will be available.

Restore_button

Your database now has built-in backups to support self-service Point in Time Restore and Geo-Restore. Azure SQL Database automatically creates backups using the following schedule:
  • Full database backup once a week
  • differential database backups once a day
  • transaction log backups every 5 minutes.
The full and differential backups are replicated across regions.

The retention period will vary between 7 and 35 days base on the selected tiers.[2]

Restore an Azure SQL Database


Restoring a database is really easy. Remember that new button at the bottom of the screen, it's now time to click on it.


Restore_setings

This will bring the settings options. It's now time to type the name of your restored database. Note that you must use a different name than the original.  It's always a good practice to double-check that you are pointing on the good database on the correct server. Pick a restore point using the slider or by filling the date and time fields. When you are done click the button. The portal will let you know that the restore is successfully completed by a notification.


Restore_done

I hope that this post shows you how easy it is to use the backup/restore with Azure SQL Database. Thank you for ready, Any comments, suggestions and/or questions are welcome.



[1]: Web and Business service tiers will be retired in September 2015, more
details on the Windows Azure site.
[2]: Detail about the retention period on Azure SQL Database Backup and Restore



~ Frank Boucher