Remove SQL Backup Files from your Azure Storage

Microsoft introduced a lot of new Azure features in SQL Server 2014, which has been released this week. It has become really easy to store your backup files on Windows Azure (or should I say Microsoft Azure J?).
You can check my previous blog post on how you can take a backup to Windows Azure. This all works really great and yesterday I also introduced this new feature to one of my customers and they wanted to go ahead with it! While I was driving home, I was thinking how I could implement this in a good way. My customer didn’t want to use Smart Backup, because they wanted to be in control of the backup process. Scheduling the backups with the SQL Server Agent looks like the appropriate solution here, just like you take backups to disk, but you choose to URL.

When you take backups to disk, you also have to schedule a cleanup job for the old backup files. The same counts for the Azure storage, unless you want to pay a lot of money for your storage… J
But how can I clean up the backups from my Azure Storage container? I decided to use PowerShell to clean up the backup files.

First of all you have to install the Azure PowerShell cmdlets. More information can be found here. Once the cmdlets are installed, don’t forget to connect to your subscription as mentioned. Execute the Get-AzureSubscription Command in the Windows PowerShell ISE.

Login to your Azure portal and copy the access key of your storage account because you will need it in the script where it says “Paste your storage access key here“.

In my script, I’m using the Get-AzureStorageBlob to get the blobs out of my backup container. I’m going to check the LastModified date for each blob with extension *.bak and *.log. If it’s less than my cleanup time (3 days for full backups and 1 day for transaction log backups) I’m removing the backup file with the Remove-AzureStorageBlob cmdlet.

$CleanupTime = [DateTime]::UtcNow.AddHours(-72)
$context = New-AzureStorageContext -StorageAccountName koherastorage -StorageAccountKey {Paste your storage access key here}
Get-AzureStorageBlob -Container "sqlbackup" -Context $context | 
Where-Object { $_.LastModified.UtcDateTime -lt $CleanupTime -and $_.BlobType -eq "PageBlob" -and $_.Name -like "*.bak"} |
Remove-AzureStorageBlob
$CleanupTime = [DateTime]::UtcNow.AddHours(-24)
Get-AzureStorageBlob -Container "sqlbackup" -Context $context | 
Where-Object { $_.LastModified.UtcDateTime -lt $CleanupTime -and $_.BlobType -eq "PageBlob" -and $_.Name -like "*.log"} |
Remove-AzureStorageBlob

 You can easily schedule it this with a SQL Server Agent Job. Just choose Powershell in the job step.

 I know this script can be fine-tuned and any suggestion is appreciated.

 Pieter

22 comments on “Remove SQL Backup Files from your Azure Storage
  1. Hi.
    Thank you for your decision.
    I have problem with PowerShell’s script.
    In usual Windows PowerShell console script works.
    In sql server’s power shell job – ‘The term ‘New-AzureStorageContext’ is not recognized as the name of a cmdlet.

  2. Hi Pieter,
    I’m trying to setup a SQL Agent job on a SQL Server in Azure I’ve created to delete the blob storage as you specified.

    How do I install the Azure Powershell cmdlets on the Azure VM itself so I can fix the ‘The term ‘New-AzureStorageContext’ is not recognized as the name of a cmdlet’ error?

    When I open IE on that Azure VM, and go to the URL you specify above, it says I cannot download the file due to security restrictions?

  3. Pingback: Remove SQL Backup Files from Azure Storage | My Clipboard - Marco Tavolini

  4. Hi Pieter,

    Thx for the script.
    It works fine after i’ve removed the filter ‘-and $_.Properties.LeaseStatus -eq “Unlocked”‘ for the .bak files.
    Is there a reason why you’ve included this filter for the Bak-files and not for the log-files ?

    • Hi Franky,

      Where did you see that filter? Because in the script on the post I’m not using that filter.
      The extra check could be useful when there is still a lease on the file. This means that the file is still “locked” and cannot be removed.

      Regards
      Pieter

  5. Hello Pieter,

    This PS script works fine, but when scheduled under Task Scheduler its not working, please help.

    Thank you, Anand

  6. Hello Pieter,

    Never mind, it was due to permission issue under Task Scheduler. Thank you for the wonderful script. Anand

  7. HI Pieter,

    Brilliant script thank you ! A massive oversight from Microsoft there I think to miss this out ! I would recommend this script to get people started then possibly look at Azure Automation to detach it from any VM.

  8. Hi Pieter

    Thank you for the script I have a quick question I am new to Powershell and Azure I like to incorporate this script but I need the following:

    powershell script to purge old versions in storage to keep at east the last n versions but otherwise purge anything older than n text_ days. (Example at least 30 versions, and older than 120 days)

    For example
    •If there are 8 versions with the oldest being 180 days old, keep all 8.
    •If there are 100 versions, remove anything older than 120 days.

    Thank you for your help on this it is highly appreciated

    Joe

    • Hi Joe,

      That would be indeed a nice addition to the script.
      I will try to create it when I have some more time.
      Don’t hesitate to try it yourself, might be a good exercise to enhance your PowerShell skills

      Regards
      Pieter

  9. You are a life-saver Pieter – thank you so much for this. Unbelievable that this is still not a feature of the portal, 3 years later …

  10. Hi Pieter,

    Great script you really save my day. We are looking for a solution and this one is simple and realliable.

    Thank you so much for your help.

Leave a Reply

Your email address will not be published. Required fields are marked *