Backups should be one of those things top on the list of checks. If something went "pop" and you needed to restore from backup, how confident are you that your backup will restore?  

No doubt, there are many solutions to check your backups, the simplest of all being a manual one. That said, I would like to share how we do it and maybe give someone some ideas for checking your backups or from a learning point of view have someone pull our method apart 🙂

We currently take full backups on a daily basis and log backups every 30 / 60 minutes. There is no point re-inventing the wheel when it comes to backups so we use the excellent Ola Hallengren scripts https://ola.hallengren.com/sql-server-backup.html. 

Generally we keep 5 days of log and full backups on the server itself. As part of that backup job we Sync the files to an Azure blob storage account using a PowerShell script. Below is an image of the basic backup flow.

Once you have your backups in Azure, how do you manage them? Azure automation runbooks are an ideal way to automate the process of managing files in a storage account.

Log files are kept for 30 days. Full backups are kept for 30 days after which we keep the weekly backup for 2 years and after that the monthly backup with the monthly backups finally being removed after 10 years. With the Archive Tier for Blob Storage you can drive down your Azure storage costs. Below is a simple file retention flow.

We have created a Windows VM with SQL Server 2016 standard with a rather substantial local SSD to hurry things along (L4S - 4vCPUs, 32GB and 678 Local SSD). Again, using Azure Automation we schedule that VM to start up every day. It has a SQL Agent job which starts when the server comes up.

This job will copy the latest full backups from the Azure storage account, restores them, checks them and reports on those checks. Once the restore job has completed the VM is automatically shut down and deallocated to save on costs. 

An additional Azure Automation Runbook finally lists the backups we currently hold which enables us to report on the type of backups and which date they relate to.

In summary, we are confident our full backups are held off site and those backups are restorable.

Yes, there are costs involved in doing this. The Azure storage costs and the cost of the VM doing the checks. But, that cost is worth it for peace of mind. Ok, it's probably not a full-blown check in the sense we do not currently attempt to restore the full backup and any subsequent log files but we know our full backups are restorable, we know if something happened to the underlying disks on our data centre VM we have known working backups.

We have gradually built more and more into Azure Automation and heavily use Azure storage accounts for additional backup. For example we backup SSIS packages, scripts, and config files to Azure on a weekly basis and keep 4 weeks worth of copies.

I guess the question is how confident are you that your backups are restorable? Do you check them? If something went pop and your backups were bad what would be the impact to you, your business and your customers?