Long Term Backup with Azure SQL Database

In a previous blog "Extended Backup Retention With Azure SQL Database", I outlined a method for extending the boundaries of the Azure SQL Database service. To refresh your memory the defaults here are 7 days for the Basic tier and 35 days for the Standard and Premium tier of Azure SQL Databases. While the method I outlined in this earlier blog post had some limits and required some automation via PowerShell, it was really the only method we had to overcome this obstacle. I'm pleased to report that the SQL Azure team heard the demand for configurable backup loud and clear, and has provided a feature to extend these limits to 10 years!

For the official announcement you can take a look at the Azure Blog, but for the sake of completeness I thought I would give a step by step guide to elaborate on a few more details that aren't spelled out in the official posting.

  1. While we logically think about database policy at a "database" level, you actually control this feature at a "server" level. So avoid your instinct to navigate to the SQL Azure Database and go to the server instead. You'll find the tab to enable this capability under "Settings".
  2. Since this feature is in preview, you must accept the preview terms before it can be used.
  3. As we earlier mentioned this policy applies at the server level; therefore, the next step is to select all the databases on the server you want it to apply to.
  4. Select the Recovery Vault you'd like to use.
  5. Finally define your retention policy and you're ready to go.

A couple things to call out that were a bit of a surprise to me:

  1. Presently this long term retention imposes the restrictions that the vault must be in the same subscription, region, and resource group. If you don't have this you'll see a warning as follows:
  2. While this give you a far longer duration of retention than previously available, there is not an ability to do any fine grained policies (ie. keep daily backups for 1 year, and weekly backups for 3 years, etc.)