LLI Wiki — My Digital Knowledge Base

Rétention de fichiers .bacpac sur Azure Files avec PowerShell

Script PowerShell qui monte un partage Azure Files et conserve un bacpac par semaine, par mois puis par an.

Les exports Azure SQL Database (fichiers .bacpac) conservés sur un partage Azure Files s'accumulent vite. Ce script PowerShell monte le partage et applique une politique de rétention basée sur l'horodatage à 14 chiffres (yyyyMMddHHmmss) en fin de nom de fichier.

Règles de rétention

Pour chaque base (fichiers regroupés par la partie du nom avant l'horodatage) :

Les noms de fichiers doivent respecter <nom>-<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."

Remarques

Mastodon LLIMastodon SocialFosstodon