locked
How to Automate .bak delete from Azure Blob? RRS feed

  • Question

  • Hi All,

    I have designed a solution to keep SQL Server Databases Backup to Azure Blob Storage. We want to keep only 5 Days Backup and and want to implement a cleanup process so that any backup which is older than 5 Days should be deleted.

    Followed: https://www.mssqltips.com/sqlservertip/4900/perform-onpremises-sql-server-database-backups-using-maintenance-plans-to-azure-blob-storage/

    Here backup Blob Type= Page Blob

    Thought of using Lifecycle Management but it supports only Block Blob (Supported blob types=Block Blob) and not Page Blob

    I know this can be done by using PowerShell but then I have to schedule PowerShell on a SQL Server Machine which has Windows 2012. Windows 2012 supports PowerShell Version 3 and It does not support Azure PowerShell commands.

    Below is the powershell I want to schedule using SQL Server Agent but It is not working and keep throwing error "The specified module 'AzureRM' was not loaded because no valid module file was found in any module directory."

    C:\Program Files (x86)\Microsoft SDKs\Azure\PowerShell\ResourceManager\AzureResourceManager

    I can't see AzureRM folder.

    #PowerShell Script to delete System Databases .bak files from Azure Block Blob
    import-module AzureRM

    $FromDate = ((Get-Date).AddDays(-5))
    $BlobType = "Pageblob"
    $bacs = Get-ChildItem $location -Filter *.bak
    $container="dwhdatabasesbackup"
    $StorageAccountName="analytics"
    $StorageAccountKey="xxxxxx"
    $context = New-AzureStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageAccountKey
    $filelist = Get-AzureStorageBlob -Container $container -Context $context
    foreach ($file in $filelist | Where-Object {$_.LastModified.DateTime -lt $FromDate -and $_.BlobType -eq $BlobType -and ($_.Name -like "*master*.bak" -or $_.Name -like "*msdb*.bak" -or $_.Name -like "*model*.bak" -or $_.Name -like "*SSISDB*.bak" -or $_.Name -like "*DW_MANAGER*.bak" -or $_.Name -like "*MasterDataServices*.bak" -or $_.Name -like "*BOREPO*.bak")})
    {
    $removefile = $file.Name
    if ($removefile -ne $null)
    {
    Write-Host "Removing file $removefile"
    Remove-AzureStorageBlob -Blob $removeFile -Container $container -Context $context
    }
    }

    I want to know is there any other way I can schedule this PowerShell to run and Cleanup files those are older than 5 Days?

    Please help.


    Thanks Shiven:) If Answer is Helpful, Please Vote




    Thursday, June 18, 2020 5:00 AM

Answers

  • Hi all,

    I have written this PS to Remove Files those are Older than 5 Days only When There was a matching name file was uploaded/Created/Modified Yesterday. 

    It is working as Expected:

    Running this PowerShell using Azure Runbook.  Basically have created a Automation Account and within it have created a Runbooks which executes below Powershell scripts and it is schedule to run once in a day. 

    https://docs.microsoft.com/en-us/azure/automation/automation-quickstart-create-account

    https://docs.microsoft.com/en-us/azure/automation/automation-quickstart-create-runbook

    #PowerShell Script to delete System Databases .bak files from Azure Block Blob
    #-eq = equals
    #-ne = not equals
    #-lt = less than
    #-gt = greater than
    #-le = less than or equals
    #-ge = greater than or equals
    #Set All Variables
    $YesterdayDate = ((Get-Date).AddDays(-1)).Date   #Get Yesterday date
    $FromDate = ((Get-Date).AddDays(-6)).Date        #Get 6 Days back date from Today
    $BlobType = "Pageblob"
    $bacs = Get-ChildItem $location -Filter *.bak
    $container="dwhdatabasesbackup"
    $StorageAccountName="analytics"
    $StorageAccountKey="xxxxxx"
    $context = New-AzureStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageAccountKey
    $filelist = Get-AzureStorageBlob -Container $container -Context $context
    #Foreach Loop With a Condition $_.LastModified.Date -eq $YesterdayDate to make Sure there was a File Uploaded Yesterday 
      
    foreach ($filein$filelist | Where-Object {$_.LastModified.Date -eq $YesterdayDate -and $_.BlobType -eq $BlobType -and ($_.Name -like "*.bak")})
    {
        
    $Yesterdayfile = $file.Name
        
    if ($Yesterdayfile -ne $null)
        {
            
    $FileFullLength = $Yesterdayfile.Length
            
    $FileNameWithoutDatePart = $Yesterdayfile.SubString(0$FileFullLength-30)
            Write-Output ("File Name Without Date Part: " +$FileNameWithoutDatePart)
            
    #Foreach Loop With a Condition $_.LastModified.Date -lt $FromDate to Remove Files those are 5 Days Old 
              
    foreach ($filein$filelist | Where-Object {$_.LastModified.Date -lt $FromDate -and $_.BlobType -eq $BlobType -and ($_.Name -like "$FileNameWithoutDatePart*.bak")})
            {
            $removefile = $file.Name

            $RemoveFileFullLength = $removefile.Length     

            $ModifiedDate = $file.LastModified.Date

            if (($removefile -ne $null) -and ($RemoveFileFullLength -eq $FileFullLength))
                {
                    Write-Output ("Remove File Name: ("+$removefile +") as Modified Date: ("$ModifiedDate +") of File is Older Than Date: ("$FromDate + ")")
                }
            }
        }
    }


    Thanks Shiven:) If Answer is Helpful, Please Vote




    Friday, June 19, 2020 5:58 AM

