Sauvegarder un journal des transactions

  • Article

S’applique à :SQL Server

Cet article explique comment sauvegarder un journal des transactions dans SQL Server à l’aide de SQL Server Management Studio, d’Azure Data Studio, de Transact-SQL ou de PowerShell.

Limites

L’instruction BACKUP n’est pas autorisée dans une transaction explicite ou implicite . Une transaction est explicite si vous définissez le début et la fin de la transaction de manière explicite.

Les sauvegardes du journal des transactions de la master base de données système ne sont pas prises en charge.

Recommandations

Si une base de données est configurée pour le mode de récupération complète ou le mode de récupération utilisant les journaux de transactions, vous devez sauvegarder le journal des transactions assez régulièrement pour protéger vos données et éviter une saturation de ce dernier. Cela tronque le journal et prend en charge la restauration de la base de données à un point précis dans le temps.

Par défaut, chaque opération de sauvegarde réussie ajoute une entrée au journal des erreurs SQL Server et au journal des événements système. Si vous sauvegardez fréquemment le journal, ces messages de réussite peuvent rapidement s’accumuler, créer des journaux d’erreurs très volumineux et compliquer la recherche d’autres messages. Dans ce cas, vous pouvez supprimer ces entrées de journal à l’aide de l’indicateur de trace 3226, si aucun de vos scripts ne dépend de ces entrées, consultez Indicateurs de trace (Transact-SQL).

Autorisations

Les autorisations BACKUP DATABASE et BACKUP LOG requises sont octroyées par défaut aux membres du rôle serveur fixe sysadmin et des rôles de base de données fixes db_owner et db_backupoperator. Veillez à avoir les autorisations appropriées avant de commencer.

Des problèmes de propriété et d'autorisations sur le fichier physique de l'unité de sauvegarde sont susceptibles de perturber une opération de sauvegarde. SQL Server doit être en mesure de lire et d'écrire sur l'unité ; le compte sous lequel le service SQL Server s'exécute doit avoir des autorisations d'écriture. Toutefois, sp_addumpdevice, qui ajoute une entrée pour une unité de sauvegarde dans les tables système, ne vérifie pas les autorisations d’accès au fichier. Les problèmes d’autorisations sur le fichier physique de l’appareil de sauvegarde ne sont pas évidents tant que vous n’avez pas tenté d’accéder à la ressource physique lorsque vous essayez de sauvegarder ou de restaurer. Là encore, vérifiez vos autorisations avant de commencer.

Utilisez SQL Server Management Studio.

Remarque

