LLI Wiki — My Digital Knowledge Base

Retention policy for .bacpac files on Azure Files with PowerShell

PowerShell script that mounts an Azure Files share and keeps one bacpac per week, per month, then per year.

Azure SQL Database exports (.bacpac files) kept on an Azure Files share can pile up quickly. This PowerShell script mounts the share and applies a retention policy based on the 14-digit timestamp (yyyyMMddHHmmss) at the end of each file name.

Retention rules

For each database (files grouped by the name part before the timestamp):

File names must match <name>-<yyyyMMddHHmmss>.bacpac.

Script

# Define the storage account (Azure Files endpoint)
$script:StorageAccountName = "mystorageaccount.file.core.windows.net"
# Test connection to the storage account on port 445 (SMB)
$script:connectTestResult = Test-NetConnection -ComputerName $script:StorageAccountName -Port 445 -InformationLevel Quiet

if ($script:connectTestResult -eq $true) {
    try {
        # Mount the network drive using cmdkey for authentication
        cmd.exe /C "cmdkey /add:`"mystorageaccount.file.core.windows.net`" /user:`"localhost\mystorageaccount`" /pass:`"<PASSWORD>`""

        if ($LASTEXITCODE -ne 0) {
            throw "Failed to execute cmdkey command."
        }

        New-PSDrive -Name Z -PSProvider FileSystem -Root ("\\" + $script:StorageAccountName + "\sqldb-backup") -Persist -ErrorAction Stop
        Write-Host ":: Drive Z mounted successfully."
    } catch {
        Write-Host ":: An error occurred: $($_.Exception.Message)"
    }
} else {
    Write-Host ":: Unable to access the Azure storage account via port 445."
}
Write-Host ":: Storage drive setup complete."

###########################
# Directory where .bacpac files are stored
$directoryPath = "Z:\"

# Regex pattern for matching any .bacpac file name ending with -<14 digits>.bacpac
$filePattern = ".*-\d{14}\.bacpac"

# Function to extract date from filename
function Get-DateFromFilename {
    param (
        [string]$fileName
    )

    if ($fileName -match "\d{14}") {
        $datePart = $matches[0]
        return [datetime]::ParseExact($datePart, "yyyyMMddHHmmss", $null)
    } else {
        Write-Output "No valid date found in the file name."
        return $null
    }
}

# Function to extract the base part of the filename (without the date)
function Get-BaseFilename {
    param ($filename)
    if ($filename -match "^(.*)-\d{14}\.bacpac$") {
        return $matches[1]
    }
    return $null
}

# Retention periods
$weeksInMonth = 4
$monthlyRetention = 3
$annualRetention = 1

# Get all files matching the pattern
$files = Get-ChildItem -Path $directoryPath -Filter "*.bacpac" | Where-Object { $_.Name -match $filePattern }

# Group files by base name (excluding the date)
$groupedFiles = $files | Group-Object { Get-BaseFilename($_.Name) }

# Files to retain
$filesToKeep = @()

foreach ($group in $groupedFiles) {
    # Sort files by date in descending order
    $sortedFiles = $group.Group | Sort-Object { Get-DateFromFilename($_.Name) } -Descending

    # Keep one file per week for the last 4 weeks
    $weeksAgoThreshold = (Get-Date).AddDays(-7 * $weeksInMonth)
    $filesByWeek = $sortedFiles | Where-Object {
        $fileDate = Get-DateFromFilename($_.Name)
        $fileDate -ne $null -and $fileDate -ge $weeksAgoThreshold
    } | Group-Object {
        $fileDate = Get-DateFromFilename($_.Name)
        if ($fileDate -is [datetime]) {
            $year = $fileDate.ToString("yyyy")
            $weekOfYear = [System.Globalization.CultureInfo]::InvariantCulture.Calendar.GetWeekOfYear(
                $fileDate,
                [System.Globalization.CalendarWeekRule]::FirstFourDayWeek,
                [System.DayOfWeek]::Monday
            )
            "{0}{1:D2}" -f $year, $weekOfYear
        }
    }

    $filesByWeek | ForEach-Object {
        $filesToKeep += $_.Group | Sort-Object { Get-DateFromFilename($_.Name) } -Descending | Select-Object -First 1
    }

    # Keep one file per month for the last 3 months
    $monthsAgoThreshold = (Get-Date).AddMonths(-$monthlyRetention)
    $filesByMonth = $sortedFiles | Where-Object {
        $fileDate = Get-DateFromFilename($_.Name)
        $fileDate -ne $null -and $fileDate -ge $monthsAgoThreshold -and $fileDate -lt $weeksAgoThreshold
    } | Group-Object {
        $fileDate = Get-DateFromFilename($_.Name)
        if ($fileDate -is [datetime]) {
            $fileDate.ToString("yyyyMM")
        }
    }

    $filesByMonth | ForEach-Object {
        $filesToKeep += $_.Group | Sort-Object { Get-DateFromFilename($_.Name) } -Descending | Select-Object -First 1
    }

    # Retain a file per year for older files
    $filesByYear = $sortedFiles | Where-Object {
        $fileDate = Get-DateFromFilename($_.Name)
        $fileDate -ne $null -and $fileDate -lt $monthsAgoThreshold
    } | Group-Object {
        $fileDate = Get-DateFromFilename($_.Name)
        if ($fileDate -is [datetime]) {
            $fileDate.ToString("yyyy")
        }
    }

    $filesByYear | ForEach-Object {
        $filesToKeep += $_.Group | Sort-Object { Get-DateFromFilename($_.Name) } -Descending | Select-Object -First 1
    }
}

# Identify files to delete (files not in the files to keep)
$filesToDelete = $files | Where-Object { $_ -notin $filesToKeep }

# Delete the files that are not retained (dry run: uncomment Remove-Item to delete)
$filesToDelete | ForEach-Object {
    Write-Host "To delete: $($_.FullName)"
    #Remove-Item $_.FullName -Force
}

Write-Host "Cleanup completed."

Notes

Mastodon LLIMastodon SocialFosstodon