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

At this time we don’t have any functionality available to automate the deletion of the backup files on Azure blob Storage account/container.

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

#To create new storage account New-AzureRmStorageAccount -ResourceGroupName resource-Test1 -Name bkuptourl -Type Standard_LRS -Location NorthEurope #Script to delete backup files $bacs = Get-ChildItem $location # use "-Filter *.bak" or "-Filter *.trn" for deleting bak or trn files specifically $container="bkup" $StorageAccountName="bkuptourl" $StorageAccountKey="xsVyDSvy48113b37ZEu0/VNkYAz9R81cO7UwOTp4qhDYU9zNbLAjioiOh3FVnzhO8n3tDYOyWnSkn==" $context = New-AzureStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageAccountKey $filelist = Get-AzureStorageBlob -Container $container -Context $context foreach ($file in $filelist | Where-Object {$_.LastModified.DateTime -lt ((Get-Date).AddDays(-1))}) { $removefile = $file.Name if ($removefile -ne $null) { Write-Host "Removing file $removefile" Remove-AzureStorageBlob -Blob $removeFile -Container $container -Context $context } }

Sample output

samplepsoutput

 

Written by:
Ujjwal Patel, Support Engineer, SQL Server Support

Reviewed by:
Raghavendra Srinivasan, Sr. Support Engineer, SQL Server Support

Comments

  • Anonymous
    May 12, 2017
    Is there anyway to protect the storage account key in the script ? I guess encrypting it in a file and having the script read it ... I don't like it though. I was hoping to use this PowerShell as a job step in a SQL Agent job, following a back up step, to purge old backups. I was hoping I could use my Azure credential (created for backups) in this as well - maybe create a proxy and run the job as the proxy account. But of course, proxies can only be Windows users ... !! Help please ?