Sauvegarde automatique de SQL Server avec 4 méthodes !

Comment faire une sauvegarde automatique de base de données SQL Server ?

Il n'est peut-être pas trop difficile de sauvegarder quelques bases de données SQL Server de temps en temps, mais si vous en avez un grand nombre, ou si vous avez besoin de sauvegarder fréquemment pour assurer la cohérence des données, le configurer manuellement à chaque fois serait fastidieux.

Par conséquent, vous vous demandez peut-être comment prendre une sauvegarde automatique de SQL Server. Et il existe plus d'une solution :

◆ Pour les utilisateurs SQL normaux, le moyen le plus accessible est d'utiliser le Plan de maintenance ou l'Agent SQL Server dans SSMS (SQL Server Management Studio). Ils fonctionnent de manière similaire, mais si vous ne voulez pas utiliser de commandes ou de scripts du tout, vous pouvez choisir le premier.
◇ Cependant, ces outils ne sont pas disponibles dans SSMS si vous utilisez des éditions Express de SQL Server. Au lieu de cela, vous devez créer un fichier batch avec une ligne de commande et utiliser le Planificateur de tâches pour l'automatiser.
◆ Si vous trouvez ces méthodes fastidieuses, vous pouvez également utiliser un logiciel de sauvegarde automatique SQL en tant qu'alternative. Ainsi, vous pouvez facilement sauvegarder automatiquement des bases de données ou des instances avec quelques clics seulement, que ce soit une édition Express ou non.

Dans la suite, vous découvrirez les différentes méthodes présentés en détails. 

Méthode 1. Sauvegarde automatique de bases de données SQL via le Plan de maintenance

Un plan de maintenance crée un flux de travail des tâches pour maintenir votre base de données. Vous pouvez également l'utiliser pour planifier la sauvegarde de SQL Server.

1. Lancez SSMS et connectez-vous à votre serveur. Développez le menu Gestion sous une instance, puis cliquez avec le bouton droit sur Plans de maintenance pour créer un nouveau plan de maintenance ou ouvrez l'Assistant Plan de maintenance.

Ils sont essentiellement les mêmes, mais l'assistant vous guidera tout au long de l'opération. Ici, je choisirai le premier comme exemple.

2. On vous demandera de donner un nom à ce plan. Après confirmation, vous pouvez cliquer sur Boîte à outils sur le côté gauche et faire glisser la tâche Sauvegarde de base de données vers la partie inférieure de la fenêtre du Plan de maintenance.

3. Double-cliquez sur la tâche générée, vous pouvez la configurer dans la fenêtre contextuelle. Tout d'abord, choisissez le type de sauvegarde parmi Complète, Différentielle et Journal des transactions. Ensuite, sélectionnez une ou plusieurs bases de données à sauvegarder. Cliquez sur OK pour confirmer. Si vous avez sélectionné plus d'une base de données, vous pouvez également choisir de créer un fichier de sauvegarde pour chaque base de données si vous le souhaitez.

• La sauvegarde complète inclut tous les objets de base de données, les tables système, les données et les transactions qui ont lieu pendant la sauvegarde.
• La sauvegarde différentielle comprend les données qui ont été modifiées et les transactions qui ont eu lieu depuis la dernière sauvegarde complète.
• La sauvegarde du journal des transactions inclut les transactions qui ont eu lieu sur une base de données depuis la précédente sauvegarde du journal des transactions, puis tronque le journal des transactions. Une sauvegarde du journal des transactions garantit la récupération de la base de données à un moment précis, par exemple, avant la perte de données.

4. Maintenant, vous pouvez cliquer sur l'icône du calendrier, appelée Programmer le sous-plan, pour automatiser la tâche de sauvegarde de la base de données. Pour une planification de sauvegarde SQL basée sur le temps, vous pouvez choisir Récurrent comme type de planification, puis configurer la fréquence et la durée.

Alternativement, vous pouvez également rendre la tâche Démarrer automatiquement lorsque SQL Server Agent démarre, ou Démarrer lorsque les processeurs deviennent inactifs.

5. Enregistrez les modifications et vous avez maintenant configuré la sauvegarde automatique du serveur SQL. Vous pouvez ensuite trouver la tâche sous SQL Server Agent et toutes vos sauvegardes seront effectuées selon le planning spécifié.

Voici comment automatiser la sauvegarde SQL Server avec l'assistant graphique. Si vous avez des connaissances en T-SQL, vous pouvez également envisager la méthode suivante.

Méthode 2. Tâche planifiée pour sauvegarder la base de données avec SQL Server Agent

SQL Server Agent est un service qui exécute des tâches administratives planifiées. Chaque tâche contient une ou plusieurs étapes, et chaque étape contient sa propre tâche, par exemple la sauvegarde de la base de données.

