Planifier et automatiser les sauvegardes de bases de données Express SQL Server

Version du produit d’origine : SQL Server
Numéro de base de connaissances d’origine : 2019698

Résumé

SQL Server Express n'inclut pas SQL Server Agent. Vous ne pouvez donc pas planifier les travaux de sauvegarde ou les plans de maintenance de la façon dont vous pouvez utiliser d'autres éditions SQL Server. Pour automatiser les sauvegardes sur SQL Server Express, combinez trois composants : la procédure stockée, l’utilitaire sp_BackupDatabasessqlcmd de ligne de commande et Windows Planificateur de tâches. Cet article présente les étapes à suivre pour configurer des sauvegardes planifiées complètes, différentielles et du journal des transactions pour des bases de données SQL Server Express. Il explique également comment confirmer qu’une sauvegarde planifiée a été exécutée.

Cet article s’applique uniquement aux éditions SQL Server Express. Elle ne s'applique pas à SQL Server Express LocalDB.

Pourquoi SQL Server Express ne peut pas planifier de sauvegardes avec SQL Server Agent

SQL Server Agent est le composant qui exécute des travaux planifiés et des plans de maintenance, et il n'est pas inclus dans SQL Server Express. Pour obtenir une comparaison complète de ce que chaque édition inclut, consultez éditions et fonctionnalités prises en charge de SQL Server.

Sans SQL Server Agent, vous pouvez toujours sauvegarder une base de données Express SQL Server à la demande à l’aide de l’un des outils suivants :

Utilitaire sqlcmd
SQL Server Management Studio (SSMS)
extension MSSQL pour Visual Studio Code
Script Transact-SQL qui utilise la famille de commandes BACKUP (Transact-SQL)

Pour exécuter ces sauvegardes selon une planification au lieu de la demande, utilisez Windows Planificateur de tâches comme moteur de planification, comme décrit dans les étapes suivantes. Pour plus d’informations sur les types de sauvegarde et la stratégie, consultez Sauvegarde et restauration de bases de données SQL Server.

Prerequisites

Avant de commencer, vérifiez que vous disposez des points suivants :

Instance en cours d’exécution de SQL Server Express. Les exemples de cet article utilisent l’instance nommée par défaut. .\SQLEXPRESS
Appartenance au rôle serveur fixe sysadmin sur cette instance, qui est nécessaire pour créer une procédure stockée dans la master base de données.
Un dossier local ou un lecteur avec suffisamment d’espace libre pour contenir les fichiers de sauvegarde. Les exemples utilisent D:\SQLBackups.
Autorisation de créer des tâches planifiées sur l’ordinateur qui exécute SQL Server Express.

Étape 1 : Créer la procédure stockée sp_BackupDatabases

sp_BackupDatabasesest une procédure stockée fournie Microsoft qui exécute la commande appropriée BACKUP sur une base de données ou chaque base de données en ligne sur l’instance. Vous le créez une fois dans la master base de données.

Téléchargez le script à partir de SQL_Express_Backups.sql et enregistrez-le localement, par exemple en tant que C :\Temp\SQL_Express_Backups.sql. Créez ensuite la procédure stockée à l’aide de SSMS ou sqlcmd.

Créer la procédure stockée à l’aide de SSMS

Ouvrez SSMS et connectez-vous à votre instance express SQL Server. Dans le nom du serveur, entrez .\SQLEXPRESS, puis sélectionnez Se connecter.
Sélectionnez Fichier>Ouvrir>Fichier, sélectionnez le fichier SQL_Express_Backups.sql que vous avez enregistré, puis sélectionnez Ouvrir.
Vérifiez que la liste de bases de données dans la barre d’outils s’affiche master. Le script commence par USE [master]: il cible automatiquement la base de données correcte.
Sélectionnez Exécuter (ou appuyez sur F5). Le volet Messages signale Commands completed successfully.

Créer la procédure stockée à l’aide de sqlcmd

Exécutez la commande suivante depuis une invite de commandes :

sqlcmd -S .\SQLEXPRESS -E -i "C:\Temp\SQL_Express_Backups.sql"

Vérifiez que la procédure stockée a été créée

Exécutez la requête suivante sur l’instance. Elle retourne une ligne si la procédure stockée existe :

SELECT name, create_date
FROM master.sys.procedures
WHERE name = 'sp_BackupDatabases';

paramètres de sp_BackupDatabases

