Question :
I know Azure databases are backed up and kept for a retention period, which I believe the default is 30 days. I’d like to come up with a plan to mimic our current strategy with a physical SQL Server box. I’m also open to other strategies, but the main goal is if a customer says “I think my project was good on March 3, 2016” I can find the closest possible database quickly if their query was within the past year, and also find a close backup, but less quickly (and more cost effective).
Our largest database is around 4GB and most others are very tiny. It is quite possible that the “hot” storage, which is quickly accessible might be so cost effective that I need not deal with “cold” storage such as Amazon Glacier.
Right now we have backups that occur every 15 minutes. But we also do full backups:
- Every day
- The first day of every week
- The first day of every month
I mostly use these .BAK file backups for user error data recovery or I download the file to run on a local instance for development purposes, mostly debugging or feature development. I probably could also just restore the backup to a temporary database on Azure and use the connectionstring from a development box, but I would like to make sure I can get a .BAK file as well.
I keep the daily, weekly, and monthly backups on a backup drive attached to the Sql Server so I can quickly grab any backup in the prior 30 days, any weekly backup for the prior 3 months, and every monthly backup for the past year.
I also take a monthly backup and upload that to Amazon glacier so I can retain those forever (maybe we will start dropping them after 5 years)
Now I believe (correct me if I’m wrong) that with Azure, if I use 30 day retention I can restore a backup from almost any point within the 30 day window.
I’m looking for a way to replicate this entire strategy on Azure. Are there built in Azure services for this, or would I need to create a VM and have scheduled scripts? What about third party products such as Cloudberry that would make managing backups simple? This includes seeing and retrieving both my “hot” and “cold” backups.
Answer :
There are a number of ways to schedule jobs for Azure SQL DB such as elastic jobs or azure automation. They can be setup to export your database at the intervals you require so it provide similar coverage as backups. Not the same experience and will be pretty klunky if your databases grow to massive sizes but in the few to tens of GB, you won’t miss the super powers of local SQL Server backup (e.g. compression).
The team is trying to provide a solution for the long term retention requirement for backups so if it’s important to you, add the request to https://feedback.azure.com/forums/217321-sql-database. I don’t know what state that investigation is in but always helpful to get users chiming in with requirements so be very explicit on how long you really need to keep the backups around.