Automating daily copy of SQL Backups to Immutable blob storage

Marc 11 Reputation points
2022-11-03T17:14:07.477+00:00

More of a "how would you go about this..." type of question...

My scenario: I need to copy my nightly backups of my SQL Server databases (backed up to local disks by SQL itself) to Azure blob storage for "off site storage" of copies of the backups to meet some regulatory requirements. I have powershell scripts set up to copy these various BAK files via azCopy to the storage which works ok.

Now I want to implement immutable policies on my containers. As each night's backup is simply meant to overwrite the previous nights, I have no need for long term (more than 24 hours) retention. Ideally, each evening, say at 6:00pm, sql server backs up the database to local disk. then say at 7:00pm, my powershell job kicks off (by windows scheduled task) to copy that backup to Azure. Here's the catch: if I enable an immutable policy, the minimum time is 24 hours. So the BAK file writes at 7:00pm (beginning time; of course it takes some short amount of time to complete the copy depending on size). The immutability kicks in at that 7:00pm time. So the next night, when the whole process repeats, the azCopy job will fail because the immutability is in play as the exact 24 hours has passed. I'd need to kick the azcopy job off at say 7:30pm to successfully get this file kicked off. But then the next night I'd have to run that job at say 8:00pm. And on and on... Now if the immutability policy allowed for 23 hours and 59 minutes, I don't think I'd be in this conondrum.

So my question is this.... Is there a better way to do this? I'd like to avoid writing a complex powershell script that checks for the time, and if not enough time (ie > 24 hours) has passed then wait some extra time)... I guess I could have 2 blob containers and alternate writing to each one (container A on the first evening, container B on the second, then back to A on the third, and so on)... but that seems convoluted too.

Maybe I'm over thinking this, hence wanted to get this community's perspective...

thanks!

Azure SQL Database
Azure Blob Storage
Azure Blob Storage
An Azure service that stores unstructured data in the cloud as blobs.
3,192 questions
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.