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):
- Last 4 weeks: keep one file per ISO week (the most recent one).
- Older than 4 weeks and up to 3 months: keep one file per month.
- Older than 3 months: keep one file per year.
- Every other file is selected for deletion.
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
- The storage account name, share name and account are generic examples. Replace
<PASSWORD>with the storage account key; never store it in the script, prefer a secret store. - Port 445 (SMB) must be reachable from the machine running the script.
Remove-Itemis commented out: the script only lists the files that would be deleted. Review the output, then uncomment it.$annualRetentionis defined but not used: one file per year is always kept.