La procédure stockée accepte les paramètres suivants :

Paramètre	Required	Description
`@backupLocation`	Yes	Dossier qui reçoit les fichiers de sauvegarde, par exemple `D:\SQLBackups\`. Le dossier doit déjà exister.
`@backupType`	Yes	Type de sauvegarde : `F` pour complet, `D` pour différentiel ou `L` pour le journal des transactions.
`@databaseName`	Non	La base de données à sauvegarder. Si vous omettez ce paramètre, la procédure stockée sauvegarde chaque base de données en ligne sur l’instance, à l’exception des bases de données que le script exclut.

La procédure stockée n’effectue jamais de sauvegarde de tempdb. Il ignore également les exemples de bases de données Northwind, pubset AdventureWorks pour chaque type de sauvegarde, et ignore les master sauvegardes différentielles et de journaux, car ces types de sauvegarde ne sont pas pris en charge pour la master base de données.

Important

Les noms de base de données exclus sont codés en dur dans le script. Si vous avez une base de données qui utilise l’un de ces noms, la procédure stockée l’ignore sans signaler d’erreur. Pour sauvegarder une telle base de données, modifiez les listes DELETE @DBs WHERE DBNAME IN (...) dans le script avant de créer la procédure stockée.

Étape 2 : Installer l’utilitaire sqlcmd

L’utilitaire sqlcmd vous permet d’exécuter des instructions Transact-SQL, des procédures stockées système et des fichiers de script à partir de la ligne de commande. Il est installé avec SSMS, et il est également disponible en tant que téléchargement autonome pour les ordinateurs qui n’ont pas SSMS. Pour l’installer, consultez Télécharger et installer l’utilitaire sqlcmd.

Pour confirmer que sqlcmd est installé et disponible, exécutez sqlcmd -? à partir d’une invite de commandes.

Le dossier qui contient l’exécutable sqlcmd est généralement ajouté à la Path variable d’environnement lorsque vous installez SQL Server ou les outils autonomes. Si sqlcmd -? indique que la commande n’est pas reconnue, ajoutez le dossier à la variable Path ou spécifiez le chemin d’accès complet de l’utilitaire dans votre fichier de commandes.

Étape 3 : Créer le fichier batch Sqlbackup.bat

Dans un éditeur de texte, créez un fichier de commandes nommé Sqlbackup.bat. Copiez le texte de l’un des exemples suivants dans ce fichier, en fonction de votre scénario.

Tenez compte des points suivants avant de choisir un exemple :

Chaque exemple utilise D:\SQLBackups comme espace réservé. Modifiez le chemin d’accès au lecteur et au dossier de sauvegarde que vous souhaitez utiliser dans votre environnement et vérifiez que le dossier existe.
Si vous utilisez SQL Server Authentification, le mot de passe est stocké en texte clair dans le fichier batch. Limitez l’accès au dossier qui contient le fichier batch aux utilisateurs autorisés uniquement.

Exemple 1 : sauvegardes complètes de toutes les bases de données à l’aide de l’authentification Windows

REM Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'"

Exemple 2 : sauvegardes différentielles de toutes les bases de données à l’aide de l’authentification SQL Server

REM Sqlbackup.bat
sqlcmd -U <YourSQLLogin> -P <StrongPassword> -S .\SQLEXPRESS -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='D'"

Note

Pour exécuter des sauvegardes, la connexion doit être membre du rôle de base de données fixe db_backupoperator ou du db_owner dans chaque base de données que vous sauvegardez, ou du rôle serveur fixe sysadmin. Pour plus d’informations, consultez Rôles au niveau de la base de données.

Exemple 3 : sauvegardes du journal des transactions de toutes les bases de données à l’aide de l’authentification Windows

REM Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='L'"

Les sauvegardes du journal des transactions nécessitent le modèle de récupération complète ou journalisée en bloc et nécessitent au moins une sauvegarde complète précédente. Pour plus d’informations, consultez modèles de récupération (SQL Server).

Exemple 4 : Sauvegarde complète d’une base de données unique à l’aide de l’authentification Windows

REM Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @databaseName='USERDB', @backupType='F'"

Pour effectuer une sauvegarde différentielle de USERDB, remplacez le paramètre Dpar @backupType . Pour effectuer une sauvegarde du journal des transactions, remplacez-la par L.

L’option -b fait quitter sqlcmd et retourne une valeur DOS ERRORLEVEL de 1 lorsqu’une erreur SQL Server présente un niveau de gravité supérieur à 10. Sans -b, sqlcmd retourne 0 même en cas d’échec de la sauvegarde, et le Planificateur de tâches signale la tâche comme ayant réussi.

Avant de continuer, exécutez Sqlbackup.bat manuellement à partir d’une invite de commandes et vérifiez que les fichiers de sauvegarde apparaissent dans le dossier de sauvegarde. La résolution des erreurs est désormais plus facile que de les diagnostiquer ultérieurement via le Planificateur de tâches.

Étape 4 : Planifier le fichier de commandes dans le Planificateur de tâches Windows

Procédez comme suit pour exécuter Sqlbackup.bat selon une planification :

Sur l’ordinateur exécutant SQL Server Express, sélectionnez Démarrer et taper le Planificateur de tâches dans la zone de texte.
Sous Meilleure correspondance, sélectionnez Planificateur de tâches pour le lancer.
Dans le Planificateur de tâches, cliquez avec le bouton droit sur Planificateur de tâches (local) et sélectionnez Créer une tâche de base.
Entrez un nom pour la nouvelle tâche (par exemple , SQLBackup), puis sélectionnez Suivant.
Sélectionnez Quotidien pour le déclencheur de tâche, puis sélectionnez Suivant.
Définissez la périodicité sur un jour, puis sélectionnez Suivant.
Sélectionnez Démarrer un programme comme action, puis sélectionnez Suivant.
Sélectionnez Parcourir, sélectionnez le fichier Sqlbackup.bat que vous avez créé à l’étape 3 : Créez le fichier de lot Sqlbackup.bat, puis sélectionnez Ouvrir.
Sélectionnez la case à cocher Ouvrir la boîte de dialogue Propriétés de cette tâche lorsque je clique sur Terminer, puis sélectionnez Terminer.
Sous l’onglet Général , passez en revue les options de sécurité et confirmez ce qui suit pour le compte répertorié sous Lors de l’exécution de la tâche, utilisez le compte d’utilisateur suivant :
- Le compte dispose au moins des autorisations Lecture et Exécution pour exécuter l’utilitaire sqlcmd .
- Si le fichier batch utilise Windows Authentication, le compte est autorisé à sauvegarder les bases de données SQL Server.
- Si le fichier batch utilise l’authentification SQL Server, la connexion SQL Server dans le fichier batch est autorisée à sauvegarder les bases de données.
Ajustez les paramètres restants en fonction de vos besoins, puis sélectionnez OK.

Conseil

En tant que test, exécutez Sqlbackup.bat à partir d’une invite de commandes que vous avez démarrée avec le même compte d’utilisateur propriétaire de la tâche. Cette étape confirme que le compte dispose des autorisations dont il a besoin avant l’exécution de la planification.

Pour plus d’informations sur les options de planification, consultez Planificateur de tâches.

Vérifiez que la sauvegarde planifiée s’est exécutée

Après la première exécution planifiée, vérifiez que la sauvegarde a réussi :

Vérifiez le dossier de sauvegarde des nouveaux fichiers .bak (sauvegardes complètes et différentielles) ou des fichiers .trn (sauvegardes du journal des transactions). La procédure stockée ajoute un horodatage de date et d’heure à chaque nom de fichier.
Dans le Planificateur de tâches, sélectionnez Bibliothèque du planificateur de tâches, sélectionnez votre tâche et passez en revue la colonne Résultat de la dernière exécution . Cette colonne affiche le code de sortie retourné par le fichier batch. Étant donné que les exemples utilisent l’option -b , une valeur de 0x1 signifie que la commande de sauvegarde a échoué. Pour d’autres valeurs, consultez les constantes d’erreur et de réussite du Planificateur de tâches.
Interrogez l’historique de sauvegarde sur l’instance :
```
SELECT database_name, type, backup_start_date, backup_finish_date, physical_device_name
FROM msdb.dbo.backupset AS bs
INNER JOIN msdb.dbo.backupmediafamily AS bmf
    ON bs.media_set_id = bmf.media_set_id
ORDER BY backup_start_date DESC;
```
Ces tables font partie de l’historique de sauvegarde que SQL Server gère dans la msdb base de données. Pour plus d’informations, consultez l’historique des sauvegardes et les informations d’en-tête (SQL Server).