1. Pour l'utiliser, lancez SSMS et connectez-vous à votre instance. Assurez-vous que SQL Server Agent est activé. Sinon, cliquez dessus avec le bouton droit de la souris et démarrez-le. Ensuite, choisissez Nouveau > Tâche à partir du menu clic-droit.

2. La fenêtre Nouvelle tâche apparaîtra. Vous pouvez saisir son Nom et sa Description dans l'onglet Général.

3. Ensuite, passez à l'onglet Étapes, cliquez sur Nouveau en bas à gauche pour la configurer. Assurez-vous d'abord que le Type est Script Transact-SQL (T-SQL), et choisissez Database comme Master. Ensuite, vous pouvez saisir la commande pour effectuer la sauvegarde.

La commande de base pour la sauvegarde de la base de données ressemblait à ceci : 

BACKUP DATABASE example

TO DISK = 'D:\SQL Server\example.bak';

Il suffit de remplacer le nom de la base de données et le chemin du fichier selon vos besoins. Si vous souhaitez effectuer une sauvegarde différentielle des bases de données, vous pouvez ajouter une ligne WITH DIFFERENTIAL. Mais veuillez noter qu'une sauvegarde complète doit être créée au préalable.

Pour sauvegarder le journal des transactions, remplacez simplement DATABASE par LOG dans la commande :

BACKUP LOG example

TO DISK = 'D:\SQL Server\example.trn';

Cliquez sur OK pour confirmer la commande, puis passez à l'onglet Planification pour automatiser la sauvegarde SQL Server.

4. De la même manière, cliquez sur Nouveau pour créer une nouvelle planification. Vous pouvez lui donner un nom, choisir un type de planification et configurer la fréquence selon vos préférences. Une fois terminé, cliquez sur OK pour l'enregistrer.

5. La configuration principale est terminée. Vous pouvez cliquer sur OK pour l'exécuter immédiatement ou continuer à configurer les alertes et les notifications.

Une fois que tout est en place, vous pouvez faire un clic droit sur la tâche créée, choisir Démarrer la tâche à l'étape... pour tester son bon fonctionnement. Si ce n'est pas le cas, veuillez cliquer sur Afficher l'historique dans le menu contextuel pour vérifier les erreurs.

Notez que cette fonctionnalité n'est pas disponible pour les sauvegardes SQL Express. Si vous utilisez une édition Express, veuillez passer à la solution suivante.

Méthode 3. Automatiser la sauvegarde SQL Server (Express) via le Planificateur de tâches

Bien qu'il existe plusieurs solutions pour effectuer une sauvegarde automatique de SQL Server, dans les éditions Express, vous n'avez pas autant de choix. Sans les fonctions de Plan de maintenance et d'Agent SQL Server intégrées à SSMS, vous devez créer un fichier batch contenant un script Transact-SQL, puis utiliser le Planificateur de tâches Windows pour l'automatiser.

Voici ce que vous devez préparer et comment automatiser les sauvegardes de SQL Server Express :

✎ Note : l'utilitaire SQLCMD est requis pour entrer des instructions Transact-SQL, des procédures système et des fichiers de script. Il est livré en tant que partie du produit dans SQL Server 2014 et les versions inférieures. Mais si vous utilisez SQL Server 2016 et une version ultérieure, vous devez le télécharger sur cette page.

1. Connectez-vous à votre instance SQL Server Express, recherchez les Procédures stockées sous Bases de données > Bases de données système > master > Programmabilité. Faites un clic droit dessus et cliquez sur Nouvelle procédure stockée.

2. Dans la fenêtre SQLQuery, copiez tout le contenu du script de sauvegardes SQL Express fourni par Microsoft, puis cliquez sur le bouton Exécuter ci-dessus. Cela créera une procédure stockée sp_BackupDatabases dans votre base de données principale.

3. Ouvrez un éditeur de texte et saisissez le script de sauvegarde. La commande de base pour effectuer une sauvegarde complète ressemblait à ceci :

Par exemple, si je veux sauvegarder toutes les bases de données de l'instance nommée locale de MSSQLSERVER_01 en utilisant l'authentification Windows, la commande est la suivante :

sqlcmd -S .\MSSQLSERVER_01 -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQL Server', @backupType='F'"

Notes :✎...

Vous pouvez copier le nom du serveur et de l'instance à partir des propriétés du serveur. Assurez-vous que c'est correct.

Si vous souhaitez utiliser l'authentification SQL Server, vous pouvez ajouter les commutateurs: -U -P

Le commutateur -E signifie utiliser une connexion de confiance et ne peut pas être utilisé avec -U ou -P.

