Comment planifier un backup de SQL Server

Avoir sous la main un plan de sauvegarde et de restauration est une partie importante d’une stratégie de reprise d’activité ou Disaster Recovery Plan (DRP). Cet article va décrire trois solutions ou approches différentes pour créer une sauvegarde (backup) planifié pour SQL Server.

Nous pouvons compter sur différents types de backup pour construire notre stratégie de backup.

Types de backups

  1. Base de données complète ou backup full. Ce type de backup inclut tous les objets de la base de données, les tables systèmes, les données, et les transactions qui ont eu lieu pendant le backup. Les backups full permettent une restauration complète d’une base de données à l’état où elle se trouvait avant le backup
  2. Backup différentiel. Ce type de backups contient seulement les données qui ont changé depuis le dernier backup full ainsi que les transactions qui ont eu lieu pendant le processus de sauvegarde. Un backup différentiel est utilisé avec le dernier backup full effectué sur la base de données. Les backups différentiels pris antérieurement au dernier backup différentiel, mais consécutifs à un même backup full, peuvent être considérés comme obsolètes car ce dernier backup différentiel contient toutes les modifications effectuées sur la base de données depuis le dernier backup full et de facto l’ensemble des informations reprises dans les backups différentiels antérieurs.
  3. Backup du journal des transactions (transaction log) ou backup log. Ce type de backup contient l’ensemble des transactions effectuées pour une base de données depuis le backup log précédent et tronque le contenu du transaction log. Un backup log permet d’assurer la restauration d’une base de données à un moment spécifique comme par exemple, le moment avant la perte d’une donnée.
  4. Backup de fichier ou de groupement de fichiers. Cette option convient principalement pour la sauvegarde de bases de données de taille importante. Un backup de fichier contiendra toutes les données d’un ou plusieurs fichiers ou d’un groupement de fichiers. Un backup log doit également être effectué pour couvrir toutes les backups de ce type en cas de restauration où les sauvegardes de l’ensemble des fichiers, prises à des moments différents, doivent être utilisées
  5. Backups copie seule ou backups copy-only. C’est le type de backup le plus utilisé pour effectuer une sauvegarde sans affecter le plan de sauvegarde mis en place pour une base de données. On peut créer des backups full et des backups log de ce type. Ainsi, un backup full copy-only est fonctionnellement équivalent à un backup full. Cependant, un backup log copy-only contiendra toutes les transactions effectuées depuis le dernier backup full, ce qui signifie qu’un backup full copy-only est ignoré pour la prise d’un backup log copy-only.

Recommandations pour développer une stratégie de backup


Emplacement de la sauvegarde

Il est conseillé que les sauvegardes (backups) ne soient pas effectuées au même endroit (même disque physique) que celui où se trouvent les bases de données. Ainsi, lorsqu’un disque physique qui contient des bases de données est en panne, nous pouvons utiliser l’autre disque ou le partage réseau utilisé pour restaurer les bases de données perdues. Si aucun emplacement pour la sauvegarde n’est spécifié à SQL Server, alors SQL Server utilisera les emplacements par défaut qui lui ont été définis lors de l’installation.

Il est à noter que changer les emplacements par défaut pur la création d’une base de données n’aura aucune influence sur les emplacements des fichiers de données et de journalisation existants. L’effet de cette modification ne sera pris en compte que lorsque vous créerez de nouvelles bases de données.


Planifier et automatiser les backups

Pour s’assurer qu’un backup est correctement effectué et qu’il est fiable, il est préférable de l’automatiser à l’aide d’un système de planification. De plus, une sauvegarde régulière est importante car plus le temps passe et plus les backups déjà effectués deviennent obsolètes vu que les données changent ou sont créées et ces backups ne reflètent dont plus l’état courant d’une base de données.

Protégez-vous et assurez-vous que vous aurez toujours sous la main un moyen de rétablir vos données jusqu’au moment qui précède un incident affectant une base de données. Des backups réguliers permettent d’avoir un historique précis de vos données.

La fréquence des backups d’une base de données dépend des besoins d’une entreprise(,…) et est défini en terme de perte de données maximale admissible ou en anglais Recovery Point Objective (RPO). Par exemple, si l’accord du niveau de service ou, en anglais, Service Level Agreement (SLA), d’une société spécifie que la quantité maximale de données perdue ne peut excéder l’heure, alors le RPO est d’une heure.