Les étapes décrites dans cette section s’appliquent également à Azure Data Studio.

  1. Après la connexion à l’instance appropriée du Moteur de base de données SQL Server, dans l’Explorateur d’objets, sélectionnez le nom du serveur pour développer l’arborescence du serveur.

  2. Développez Bases de donnéespuis, selon la base de données, sélectionnez une base de données utilisateur ou développez Bases de données système et sélectionnez une base de données système.

  3. Cliquez avec le bouton droit sur la base de données, pointez sur Tâches, puis sélectionnez Sauvegarder. La boîte de dialogue Sauvegarder la base de données s’affiche.

  4. Dans la zone de liste Base de données , vérifiez le nom de la base de données. Vous pouvez éventuellement sélectionner une autre base de données dans la liste.

  5. Vérifiez que le mode de récupération est FULL ou BULK_LOGGED.

  6. Dans la zone de liste Type de sauvegarde , sélectionnez Journal des transactions.

  7. (facultatif) Sélectionnez Copier uniquement la sauvegarde pour créer une sauvegarde de copie uniquement. Une sauvegarde de copie uniquement est une sauvegarde SQL Server indépendante de la séquence de sauvegardes SQL Server conventionnelles, consultez sauvegardes en copie seule.

    Remarque

    Lorsque l’option Différentielle est sélectionnée, vous ne pouvez pas créer une sauvegarde de copie uniquement.

  8. Acceptez le nom du jeu de sauvegarde par défaut proposé dans la zone de texte Nom , ou attribuez-lui un autre nom.

  9. (facultatif) Dans la zone de texte Description , entrez une description du jeu de sauvegarde.

  10. Indiquez quand le jeu de sauvegarde arrivera à expiration :

    • Pour que le jeu de sauvegarde expire après un nombre spécifique de jours, sélectionnez After (l’option par défaut) et entrez le nombre de jours après la création définie que le jeu expirera. Cette valeur peut être comprise entre 0 et 99999 jours ; une valeur de 0 jours signifie que le jeu de sauvegarde n’expire jamais.

      La valeur par défaut est définie dans l’option Délai de rétention par défaut du support de sauvegarde (jours) de la boîte de dialogue Propriétés du serveur (pageParamètres de base de données ). Pour accéder à cette base de données, cliquez avec le bouton droit sur le nom du serveur dans l’Explorateur d’objets et sélectionnez Propriétés. Ensuite, sélectionnez la page Paramètres de base de données .

    • Pour que le jeu de sauvegarde expire à une date spécifique, sélectionnez Activé et entrez la date à laquelle le jeu expire.

  11. Choisissez le type de destination de sauvegarde en sélectionnant Disque, URL ou Bande. Pour sélectionner les chemins d’accès d’un maximum de 64 lecteurs de disque ou de bande contenant un seul jeu de supports, sélectionnez Ajouter. Les chemins d'accès sélectionnés apparaissent dans la zone de liste Sauvegarde sur .

    Pour supprimer une destination de sauvegarde, sélectionnez-la, puis sélectionnez Supprimer. Pour afficher le contenu d’une destination de sauvegarde, sélectionnez-le et sélectionnez Contenu.

  12. Pour afficher ou sélectionner les options avancées, sélectionnez Options dans le volet Sélectionner une page.

  13. Sélectionnez une option Remplacer le média en sélectionnant l’une des options suivantes :

    • Sauvegarder sur le support de sauvegarde existant

      Pour cette option, sélectionnez Ajouter au jeu de sauvegarde existant ou remplacer tous les jeux de sauvegarde existants, consultez Media Sets, Media Families et Backup Sets (SQL Server).

      • (facultatif) Sélectionnez Vérifier le nom du jeu de supports et l’expiration du jeu de sauvegarde pour que l’opération de sauvegarde vérifie la date et l’heure à laquelle le jeu de supports et le jeu de sauvegarde expirent.

      • (facultatif) Entrez un nom dans la zone de texte Nom du jeu de supports. Si aucun nom n’est spécifié, un support de sauvegarde avec un nom vide est créé. Si vous spécifiez un nom pour le support de sauvegarde, ce support (bande ou disque) est vérifié pour voir si le nom réel correspond bien au nom que vous entrez ici.

      Si vous laissez le nom du média vide et case activée la zone pour l’case activée sur le média, la réussite est égale au nom du média sur le média également vide.

    • Sauvegarder sur un nouveau support de sauvegarde et effacer tous les jeux de sauvegarde existants

      Pour cette option, entrez un nom dans la zone de texte Nouveau nom du jeu multimédia et, éventuellement, décrivez le support dans la zone de texte Nouvelle description du jeu multimédia, consultez Jeux de supports, Familles de supports et Jeux de sauvegarde (SQL Server).

  14. Dans la section Fiabilité , vous pouvez éventuellement activer les cases à cocher :

  15. Dans la section Journal des transactions :

    • Pour les sauvegardes normales du journal, conservez la sélection par défaut, Tronquer le journal des transactions en supprimant les entrées inactives.

    • Pour sauvegarder la fin du journal (le journal actif), cochez la case Sauvegarder la fin du journal et laisser la base de données dans l’état de restauration.

      Une sauvegarde de la fin du journal est effectuée après une défaillance pour éviter de perdre des données. Sauvegardez le journal actif (sauvegarde de la fin du journal) après une défaillance, avant de commencer la restauration de la base de données ou en cas de basculement sur une base de données secondaire. Sélectionner cette option équivaut à spécifier l'option NORECOVERY dans l'instruction BACKUP LOG de Transact-SQL.

      Pour plus d’informations sur les sauvegardes de la fin du journal, consultez sauvegardes de la fin du journal (SQL Server).

  16. Si vous sauvegardez sur un lecteur de bande (comme spécifié dans la section Destination de la page Général ), le déchargement de la bande après l’option de sauvegarde est active. La sélection de cette option active le rembobinage de la bande avant le déchargement .

  17. Par défaut, si une sauvegarde est compression dépend de la valeur de l’option de configuration de serveur par défaut de compression de sauvegarde. Toutefois, quelle que soit la valeur par défaut du serveur actuel, vous pouvez compresser une sauvegarde en case activée en compressant la sauvegarde, et vous pouvez empêcher la compression en case activée ing Ne pas compresser la sauvegarde.

    La compression de sauvegarde est prise en charge sur SQL Server 2008 (10.0.x) Enterprise et versions ultérieures, et SQL Server 2016 (13.x) Standard avec Service Pack 1 et versions ultérieures.

    Pour afficher la valeur par défaut de compression de sauvegarde actuelle, consultez Afficher ou Configurer la valeur par défaut de compression de sauvegarde (option de configuration du serveur) .

    Pour chiffrer le fichier de sauvegarde, case activée la zone Chiffrer le case activée de sauvegarde. Sélectionnez l'algorithme de chiffrement à utiliser pour chiffrer le fichier de sauvegarde et fournissez un certificat ou une clé asymétrique. Les algorithmes disponibles pour le chiffrement sont :

    • AES 128
    • AES 192
    • AES 256
    • Triple DES

Utiliser Transact-SQL

Exécutez l’instruction BACKUP LOG pour sauvegarder le journal des transactions, en fournissant les informations suivantes :

  • Le nom de la base de données à laquelle appartient le journal des transactions à sauvegarder.
  • l'unité de sauvegarde où sera écrite la sauvegarde du journal des transactions.

Important

Cet exemple utilise la base de données AdventureWorks2022 , qui fait appel au mode de récupération simple. Pour autoriser les sauvegardes de fichier journal, avant d'effectuer une sauvegarde complète de base de données, la base de données a été configurée pour utiliser le mode de récupération complète.

Pour plus d’informations, consultez Afficher ou modifier le modèle de récupération d’une base de données (SQL Server).

L'exemple suivant crée une sauvegarde du journal des transactions pour la base de données AdventureWorks2022 sur l'unité de sauvegarde nommée qui a été précédemment créée, MyAdvWorks_FullRM_log1.

BACKUP LOG AdventureWorks2022
   TO MyAdvWorks_FullRM_log1;
GO

Utiliser PowerShell

Configurez et utilisez le fournisseur SQL Server PowerShell. Utilisez l’applet de commande Backup-SqlDatabase et spécifiez Log comme valeur du paramètre -BackupAction .

L'exemple suivant crée une sauvegarde de fichier journal de la base de données <myDatabase> à l'emplacement de sauvegarde par défaut de l'instance de serveur Computer\Instance.

Backup-SqlDatabase -ServerInstance Computer\Instance -Database <myDatabase> -BackupAction Log

Contenu connexe