SQL SERVER – Powershell Script – Remove Old SQL Database Backup Files from Azure Storage

Few days back I wrote blog to help my blog reader and this is follow up on the same post How to Add Date to Database Backup Filename? – Interview Question of the Week #109. In this blog post we will see a powershell script for Removing Old SQL Database Backup Files from Azure Storage.

So, I added SQL Agent job in my SQL Server and forgot it. Later I realized that I am consuming space on my Azure storage and I need to clean up my old backups. If they were on disk, I would have created maintenance plan to clean them up, but since they are in Azure storage now, I need to write a PowerShell script to clean them up.

SOLUTION/WORKAROUND

Here is what I came up with.

$HowOldFile = [DateTime]::UtcNow.AddHours(-72)
$StorageAccountName = "<StorageAccountName>"
$StorageAccountKey = "StorageAccountKey>"
$Containername = "<ContainerName>"
$extension = "*.bak"
$Storagecontext = New-AzureStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageAccountKey
Get-AzureStorageBlob -Container $Containername -Context $Storagecontext | Where-Object { $_.LastModified.UtcDateTime -lt $HowOldFile -and $_.BlobType -eq 'PageBlob' -and $_.Name -like $extension} | Remove-AzureStorageBlob

You need to fill the parameters based on your configuration.

Notes

1. To run above, you need to have Azure PowerShell cmdlets installed.
2. It can be run from anywhere, not necessarily SQL Server machine because it has nothing to do with SQL Server.
3. In the above script, I am checking the LastModified date for each blob with extension with *. back and deleting those which are 72 hours older. You need to change the extension and time.
4. When you copy paste from a blog, it might not parse correctly, so take extra care for special characters.

Do you have any better script which can do the same? Please share via comments to help others.

If due to any reason, this script does not work. Please visit this blog post as I have posted another script here: SQL SERVER – PowerShell Script – Remove Old SQL Database Backup Files From Azure Storage.

Reference: Pinal Dave (https://blog.sqlauthority.com)