SQL Server : stratégie de sauvegarde, automatisation et restauration

Met en place les sauvegardes d'une instance SQL Server installée (Linux ou Windows ; instance unique, FCI ou réplicas d'AG) et restaure à partir de ces sauvegardes. Sauvegarde : choix du mode de récupération, full hebdomadaire + différentielle quotidienne + sauvegarde du journal toutes les n minutes, COMPRESSION, CHECKSUM, chiffrement par certificat, rétention et planification (jobs T-SQL SQL Server Agent ou Ola Hallengren ; Task Scheduler / cron sur Express), sauvegarde COPY_ONLY ponctuelle, vérification des sauvegardes et contrôle de la chaîne. Récupération : restauration de la chaîne full / différentielle / journal, retour à un instant précis avec STOPAT (PITR), transaction marquée, restauration sous un autre nom ou sur un autre serveur avec WITH MOVE, tail-log + restauration après perte d'un fichier de données, page restore, restauration de master / msdb / model, mappage des logins (orphaned users) et chargement du certificat d'une sauvegarde chiffrée.

Choisissez votre tâche dans la carte des scénarios ; vous pouvez aussi saisir un numéro d'erreur (p. ex. 824, 4305, 33111, 18456) dans la recherche du catalogue pour accéder directement au scénario correspondant. Pour exporter une table ou le résultat d'une requête vers un fichier (déplacement de données, pas une sauvegarde), voir Export avec bcp.

Topologie: 1 serveur, Failover Cluster Instance ou réplicas d'AG Always On (restauration sur un autre serveur : source + cible)

Scénarios