All replies

  • Hello,

    Is it not better to use an Azure Runbook with Powershell for this?

    Some example:

    https://savilltech.com/2018/03/25/writing-to-files-with-azure-automation/

    Kind regards,

    Niels



    • Edited by NKO_IT Thursday, June 18, 2020 5:54 AM
    Thursday, June 18, 2020 5:53 AM
  • Thanks for your input.

    Actually for backup I have used SQL Agent JOB and for cleanup If I make a separate process then these is a chance backup Job is failing for last 5 days and cleanup is keep running then Will not have any backup after 5 days.

    I followed this link 

    https://www.mssqltips.com/sqlservertip/4900/perform-onpremises-sql-server-database-backups-using-maintenance-plans-to-azure-blob-storage/

    So In SQL Job: 1st Step to Create Backup and on successful completion of Step 1 run Step 2 which is clean of backup files older than 5 Days. 

     

    Thanks Shiven:) If Answer is Helpful, Please Vote


    Thursday, June 18, 2020 6:31 AM
  • Hello,

    Is there way to create a secondary file for a backup when a backup has succesfully completed? (Create an extra job step for the file to be created after a succesfull backup.)

    Then adapt the powershell script with an IF statement to check for the secondary file and then delete the old backup.

    Is this something you can work with?

    Regards,

    Niels

    Thursday, June 18, 2020 8:42 AM
  • Hi all,

    I have written this PS to Remove Files those are Older than 5 Days only When There was a matching name file was uploaded/Created/Modified Yesterday. 

    It is working as Expected:

    Running this PowerShell using Azure Runbook.  Basically have created a Automation Account and within it have created a Runbooks which executes below Powershell scripts and it is schedule to run once in a day. 

    https://docs.microsoft.com/en-us/azure/automation/automation-quickstart-create-account

    https://docs.microsoft.com/en-us/azure/automation/automation-quickstart-create-runbook

    #PowerShell Script to delete System Databases .bak files from Azure Block Blob
    #-eq = equals
    #-ne = not equals
    #-lt = less than
    #-gt = greater than
    #-le = less than or equals
    #-ge = greater than or equals
    #Set All Variables
    $YesterdayDate = ((Get-Date).AddDays(-1)).Date   #Get Yesterday date
    $FromDate = ((Get-Date).AddDays(-6)).Date        #Get 6 Days back date from Today
    $BlobType = "Pageblob"
    $bacs = Get-ChildItem $location -Filter *.bak
    $container="dwhdatabasesbackup"
    $StorageAccountName="analytics"
    $StorageAccountKey="xxxxxx"
    $context = New-AzureStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageAccountKey
    $filelist = Get-AzureStorageBlob -Container $container -Context $context
    #Foreach Loop With a Condition $_.LastModified.Date -eq $YesterdayDate to make Sure there was a File Uploaded Yesterday 
      
    foreach ($filein$filelist | Where-Object {$_.LastModified.Date -eq $YesterdayDate -and $_.BlobType -eq $BlobType -and ($_.Name -like "*.bak")})
    {
        
    $Yesterdayfile = $file.Name
        
    if ($Yesterdayfile -ne $null)
        {
            
    $FileFullLength = $Yesterdayfile.Length
            
    $FileNameWithoutDatePart = $Yesterdayfile.SubString(0$FileFullLength-30)
            Write-Output ("File Name Without Date Part: " +$FileNameWithoutDatePart)
            
    #Foreach Loop With a Condition $_.LastModified.Date -lt $FromDate to Remove Files those are 5 Days Old 
              
    foreach ($filein$filelist | Where-Object {$_.LastModified.Date -lt $FromDate -and $_.BlobType -eq $BlobType -and ($_.Name -like "$FileNameWithoutDatePart*.bak")})
            {
            $removefile = $file.Name

            $RemoveFileFullLength = $removefile.Length     

            $ModifiedDate = $file.LastModified.Date

            if (($removefile -ne $null) -and ($RemoveFileFullLength -eq $FileFullLength))
                {
                    Write-Output ("Remove File Name: ("+$removefile +") as Modified Date: ("$ModifiedDate +") of File is Older Than Date: ("$FromDate + ")")
                }
            }
        }
    }


    Thanks Shiven:) If Answer is Helpful, Please Vote




    Friday, June 19, 2020 5:58 AM
  • Cool! Glad that you worked things out.
    Friday, June 19, 2020 11:13 AM