Automate SQL server backup file removal/deletion from Azure blob storage
In this post, we would like to explain one of the interesting issues that we encountered while working with azure backups and restore.
Symptoms
Cannot delete the .bak files from azure blob storage through maintenance plans or any other options from SQL which have been backed up using backup to URL or managed backup.
Cause
No Such functionality available where you can delete all backup files which are less than one week/two weeks’ old from SQL.
Resolution
You can backup and restore using maintenance plans to azure blob storage but you cannot use the maintenance cleanup task to clear the data from blob storage like you can do for your on premise.
The only way out to achieve this is by using PowerShell script. We developed the below script which will delete the files which have been modified earlier than one day in the below script from the date it’s called. The date can be changed 1 day to any number needed per requirements. In the below example, we have deleted the files which are older than one day.
We created a new storage account for testing this by using the below script. Please test this before deploying( This will delete all files with all extensions from blob storage)
CLS
$Days =7 # for file older than 7 days
$backuptype = "bak" # for file extension .BAK
#$backuptype = "trn" # for file extension .trn
$container="backup"
$StorageAccountName="agdiag558"
$StorageAccountKey="qZ8Q3Sj6jnsxLx4oN2eWDyEGhDIdQEOlO4URX/a3Uk3goHtm/eWiLMslLowCxA+WvfxnGiz6STvI9B7Kx4YsRQ=="
$context = New-AzureStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageAccountKey
$filelist = Get-AzureStorageBlob -Container $container -Context $context
$filelist = $filelist | Where-Object {$_.Name -like '*.'+$backuptype}
foreach ($file in $filelist | Where-Object {$_.LastModified.DateTime -lt ((Get-Date).AddDays(-$Days))})
{
if ($file.Name -ne $null)
{
Write-Host "Removing file: $file.Name"
Remove-AzureStorageBlob -Blob $file.Name -Container $container -Context $context
$flag=1
}
}
if ($flag -eq 0 -or $flag -eq $null) { Write-Host "No files found which are older than:",(Get-Date).AddDays(-$Days)}
Sample Output: 
You can alternatively use the below script which also does the same job:
$CleanupTime = [DateTime]::UtcNow.AddHours(-72)
$context = New-AzureStorageContext -StorageAccountName ujpat2012test -StorageAccountKey zImjHXPe8aK5eY8CJnjSPdy8HcWHLG6dmtaamTkMa1jlzthICNAIhEGAvzLJV1FBi+V/3XNdAhrAZP5Dadsaii+A==
Get-AzureStorageBlob -Container "sqlbackup" -Context $context | Where-Object { $_.LastModified.UtcDateTime -lt $CleanupTime -and $_.BlobType -eq "PageBlob" -and $_.Name -like "*.bak"} |Remove-AzureStorageBlob
Written by – Ujjwal Patel, Support Engineer.
Reviewed by – Raghavendra Srinivasan , Support Escalation Engineer.
Comments
- Anonymous
November 06, 2017
hi, I get the following error :New-AzureStorageContext : The term 'New-AzureStorageContext' is not recognized as the name of a cmdlet, function,script file, or operable program. Get-AzureStorageBlob : The term 'Get-AzureStorageBlob' is not recognized as the name of a cmdlet, function, scriptfile, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correctand try again.- Anonymous
November 07, 2017
You haven't loaded the Azure PowerShell module (hence it is missing from the list you have). When you install the Cmdlets you will also get a new shortcut "Microsoft Azure Powershell" which will automatically load the module for you (and make the Cmdlets available). Can you run the command get-module and see if you can see the microsoft powershell Module installed as below:PS C:\Users\UJJWAL> GET-MODULEModuleType Version Name ExportedCommands ---------- ------- ---- ---------------- Script 1.0.0.0 ISE {Get-IseSnippet, Import-IseSnippet, New-IseSnippet} Manifest 3.1.0.0 Microsoft.PowerShell.Management {Add-Computer, Add-Content, Checkpoint-Computer, Clear-Content...} Manifest 3.1.0.0 Microsoft.PowerShell.Utility {Add-Member, Add-Type, Clear-Variable, Compare-Object...}- Anonymous
November 07, 2017
You can alteranetly use the below powershell which also works fine to delete the files:$CleanupTime = [DateTime]::UtcNow.AddHours(-72)$context = New-AzureStorageContext -StorageAccountName blakhanisql2012test -StorageAccountKey zImjHXPe8aK5eY8CJnjSPdy8HcWHLG6dmtaamTkMa1jlzthICNAIhEGAvzLJV1FBi+V/3XNdAhrAZP5Dadsaii+A==Get-AzureStorageBlob -Container "sqlbackup" -Context $context | Where-Object { $.LastModified.UtcDateTime -lt $CleanupTime -and $.BlobType -eq "PageBlob" -and $_.Name -like "*.bak"} |Remove-AzureStorageBlob
- Anonymous
- Anonymous