Tester les sauvegardes

Les stratégies de sauvegarde et restauration ne peuvent être complètes si les sauvegardes qui sont prises ne peuvent être restaurées avec succès sur un serveur de test et que nous nous sommes assurés que les backups que nous prenons permettent de remplir toutes les exigences et toutes les conditions pour toutes les combinaisons définies dans nos stratégies de restauration. Il y a plusieurs facteurs qui entrent en ligne de compte comme : les exigences de la société en termes d’utilisation des données, de protection de ces données…


Vérification des backups

La vérification des backups nous permet de vérifier qu’une sauvegarde a été créée correctement, est intacte physiquement, et que tous les fichiers peuvent être lus et restaurés dans les cas définis par l’utilisateur. Elle permet aussi de vérifier que toutes les transactions sont consistantes. Il est important de comprendre que la vérification d’un backup n’a aucune action sur la vérification de la structure des données contenues dans le backup. Cependant, si un backup a été créé avec l’option WITH CHECKSUMS, alors vérifier le backup en spécifiant cette même option WITH CHECKSUMS permettra d’avoir de bonnes indications sur la fiabilité des données du backup.

En utilisant T-SQL:

Inclure l’option CHECKSUM assure la consistance des données dans le backup de destination. Nous le faisons comme dans la requête ci-dessous:

BACKUP DATABASE [AdventureWorks2012]
TO  DISK = N'F:\Backup\AW12.bak'
WITH CHECKSUM;

SQL Server Management Studio (SSMS) fournit aussi l’option d’inclure une vérification de la sauvegarde en présentant une case à cocher “checksum” dans l’écran de création d’un backup:

Les options Verify backup when finished (vérifier la sauvegarde en fin d’opération) et Perform checksum before writing to media (Effectuer un contrôle checksum avant d’écrire sur le média) sont utilisées pour assurer que tant le backup créé ou les données qu’il contient sont consistants.

Nous allons également montrer comment inclure des vérifications dans des backups planifiés.

Dans cet article, nous allons créer une sauvegarde SQL Server planifiée en utilisant un job SQL Server Agent et plans de maintenance SQL Server.

Planifier un backup avec un job SQL Server Agent

Pour automatiser et planifier un backup avec SQL Server Agent:

  1. Dans l’explorateur d’objets, sous le noeud SQL Server Agent, cliquez droit sur Jobs et sélectionner New Job dans le menu contextuel:

  2. Dans la boite de dialogue New Job, entrez le nom du job
  3. Dans la zone consacrée aux étapes du job, cliquez sur le bouton New et créez une étape de backup en insérant le code T-SQL correspondant. Dans l’exemple ci-dessous, la clause CHECKSUM est spécifiée:
    USE AdventureWorks2012
    GO
    BACKUP DATABASE [AdventureWorks2012]
    TO  DISK = N'F:\Backup\AW12.bak'
    WITH CHECKSUM;
    

    Pour créer un backup différentiel, utilisez le script T-SQL suivant:

    USE AdventureWorks2012
    GO
    BACKUP DATABASE [AdventureWorks2012]
    TO  DISK = N'F:\Backup\AW12.bak'
    WITH CHECKSUM;
    
    
    BACKUP DATABASE [AdventureWorks2012]
       TO  DISK = N'F:\Backup\AWD12.bak'
       WITH DIFFERENTIAL;
       WITH CHECKSUM;
    
    GO
    

    Pour créer un backup log, utilisez le script T-SQL suivant:

    BACKUP LOG [AdventureWorks2012]
       TO  DISK = N'F:\Logs\AWD12.log';
    GO
    

    Note: Pour créer un backup différentiel ou un backup log d’une base de données, au moins un backup full doit déjà avoir été pris préalablement. Si la base de données considérée n’a jamais fait l’objet d’un backup full, avant de créer un backup différentiel, créez d’abord un backup full. Les backups différentiels et backups logs peuvent être utilisés en lien avec un backup full. Par exemple, un backup full est planifiée toutes les 24 heures, un backup différentiel toutes les 5 heures et un backup log toutes les 15 minutes.

  4. Cliquez sur Add a step puis cliquer sur OK pour créer le job:

  5. Pour planifier une exécution du job, dans la boite de dialogue de création d’un nouveau job, cliquez sur le bouton New dans la zone réservée à la planification (Schedule“)
  6. Dans la boite de dialogue qui vient d’apparaitre, sélectionnez une fréquence d’exécution et une date de début puis cliquez sur OK:

Pour vérifier que le job a été créé et fonctionne, aller dans l’explorateur d’objets, sous le noeud SQL Server Agent puis jobs, cliquez droit sur le job créé et sélectionnez Start Job at Step:

Utiliser le SQL Server Agent pour effectuer régulièrement le backup de toutes les bases de données d’une instance peut se faire de deux manières. Dans tous les cas, ceci requière des opérations manuelles. Une première approche consiste à créer un package SSIS en utilisant l’option Backup Task de la barre d’outil SSIS et de créer un job SQL Server Agent pour y faire appel.

L’autre approche consiste à écrire un script T-SQL qui sauvegarde toutes les bases de données comme opération d’un job SQL Server Agent.

Créer des backups réguliers en utilisant les plans de maintenance SQL Server

Il y a deux options pour créer une tâche planifiée pour effectuer un backup avec un plan de maintenance SQL Server: créer le plan manuellement ou en utilisant l’assistant de création de plan de maintenance.

Pour créer un plan de maintenance manuellement qui effectuera des backups:

  1. Dans le panneau Object Explorer, sous le noeud Management, cliquez droit sur Maintenance Plans et sélectionnez l’option New maintenance plan:

  2. Depuis la boite à outils Maintenance Plan Tasks, sélectionnez Back Up Database Task:

    L’assistant de création de plans de maintenance fournit également une tâche de vérification de l’intégrité Check database integrity Task qui peut être inclue au plan de maintenance:

  3. Double-cliquez sur l’élément ajouté au plan correspondant à la tâche de backup et définissez les options de backup:

  4. Pour planifier un job SQL Server Agent, utilisez l’option Sub plan scheduling
  5. Quand un plan est créé, cliquez sur Save et cette action créera un job correspondant sous le dossier Jobs de SQL Server Agent

Il existe également une méthode pour créer un nouveau plan de maintenance basée sur l’assistant de création de plan de maintenance, qui guide son utilisateur durant tout le processus, mais cette option comporte moins de paramètres pour une configuration fine.

Pour automatiser et planifier un backup avec l’assistant de création de plans de maintenance:

  1. Dans le panneau Object Explorer, sous le noeud Management, cliquez droit sur Maintenance Plans et sélectionnez l’option Maintenance Plan Wizard:

  2. Dans la fenêtre de sélection des propriétés du plan, spécifiez un nom pour le plan. Pour planifier un job SQL Server Agent, cliquez sur le bouton Change:

  3. Dans la partie de sélection des tâches du plan de maintenance, sélectionnez les options Back Up Database et Check data integrity. La tâche check data integrity effectue un test de consistance interne des données et des pages d’index au sein de la base de données

  4. Dans la fenêtre suivante, configurez la tâche de maintenance en choisissant une base de données à sauvegarder et les options de backup. Dans la fenêtre Define Back Up Task, sélectionnez également l’option Verify backup integrity:

  5. Vérifiez vos choix dans le résumé puis cliquez sur Finish:

Les plans de maintenance sont plus adaptés pour les DBAs moins expérimentés parce qu’ils fournissent une interface graphique simple à utiliser et ne requière aucun script de maintenance écrit manuellement. L’inconvénient des plans de maintenance est que les tâches qu’ils permettent sont basiques et ne laissent que peu de place à la personnalisation.

Un plan de maintenance est également atomique et il est dès lors impossible d’exécuter plusieurs tâches. Chaque type de tâches de maintenance ne peut être configurée que pour tourner une seule fois au sein d’un même plan de maintenance. Par exemple, si une tâche est créée pour supprimer les anciens fichiers de backup, cette tâche ne supprimera qu’un seul type de fichier à la fois. Pour cette raison, nous sommes obligés dans certains cas de créer plusieurs plans de maintenance pour effectuer une tâche simple. De plus, à chaque plan de maintenance doit correspondre un job SQL Server Agent qui doit être planifié.

Ressources utiles:

Backup Overview (SQL Server)
Maintenance Plans
Schedule a Job

August 26, 2017