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

SQL SERVER - Powershell Script - Remove Old SQL Database Backup Files from Azure Storage backupicon 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.

Solarwinds
$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)

Solarwinds
, , , , ,
Previous Post
SQL SERVER – SQL Server: Getting OS information using DMV
Next Post
SQL SERVER – Unable to See Tables (Objects) in SSMS

Related Posts

6 Comments. Leave new

  • But this leaves all the .log files as well. What we need is to find the .bak closest to that date and delete all .log and .bak files earlier than that file then we get nice cleanup. I will try and work on that script.

    Reply
  • $Storagecontext = New-AzureStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageAccountKey
    Get-AzureStorageBlob -Container $Containername -Context $context | Where-Object { $_.LastModified.UtcDateTime -lt $HowOldFile -and $_.BlobType -eq &quot;PageBlob&quot; -and $_.Name -like $extension} | Remove-AzureStorageBlob
    

    Shouldn’t the last line be:

    Get-AzureStorageBlob -Container $Containername -Context $Storagecontext | Where-Object { $_.LastModified.UtcDateTime -lt $HowOldFile -and $_.BlobType -eq &quot;PageBlob&quot; -and $_.Name -like $extension} | Remove-AzureStorageBlob
    
    Reply
  • Hi I am looking for solution to restore SQL native backup in azure. Please share if any…

    Reply

Leave a Reply

Menu