Pour effectuer une sauvegarde différentielle, remplacez le dernier F par D. De même, vous pouvez utiliser L pour la sauvegarde du journal des transactions.

Vous pouvez également sauvegarder individuellement des bases de données SQL en fonction de vos préférences, par exemple :

4. Enregistrez le fichier avec l'extension .bat. Vous pouvez lui donner un nom tel que Sqlbackup.bat.

5. Ouvrez le Planificateur de tâches pour automatiser le fichier de sauvegarde en lots. Vous pouvez le trouver soit en recherchant planificateur de tâches dans Windows, soit en allant dans Panneau de configuration > Outils d'administration > Planificateur de tâches.

6. Cliquez sur Créer une tâche de base et suivez l'assistant pour choisir un déclencheur. Si vous souhaitez effectuer une sauvegarde automatique de SQL Server chaque jour, choisissez Tous les jours.

7. Choisissez l'action Démarrer un programme et spécifiez le fichier batch comme programme. Une fois l'installation terminée, vous avez une tâche de sauvegarde automatique de SQL Express prête à s'exécuter.

La planification des sauvegardes SQL peut accumuler de nombreux fichiers de sauvegarde sur votre disque, donc si vous souhaitez distinguer ces sauvegardes quotidiennes, vous pouvez également créer des sauvegardes de base de données quotidiennes avec des noms uniques dans SQL Server.

Méthode 4. Planifier des sauvegardes automatique SQL Server avec un logiciel spécialisé

Toutes les méthodes ci-dessus peuvent sauvegarder des bases de données simples ou multiples, mais elles ne sont pas très simples et directes. Il y a aussi un risque élevé d'erreurs en raison de petites omissions lors du processus de configuration.

Pour sauvegarder automatiquement les bases de données ou les instances SQL Server sans effort, vous pouvez également essayer un logiciel de sauvegarde automatique SQL tel que AOMEI Cyber Backup. Il est doté d'une interface graphique intuitive qui vous permet de l'utiliser sans compétences particulières. Avec lui, vous pouvez simplement sauvegarder automatiquement autant de bases de données que vous le souhaitez. En plus des bases de données SQL, il prend également en charge d'autres types de données tels que les sauvegardes de machines virtuelles.

Ce logiciel fonctionne avec Windows 7 et Windows Server 2008 R2 et versions ultérieures, et vous permet de sauvegarder SQL Server 2005-2022.

Vous pouvez télécharger la version d'essai gratuite de 30 jours du logiciel de sauvegarde SQL et bénéficier d'une remise limitée :

Comment planifier une sauvegarde automatique de base de données MSSQL :

1. Lancez AOMEI Cyber Backup installé sur la machine serveur, accédez à Appareil source > Microsoft SQL > Ajouter Microsoft SQL.

Choisissez Télécharger le programme proxy (ou Copier le lien) et installez-le sur l'appareil avec SQL Server installé. Ensuite, cliquez sur Proxy installés et sélectionnez l'appareil.

Ensuite, cliquez sur  > Authentification pour valider l'instance de base de données. Vous pouvez choisir l'authentification Windows ou l'authentification SQL.

☛ Comment prendre une sauvegarde de base de données SQL Server automatiquement :

1. Cliquez sur Tâches de sauvegarde > Créer une nouvelle tâche pour accéder à la page de création de tâche. Choisissez le type de sauvegarde comme Sauvegarde Microsoft SQL.

2. Cliquez sur Sélectionner à côté de Nom d'appareil pour spécifier les instances et bases de données SQL à sauvegarder. Vous pouvez sélectionner une ou plusieurs bases de données selon vos besoins.

3. Sélectionnez la destination pour enregistrer les sauvegardes. Vous pouvez spécifier un chemin local ou un chemin réseau.

4. Avant de lancer la sauvegarde, vous pouvez également configurer une planification pour exécuter la sauvegarde de base de données SQL quotidiennement, hebdomadairement ou mensuellement, et définir la méthode de sauvegarde comme sauvegarde complète, incrémentielle ou différentielle.

5. Exécutez la tâche. Une fois terminée, vous la trouverez dans l'onglet Tâches de sauvegarde.

✍Plus de fonctionnalités utiles :

• Le nettoyage de sauvegarde vous aide à supprimer automatiquement les versions de sauvegarde plus anciennes et ainsi à économiser de l'espace de stockage.
• La notification par email vous permet de recevoir des notifications par email lorsque la tâche est anormale ou réussie.

Conclusion

Quatre méthodes sont fournies ici pour vous aider à planifier la sauvegarde SQL et la sauvegarde automatique des bases de données SQL Server pour répondre à vos besoins de récupération. Si vous n'êtes pas un expert ni familier avec les scripts Transact-SQL, alors AOMEI Cyber Backup pourrait être une solution plus facile.