SQL SERVER – PowerShell Script – Remove Old SQL Database Backup Files From Azure Storage

Earlier back, I wrote blog to help my blog reader SQL SERVER – Powershell Script – Remove Old SQL Database Backup Files from Azure Storage

As they say, there’s more than one way to skin a cat. One of my blog reader shared another script which can do the same thing.

Solution: Remove Old Backup Files From Azure Storage

$context = New-AzureStorageContext -StorageAccountName "StorageAccountNameGoesHere" -StorageAccountKey "StorageKeyGoesHere"

# In the next line change .bak to the file extension of the file to be deleted

$blobs= Get-AzureStorageBlob -Container "ContainerNameGoesHere" -blob *.bak -Context $context 
foreach ($blob in $blobs)
{
$modifieddate = $blob.LastModified
Write-Host $modifieddate

if ($modifieddate -ne $null) 
    {
        # in below clause we need to put number of days for retention. I have put 10 days.

        $howold = ([DateTime]::Now - [DateTime]$modifieddate.LocalDateTime) 
        if ($howold.TotalDays -ge 10)            {
                Remove-AzureStorageBlob -Blob $blob.Name -Container "ContainerNameGoesHere" -Context $context
                Write-Host $blob.Name
            }
    }
}  

As I wrote in an earlier blog, you need to fill the parameters based on your configuration before you delete any backup files from Azure Storage.

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 older than 10 days. 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 powershell script which can do the same? Please share via comments to help others.

Updating This Script for Today

This script uses the older Azure PowerShell cmdlets. In the current Az module, the same commands are New-AzStorageContext, Get-AzStorageBlob and Remove-AzStorageBlob, and the logic stays the same. Before you delete anything, I suggest a dry run. Comment out the Remove line, run the script, and read the list of dates it prints. Once the list looks right, turn the delete back on.

There is also a way to do this with no script at all. Azure Storage has lifecycle management rules that delete blobs a set number of days after they were last modified. For many teams, that is the simplest way to clean up old backups, and it runs on its own.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.