We have a production SQL Server with a number of critical bases. At this time, we have a maintenance plan which performs the following:
- creates a FULL backup every Saturday with a TTL of 4 weeks
- creates a DIFF backup every night (except Saturday) with a TTL of 2 weeks
- creates a LOG backup every hour with a TTL of 1 week
As such, we're able to restore the databases to any Saturday of the past 4 weeks, any day (night) of the past 2 weeks and almost any specific point in time for the past week. Also, we're using the backup scripts by Ola Hallengren, and the databases are for our SharePoint and TFS.
We recognize that 4 weeks is not that long AND those backups are, ultimately, local backups, so if something serious happens to the physical server they are on, we'll be in trouble.
I wanted to utilize Azure Blobs to store online backups (for FULL and DIFF as LOG can remain local), however I have a bit of a problem coming up with a good plan on how to do that. The main problem is that our internet connection isn't very fast (we only have a symmetrical 30/30 at the office) and, well, Azure costs money (Blobs are cheap, but still). But make no mistake, network speed is the primary problem.
I wanted to use a different backup plan for Azure than for local backups, but quickly ran into a problem – DIFF backups become useless if the FULL backups aren't the same in both backup locations and I can't have the same FULL backups in both locations if I want a different schedule for Blobs.
To put that into a more practical example: say I want to save a FULL backup into Azure on the first weekend of every month… if my local backup was the same, that wouldn't be a problem. But now, if I do copy-only FULL backup to Azure, then daily DIFF backups stored in Azure won't work. If I do a non-copy-only FULL backup to Azure, then the LOCAL backups get broken. Besides, the first FULL local backup after the first Azure backup will break the subsequent DIFF backups on Azure anyway.
I think the only thing I can do is take FULL backups less often (say, 1 FULL per month?), increase the TTL accordingly, and then have the same backup plan for Azure (using the MIRROR options in the script). But then I worry that this is not often enough for FULL backups. Alternatively, the script by Ola has a ModificationLevel parameter, but I'm not sure how to use it for optimal storage space utilization.
Alternatively I could utilize a file-sync-to-Azure solution (rather than an SQL-backup-to-Azure solution), but this is really the same problem.
Is there a way to have a different backup plan for Azure than for local SQL backups?
You can use above script while creating the T-SQL job and schedule it accordingly.
Best Answer
As @LowlyDBA pointed out "having a separate job to upload the files to Azure BLOB" is the right approach here. You simply can't have two separate jobs managing differential or log backups. And you don't really want to have any BACKUP command that runs for a really long time.
Just have a single local backup plan, and a secondary job that uses AZCopy to push some or all of the resulting backup files into Azure Blob storage.
Here's an old blog post on this topic Copying SQL Server Backups to Windows Azure Storage using AzCopy. You can also try the new Azure File Sync service to automatically move files to Azure.