Planifier et automatiser les sauvegardes des bases de données SQL Server dans SQL Server Express
Cet article explique comment utiliser un script Transact-SQL et un planificateur de tâches Windows pour automatiser les sauvegardes des bases de données SQL Server Express selon une planification.
Version du produit d’origine : SQL Server
Numéro de base de connaissances d’origine : 2019698
Résumé
Les éditions SQL Server Express n’offrent pas de moyen de planifier des travaux ou des plans de maintenance, car le composant SQL Server Agent n’est pas inclus dans ces éditions. Par conséquent, vous devez adopter une approche différente pour sauvegarder vos bases de données lorsque vous utilisez ces éditions.
Actuellement, les utilisateurs DE SQL Server Express peuvent sauvegarder leurs bases de données à l’aide de l’une des méthodes suivantes :
Utilisez SQL Server Management Studio ou Azure Data Studio. Pour plus d’informations sur l’utilisation de ces outils pour sauvegarder une base de données, consultez les liens suivants :
Tutoriel : Sauvegarder et restaurer des bases de données à l’aide d’Azure Data Studio
Utilisez un script Transact-SQL qui utilise la famille de commandes BACKUP DATABASE. Pour plus d’informations, consultez BACKUP (Transact-SQL).
Cet article explique comment utiliser un script Transact-SQL avec le planificateur de tâches pour automatiser les sauvegardes des bases de données SQL Server Express selon une planification.
Note
Cela s’applique uniquement aux éditions express SQL Server et non à SQL Server Express LocalDB.
Comment créer une sauvegarde planifiée dans SQL Express
Vous devez suivre ces quatre étapes pour sauvegarder vos bases de données SQL Server à l’aide du Planificateur de tâches Windows :
Étape 1 : Créer une procédure stockée pour sauvegarder vos bases de données
Connectez-vous à votre instance SQL Express et créez sp_BackupDatabases une procédure stockée dans votre base de données master à l’aide du script à l’emplacement suivant :
Étape 2 : Télécharger l’utilitaire client SQLCMD
L’utilitaire sqlcmd vous permet d’entrer des instructions Transact-SQL, des procédures système et des fichiers de script. Dans SQL Server 2014 et versions antérieures, l’utilitaire est livré dans le cadre du produit. À compter de SQL Server 2016, sqlcmd l’utilitaire est proposé sous la forme d’un téléchargement distinct. Pour plus d’informations, consultez l’utilitaire sqlcmd.
Étape 3 : Créer un fichier batch à l’aide d’un éditeur de texte
Dans un éditeur de texte, créez un fichier de commandes nommé Sqlbackup.bat, puis copiez le texte à partir de l’un des exemples suivants dans ce fichier, en fonction de votre scénario :
Tous les scénarios ci-dessous s’utilisent
D:\SQLBackupscomme espace réservé. Le script doit être ajusté à l’emplacement approprié du lecteur et du dossier de sauvegarde dans votre environnement.Si vous utilisez l’authentification SQL, assurez-vous que l’accès au dossier est limité aux utilisateurs autorisés, car les mots de passe sont stockés en texte clair.
Note
Le dossier de l’exécutable SQLCMD se trouve généralement dans les variables Path du serveur une fois SQL Server installé ou après l’avoir installé en tant qu’outil autonome. Toutefois, si la variable Path ne répertorie pas ce dossier, vous pouvez ajouter son emplacement à la variable Path ou spécifier le chemin d’accès complet à l’utilitaire.
Exemple 1 : sauvegardes complètes de toutes les bases de données dans l’instance nommée locale de SQLEXPRESS à l’aide de l’authentification Windows
// Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'"
Exemple 2 : Sauvegardes différentielles de toutes les bases de données dans l’instance nommée locale de SQLEXPRESS à l’aide d’un SQLLogin et de son mot de passe
// Sqlbackup.bat
sqlcmd -U <YourSQLLogin> -P <StrongPassword> -S .\SQLEXPRESS -Q "EXEC sp_BackupDatabases @backupLocation ='D:\SQLBackups', @BackupType='D'"
Note
SQLLogin doit avoir au moins le rôle Opérateur de sauvegarde dans SQL Server.
Exemple 3 : Sauvegardes de journal de toutes les bases de données dans l’instance nommée locale de SQLEXPRESS à l’aide de l’authentification Windows
// Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\',@backupType='L'"
Exemple 4 : Sauvegardes complètes de la base de données USERDB dans l’instance nommée locale de SQLEXPRESS à l’aide de l’authentification Windows
// Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @databaseName='USERDB', @backupType='F'"
De même, vous pouvez effectuer une sauvegarde différentielle de USERDB en collant « D » pour le paramètre @backupType et une sauvegarde de journal de USERDB en collant « L » pour le paramètre @backupType .
Étape 4 : Planifier un travail à l’aide du Planificateur de tâches Windows pour exécuter le fichier batch que vous avez créé à l’étape 2
Effectuez les étapes suivantes :
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 le nom de la nouvelle tâche (par exemple, SQLBackup) et 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 et sélectionnez Suivant.
Sélectionnez Démarrer un programme comme action, puis sélectionnez Suivant.
Sélectionnez Parcourir, sélectionnez le fichier de commandes que vous avez créé à l’étape 3, puis sélectionnez Ouvrir.
Cochez la case Ouvrir la boîte de dialogue Propriétés de cette tâche lorsque je clique sur Terminer .
Sous l’onglet Général :
Passez en revue les options de sécurité et vérifiez les éléments suivants pour le compte d’utilisateur exécutant la tâche (répertorié sous Lors de l’exécution de la tâche, utilisateur du compte d’utilisateur suivant :)
Le compte doit disposer au moins des autorisations Lecture et Exécution pour lancer l’utilitaire
sqlcmd. En outre :Si vous utilisez l’authentification Windows dans le fichier batch, assurez-vous que le propriétaire de la tâche est autorisé à effectuer des sauvegardes SQL.
Si vous utilisez l’authentification SQL dans le fichier batch, l’utilisateur SQL doit disposer des autorisations nécessaires pour effectuer des sauvegardes SQL.
Ajustez d’autres paramètres en fonction de vos besoins.
Conseil
En guise de test, exécutez le fichier de commandes à partir de l’étape 3 à partir d’une invite de commandes qui a démarré avec le même compte d’utilisateur propriétaire de la tâche.
Configuration requise
Tenez compte des exigences suivantes lorsque vous utilisez la procédure décrite dans cet article :
Le service Planificateur de tâches doit s’exécuter au moment où le travail est planifié pour s’exécuter. Nous vous recommandons de définir le type de démarrage de ce service comme automatique. Cela permet de s’assurer que le service s’exécute même lors d’un redémarrage.
Vous devez créer suffisamment d’espace sur le lecteur où les sauvegardes sont écrites. Nous vous recommandons de nettoyer régulièrement les anciens fichiers dans le dossier de sauvegarde pour vous assurer que vous n’avez pas d’espace disque insuffisant. Le script ne contient pas la logique pour nettoyer les anciens fichiers.