In this post i want to cover the use of Azure Lifecycle Management Policies and the purging of sql server backup blobs in storage account containers. Anyone now using BackupToURL for their sql server instances will likely have an Azure storage account steadily accruing a large number of backup files.

I will cover BackupToURL in another post, but for now, i want to focus on the management of the accrued backup files. If, when you setup your backup strategy, you did nothing else other than configure the instance and create the jobs, then files will be accruing at a fair rate. The option to delete files in some backup scripts does not reliably manage this for you either, have you checked your storage account usage recently to see just how much data is stored there?

This is where the Lifecycle Policy option comes into play. You can find this feature on the Azure Storage Account blade, as shown below:

The typical view for a storage account with containers is shown below:

Within the container i have folders for each logical backup type and these are easily configurable using everyones favourite backup scripts. The folders are symbolic links and not real folders, deleting all files from the folder will make the folder disappear from the container. The structure i have is shown below:

I have separate folders created for each backup type of FULL, DIFF and LOG. Looking at the FULL backup folder i have a couple of test files as shown below:

The policy used to purge these folders is detailed below. You can create the policy either by list view or code view.

{
  "rules": [
    {
      "enabled": true,
      "name": "rm-diff-log-sqlpw01",
      "type": "Lifecycle",
      "definition": {
        "actions": {
          "baseBlob": {
            "delete": {
              "daysAfterModificationGreaterThan": 2
            }
          }
        },
        "filters": {
          "blobTypes": [
            "blockBlob"
          ],
          "prefixMatch": [
            "mycontainer01/LOG/LOG",
            "mycontainer01/DIFF/DIFF"
          ]
        }
      }
    },
    {
      "enabled": true,
      "name": "rm-full-sqlpw01",
      "type": "Lifecycle",
      "definition": {
        "actions": {
          "baseBlob": {
            "delete": {
              "daysAfterModificationGreaterThan": 4
            }
          }
        },
        "filters": {
          "blobTypes": [
            "blockBlob"
          ],
          "prefixMatch": [
            "mycontainer01/FULL/FULL"
          ]
        }
      }
    }
  ]
}

The policy performs a pre fix match looking for files to action. The best way i found to ensure the policy purged the files was to prefix each backup file name with the backup type rather than other naming conventions where the backup type may be a middle part of the string. This is easily configurable in any backup script, a typical naming convention i use is: 

backuptype_servername_dbname_yyyymmdd_hhmmss.bak

Since you will always usually retain the full backups for longer than the PIT backups i have a longer retention for Full backups on the lifecycle policies. Typically, full backups may be retained for 6 or even 12 months or more, PIT recovery would not usually be required after 35 days or so, this is the logic applied for Azure SQL Database PITR periods. You can also move older backup files to cooler storage tiers to make further cost savings while still retaining the data for longer.
For your environment you should test to find the best solution that fits, offloading database backups to the cloud offers high redundancy and flexibility. For backups stored in a locally redundant storage account in the UK South region, 3 copies of the data are kept, 1 in each availability zone.There will be some work required to ensure that your environment and network configuration supports BackupToURL, more can be found on this topic at the following Microsoft links:  

SQL Server BackupToURL 

Troubleshooting BackupToURL