• Mettre en place une stratégie de sauvegarde (sauvegardes planifiées + rétention) — Nouveau serveur ou base de données sans sauvegardes régulières : mise en place des jobs full / différentielle / journal, de la rétention et de la supervision.
• Prendre une sauvegarde ponctuelle (p. ex. COPY_ONLY avant un changement) — Une seule sauvegarde avant un changement, un patch ou un upgrade, ou pour une copie vers un environnement de test ; COPY_ONLY ne casse pas la chaîne.
• Vérifier les sauvegardes (historique, VERIFYONLY, restauration de test) — Pour prouver que les sauvegardes sont réellement restaurables : historique des sauvegardes, VERIFYONLY, HEADERONLY / FILELISTONLY et restauration de test dans une base temporaire + CHECKDB.
• La chaîne de sauvegarde est-elle intacte ? (trou de LSN, fichier manquant) — Avant une restauration ou lors d'un audit régulier : reconstitue la chaîne à partir de l'historique msdb ; détecte les trous de LSN, les fichiers absents du disque et les sauvegardes intercalées. Ne modifie rien.
• Restaurer une base de données à partir d'une sauvegarde (chaîne, STOPAT / STOPATMARK, MOVE / REPLACE, autre serveur) — Des données ont été supprimées ou mal mises à jour, une copie de la base est nécessaire ou elle doit être déplacée sur un autre serveur ; l'instance fonctionne. Chaîne full + différentielle + journal, instant précis ou transaction marquée, autre nom / répertoire, jusqu'au dernier instant avec une sauvegarde tail-log.
• La base de données ne s'ouvre pas : fichier de données manquant ou endommagé (tail-log + restauration) — La base est RECOVERY_PENDING ou SUSPECT, le .mdf/.ndf est illisible mais l'instance fonctionne : prenez d'abord une sauvegarde tail-log avec NO_TRUNCATE, puis restaurez jusqu'au dernier instant. La base reste indisponible jusqu'à la fin de l'opération.
• Quelques pages corrompues (Msg 823 / 824) : page restore — Des requêtes ou CHECKDB échouent sur des pages précises (msdb.dbo.suspect_pages) : seules les pages endommagées sont restaurées depuis la sauvegarde. En Enterprise/Developer la base reste en ligne ; en Standard/Express elle est hors ligne pendant un court moment.
• Restaurer la base de données master (mode mono-utilisateur) — master endommagée ou perte de logins / linked servers / paramètres serveur : l'instance est démarrée en mode mono-utilisateur (-m), master est restaurée, puis SQL Server s'arrête de lui-même. Une interruption de service est nécessaire ; si l'instance ne démarre pas du tout, les bases système sont d'abord reconstruites.
• Restaurer la base de données msdb ou model — Jobs, historique des sauvegardes, Database Mail (msdb) ou modèle des nouvelles bases (model) endommagés / perdus : restauration WITH REPLACE depuis une sauvegarde prise sur le même build ; Agent est arrêté brièvement.
• Les utilisateurs ne peuvent plus se connecter après une restauration (orphaned users, mappage des logins) — La base a été déplacée ou restaurée sur un autre serveur et l'application reçoit 'Login failed' : les logins sont transférés depuis la source avec leur SID et le hash du mot de passe, les utilisateurs restants sont rattachés à un login. La base reste en ligne.
• La sauvegarde chiffrée ne se restaure pas (certificat absent, Msg 33111) — La sauvegarde (ou la sauvegarde d'une base TDE) est chiffrée par un certificat absent de ce serveur : identifier le thumbprint, charger le certificat avec sa clé privée, vérifier la sauvegarde. Les bases existantes ne sont pas touchées.

Avant de commencer

• Le RPO (perte de données acceptable) et le RTO (interruption acceptable) ont été fixés par écrit avec le métier. Plan retenu : …au plus … minutes de perte de données…retour à la dernière sauvegarde full/différentielle (SIMPLE : jusqu'à une journée de perte de données)… ; la durée de restauration sera mesurée par une restauration de test de la plus grosse base.
• SQL Server fonctionne ; mssql-tools18 (sqlcmd) est installé et … est sysadmin. Avant d'exécuter le script, chargez le mot de passe dans une variable sans l'afficher : read -rsp 'Mot de passe : ' SQLCMDPASSWORD; export SQLCMDPASSWORD puis sudo --preserve-env=SQLCMDPASSWORD bash <script>.sh.
• PowerShell a été ouvert en tant qu'administrateur (Démarrer → "PowerShell" → clic droit → Run as administrator ; le titre affiche Administrator:) avec un compte Windows sysadmin dans SQL Server ; sqlcmd est dans le PATH.
• Le répertoire de sauvegarde (…) est sur un disque distinct du disque de données et dispose d'assez d'espace libre pour au moins une semaine de sauvegardes (en gros : taille des données utilisées × 2 + changement quotidien × nombre de jours ; en général 3 à 5 fois moins avec la compression).
• Le répertoire de sauvegarde (…) est sur un disque distinct du disque de données et dispose d'assez d'espace libre pour au moins une semaine de sauvegardes (en gros : taille des données utilisées × 2 + changement quotidien × nombre de jours ; en général 3 à 5 fois moins avec la compression).
• La taille des bases est connue ; la fenêtre de sauvegarde (durée de la full) ne chevauche pas les heures ouvrées.
• La méthode de copie des sauvegardes hors du serveur (autre serveur, NAS, stockage objet, logiciel de sauvegarde) est définie. Une sauvegarde sur le même disque ne sert à rien en cas de perte du disque ou du serveur.
• La version de SQL Server de restauration est identique ou plus récente que celle sur laquelle la sauvegarde a été prise. Une sauvegarde prise sur une version plus récente ne peut pas être restaurée sur une version plus ancienne (p. ex. 2022 → 2019 impossible).
• L'équipe applicative a été prévenue et l'application est arrêtée : toutes les connexions à la base écrasée par la restauration sont coupées et la base est indisponible pendant la restauration.
• Les répertoires de données (…) et de journal (…) existent sur la cible, appartiennent à mssql et disposent d'un espace libre égal à la taille des fichiers de la base à restaurer (pas la taille de la sauvegarde ; les valeurs Size de FILELISTONLY).
• Les répertoires de données (…) et de journal (…) existent sur la cible, le compte de service SQL Server a le droit d'écriture et l'espace libre correspond à la taille des fichiers de la base à restaurer.
• La source (…) est joignable depuis le serveur cible (…) …par SSH (root ou utilisateur sudo)…par le partage d'administration SMB (…)… ; les fichiers de sauvegarde peuvent être copiés.

Phases du guide

• État actuel — Lister les bases et les dernières sauvegardes
• Stratégie de sauvegarde — Préparer le répertoire de sauvegarde · Préparer le répertoire de sauvegarde · Définir le mode de récupération (…) · Définir la préférence de sauvegarde de l'AG (…) · Vérifier le réplica préféré · Créer le certificat et le sauvegarder (chiffrement) …
• Sauvegarde ponctuelle — …Prendre une sauvegarde full COPY_ONLY…Prendre une sauvegarde full…Prendre une sauvegarde différentielle…Prendre une sauvegarde du journal… (…) · Vérifier l'historique des sauvegardes (msdb.dbo.backupset)
• Restauration — Trouver la chaîne de restauration dans l'historique des sauvegardes · Copier les fichiers de sauvegarde sur le serveur cible · Copier les fichiers de sauvegarde sur le serveur cible · Charger le certificat sur le serveur cible · Charger le certificat sur le serveur cible · Examiner les fichiers de sauvegarde (VERIFYONLY, HEADERONLY, FILELISTONLY) …
• Vérification des sauvegardes — Vérifier l'historique des sauvegardes (msdb.dbo.backupset) · Examiner le fichier de sauvegarde (VERIFYONLY, HEADERONLY, FILELISTONLY) · Effectuer une restauration de test et exécuter DBCC CHECKDB
• Contrôle de la chaîne de sauvegarde — Diagnostic : état de la chaîne (base + historique msdb) · Les fichiers de sauvegarde sont-ils sur le disque ? · Les fichiers de sauvegarde sont-ils sur le disque ? · Vérifier chaque sauvegarde de la chaîne (VERIFYONLY) · Ensuite : prendre une nouvelle sauvegarde full si la chaîne est rompue
• Fichier de données manquant / endommagé : tail-log + restauration — Diagnostic : état de la base et de ses fichiers · Diagnostic : les fichiers sont-ils sur le disque, le fichier journal est-il intact ? · Diagnostic : les fichiers sont-ils sur le disque, le fichier journal est-il intact ? · Filet de sécurité : copier les fichiers restants · Filet de sécurité : copier les fichiers restants · Prendre une sauvegarde tail-log (NO_TRUNCATE) …
• Page restore (pages corrompues) — Diagnostic : trouver les pages corrompues (suspect_pages, CHECKDB) · Diagnostic : à quelle table / quel index appartient la page ? · Filet de sécurité : prendre une sauvegarde du journal, arrêter les jobs de sauvegarde du journal · Établir la séquence de restauration à partir de l'historique msdb (PAGE) · Prendre une sauvegarde tail-log (NORECOVERY) : la base passe hors ligne · Restaurer les pages endommagées et appliquer les sauvegardes du journal (NORECOVERY) …
• master restore — Diagnostic : fichier de sauvegarde, version et ce qui sera perdu · Diagnostic : pourquoi SQL Server ne démarre-t-il pas ? · Diagnostic : pourquoi SQL Server ne démarre-t-il pas ? · Reconstruire les bases système (master ne démarre pas) · Reconstruire les bases système (master ne démarre pas) · Démarrer SQL Server en mode mono-utilisateur …
• Restauration de msdb / model — Diagnostic : état, version et fichier de sauvegarde · Filet de sécurité : sauvegarder la base … actuelle · Arrêter SQL Server Agent · Restaurer la base … (REPLACE) · Démarrer SQL Server Agent · Vérifier
• Mappage des logins (orphaned users) — Diagnostic : trouver les utilisateurs orphelins (cible) · Générer le script des logins sur la source (SID + hash de mot de passe) · Générer le script des logins sur la source (SID + hash de mot de passe) · Copier le script des logins sur la cible et l'exécuter · Copier le script des logins sur la cible et l'exécuter · Mapper les utilisateurs restants sur des logins (ALTER USER WITH LOGIN) …
• Sauvegarde chiffrée : charger le certificat — Diagnostic : certificats et master key de ce serveur · Diagnostic : thumbprint du fichier de certificat · Diagnostic : thumbprint du fichier de certificat · Charger le certificat avec sa clé privée · Charger le certificat avec sa clé privée · Vérifier : la sauvegarde est désormais lisible …