Automatiser la rétention à long terme (LTR) d'Azure SQL avec PowerShell planifié
Appliquer la politique LTR de façon idempotente, produire un rapport de vérification et tester la restauration automatiquement.
La rétention à long terme (LTR) d'Azure SQL conserve des sauvegardes complètes hebdomadaires, mensuelles ou annuelles pendant des années, au-delà de la restauration à un instant donné (PITR). Le service fait le travail, mais deux choses restent à notre charge : s'assurer que chaque base a bien la bonne politique, et prouver qu'on sait restaurer. Je les automatise avec un script PowerShell planifié.
Principe
flowchart LR
A["Planificateur (pipeline ou runbook)"] --> B["Script PowerShell (identité managée)"]
B --> C["1. Appliquer la politique LTR"]
B --> D["2. Rapport de vérification"]
B --> E["3. Test de restauration"]
C --> F["Azure SQL (serveurs et bases)"]
D --> G["E-mail HTML + CSV"]
E --> H["Base de test jetable"]
Le planificateur peut être un pipeline Azure DevOps, un runbook Azure Automation ou une tâche Azure Batch. Le script s'authentifie avec une identité managée : pas de secret à stocker.
1. La politique, appliquée de façon idempotente
Je décris la politique attendue une seule fois et le script corrige les écarts, y compris pour les bases créées depuis le dernier passage.
Connect-AzAccount -Identity | Out-Null
$expected = @{ Weekly = 'P4W'; Monthly = 'P12M'; Yearly = 'P5Y'; WeekOfYear = 1 }
foreach ($srv in Get-AzSqlServer) {
$dbs = Get-AzSqlDatabase -ServerName $srv.ServerName -ResourceGroupName $srv.ResourceGroupName |
Where-Object DatabaseName -ne 'master'
foreach ($db in $dbs) {
$p = Get-AzSqlDatabaseBackupLongTermRetentionPolicy -ServerName $srv.ServerName `
-DatabaseName $db.DatabaseName -ResourceGroupName $srv.ResourceGroupName
if ($p.WeeklyRetention -ne $expected.Weekly -or $p.MonthlyRetention -ne $expected.Monthly -or
$p.YearlyRetention -ne $expected.Yearly) {
Set-AzSqlDatabaseBackupLongTermRetentionPolicy -ServerName $srv.ServerName `
-DatabaseName $db.DatabaseName -ResourceGroupName $srv.ResourceGroupName `
-WeeklyRetention $expected.Weekly -MonthlyRetention $expected.Monthly `
-YearlyRetention $expected.Yearly -WeekOfYear $expected.WeekOfYear | Out-Null
}
}
}
Les durées sont au format ISO 8601 (P4W, P12M, P5Y). Pour une Managed Instance, il existe des cmdlets équivalentes au niveau de la base de l'instance.
2. Le rapport de vérification
Une politique correcte ne prouve pas qu'il existe des sauvegardes. Pour chaque base, je relève la dernière sauvegarde LTR et son âge.
$ltb = Get-AzSqlDatabaseLongTermRetentionBackup -Location westeurope `
-ServerName sql-contoso-prd -DatabaseName AppDb |
Sort-Object BackupTime -Descending
$last = $ltb | Select-Object -First 1
[pscustomobject]@{
Database = 'AppDb'
Count = @($ltb).Count
LastLtb = $last.BackupTime
AgeDays = if ($last) { [int]((Get-Date) - $last.BackupTime).TotalDays } else { $null }
}
Le rapport (CSV + e-mail HTML) classe chaque base en trois états : OK, politique non conforme corrigée, sauvegarde trop ancienne ou absente. Seuls les cas anormaux méritent une alerte.
3. Le test de restauration
Une sauvegarde qu'on n'a jamais restaurée est une hypothèse. Chaque semaine, je restaure la dernière sauvegarde LTR d'une base tirée au sort vers un serveur de test, je fais quelques contrôles, puis je supprime la copie.
Restore-AzSqlDatabase -FromLongTermRetentionBackup -ResourceId $last.ResourceId `
-ServerName sql-contoso-test -ResourceGroupName rg-dba-test `
-TargetDatabaseName AppDb_ltrtest -ServiceObjectiveName S0
# contrôles : DBCC CHECKDB, comptage des lignes d'une table clé...
Remove-AzSqlDatabase -ServerName sql-contoso-test -ResourceGroupName rg-dba-test `
-DatabaseName AppDb_ltrtest -Force
Le test mesure aussi la durée de restauration, une donnée précieuse pour répondre à « combien de temps pour récupérer ? ».
Pièges et retour d'expérience
- Délai initial : après la création d'une politique, la première sauvegarde LTR n'apparaît pas immédiatement. Le rapport doit tolérer une base récente.
- Droits : l'identité a besoin d'un rôle capable de modifier les serveurs SQL pour la politique, et de créer des bases sur le serveur de test pour la restauration. Je sépare les deux avec deux identités quand c'est possible.
- Le test de restauration coûte de l'argent et du temps : taille de service minimale, suppression systématique, y compris en cas d'échec (bloc
finally). - Bases orphelines de test : un échec en cours de route laisse une base derrière soi. Un nettoyage préalable par préfixe de nom l'évite.
- LTR n'est pas un export : les sauvegardes restent dans Azure et ne se restaurent que vers Azure SQL. Pour une copie portable (fichier BACPAC dans un stockage séparé), j'ajoute un export planifié en complément, à partir d'une copie de la base pour garder la cohérence transactionnelle.
- Politique en code : versionner la politique attendue dans le dépôt, c'est le meilleur moyen de la faire évoluer sans surprise.