Cleaning SQL backup files from Azure storage – PowerShell

Overview

Since SQL Server 2012, there is an option in the SQL Server backup to save the output file into an Azure storage account. During the last few project I was working in, I got the request of using this method more than one times. I configured the backup this way and then, I realized that there is no way for setting up retention policy. So, I decided to solve this problem not only for myself, but for my fellow Azure Engineers/Architects. The result is a PowerShell script which is capable of deleting specified files in a selected Azure storage account.

Prerequisites

There are only a few requirements to meet for the script:

  • Azure PowerShell module needs to be installed on the computer the script runs
  • Administrator permission for the user running the script.
  • At least modify permission for the user running the script on the Azure storage

Script

This script originally was designed to take care of old SQL backup files on the selected Azure storage, but it can work with other files as well. It is prepared to handle Simple and Full recovery model files. By executing the main script, the following tasks will be done:

  • Connection established to the given Azure storage account
  • Check the files on the specified container(s)
  • Remove files which match the criteria

Preparation

For successfully running the script and have the desired result, it needs to have the following information passed using start-up parameters:

  • Storage Account Name
  • Storage Account Security Key
  • Recovery Model (Full or Simple) – If you work with other than SQL backup files use Simple
  • Container used to store Full/Differential/Transaction log backup files
  • Extension of the Full/Differential/Transaction log backup files
  • Retention Time of the Full Backups
  • How many full backup need the differential and transaction log backup files to be kept

Parameters

The following list contains all the possible parameters for the script. Some of them must be used together:

  • blnFull – Alias: Full – Parameter is used to tell the script the Recovery Model. It changes the script behaviour to look for differential and transaction log backups. One of the parameters blnFull or blnSimple is mandatory. If this parameter is set, the script will check the availability of differential and transaction log backup files and remove the ones meet the criteria. The following parameters only available when blnFull is selected:
    • strContainerForDiff
    • strExtensionForDiff
    • strContainerForTrn
    • strExtensionForTrn
    • intKeepDiffForFull
  • blnSimple – Alias: Simple – Parameter is used to tell the script the Recovery Model. It changes the script behaviour to look only for full backup file. The parameters listed in the section blnFull are not available when Simple recovery model is selected. One of the parameters blnFull or blnSimple is mandatory. If this parameter is set, the script won’t look for any differential or transaction log backup files.
  • strStorageAccountName – Alias: StorageName – Mandatory parameter. It is for the Name of the Storage Account in Azure
  • strStorageAccountKey – Alias: StorageKey – Mandatory parameter. It is for the Secret Key of the Storage Account in Azure
  • strContainerForFull – Alias: ContainerFull – Mandatory parameter. It identifies the container used to store the full backup files
  • strExtensionForFull – Alias: ExtensionFull – This parameter identifies the extension of the files used for the full backups. Optional parameter, its default value is “.bak”
  • strContainerForDiff – Alias: ContainerDiff – Optional parameter. It identifies the container used to store the differential backup files. It is available only with blnFull. Default value is the same as strContainerForFull
  • strExtensionForDiff – Alias: ExtensionDiff – This parameter identifies the extension of the files used for the differential backups. Optional parameter, its default value is “.dif”
  • strContainerForTrn – Alias: ContainerTrn – Optional parameter. It identifies the container used to store the Transaction Log backup files. It is available only with blnFull. Default value is the same as strContainerForFull
  • strExtensionForTrn – Alias: ExtensionTrn – This parameter identifies the extension of the files used for the Transaction log backups. Optional parameter, its default value is “.trn”
  • strFilterForFileName – Alias: FileNameFilter – This parameter is for telling the script about the pattern of the files we would like to work with. Together with the extension it creates the full search pattern. Mandatory parameter
  • chrCleanupTimeUnitFull – Alias: CleanupTimeUnitFull – Parameter to identifies the retention time unit. Together with intCleanupTimeFrameFull it creates the retention time period. Mandatory parameter. It can holds the following characters:
    • Y or y – It means the time unit is YEAR
    • M or m – It means the time unit is MONTH
    • D or d – It means the time unit is DAY
    • H or h – It means the time unit is HOUR
  • intCleanupTimeFrameFull – Alias: CleanupTimeFrameFull – Parameters to identifies how many units the script needs to calculate into the past to identifies the deletion point. Together with chrCleanupTimeUnitFull it creates the retention time period. If the desired time is in the past the parameter must be negative. Mandatory parameter
  • intKeepDiffForFull – Alias: KeepDiffForFull – This parameter tell the script how many full backups in the past needs to have their differential and transaction log backup files kept. Optional parameter, its default value is 1. This means only the last full backup will have its differential and transaction log backup files kept.
  • blnVerbose – Alias: ScriptVerbose – If this parameter selected the script will use the console to inform the user which action is currently running. It is used only for informational purposes. Optional parameter, its default value is False.

Examples

Here are 2 examples to use the script. One for Simple recovery model and another one for Full.

  • Full recovery model – this run clean-up files for full backups which are more than 35 days old and match the pattern *file*.bak. After that it cleans all differential and transaction log backup files except the ones related to the last full backup. The name of the storage account is NameOfStorage with secret key called KeyOfStorage. The full backup files stored in the container called ContainerForFull with files in it using extention .bak. The differential backup files stored in the container called ContainerForDiff with files in it using extension .dif. The transaction log backup files stored in the container called ContainerForTrn with files in it using extention .trn

    .\AzureBlobCleanup.ps1 -Full -StorageName NameOfStorage -StorageKey KeyOfStorage -ContainerFull ContainerForFull -ExtensionFull “.bak” -ContainerDiff ContainerForDiff -ExtensionDiff “.dif” -ContainerDiff ContainerForTrn -ExtensionDiff “.trn” -CleanupTimeUnitFull D -CleanupTimeFrameFull -35 -FileNameFilter “*file*” -KeepDiffForFull 1 -ScriptVerbose

  • Simple recovery model – this run clean up files for full backup which are older than 35 days and match the pattern *file*.bak. After that it does not look for any differential or transaction log backup files. The name of the storage account is NameOfStorage with secret key called KeyOfStorage. The full backup files stored in the container called ContainerForFull with files in it using extension .bak

    .\AzureBlobCleanup.ps1 -Simple -StorageName NameOfStorage -StorageKey KeyOfStorage -ContainerFull ContainerForFull -ExtensionFull “.bak” -CleanupTimeUnitFull D -CleanupTimeFrameFull -35 -FileNameFilter “*file*” -ScriptVerbose

Download

The script can be downloaded from here

Summary

As I said before, this script was originally designed to use to clean-up SQL backup files from a designated Azure storage account, however it can be used every type of content which can be defined using the FileNameFilter and the ExtensionFull parameters correctly. With this script, I hope I made many of your life easier.

Enterprise Mobility Workshops - 24th November 2015 - London | 9:00am – 3:00pm

One thought on “Cleaning SQL backup files from Azure storage – PowerShell

Leave a Reply

Your email address will not be published. Required fields are marked *