Partager via

Sauvegarder un journal des transactions (SQL Server)

Cette rubrique explique comment sauvegarder un journal des transactions dans SQL Server 2014 à l’aide de SQL Server Management Studio, Transact-SQL ou PowerShell.

Dans cette rubrique

Avant de commencer

Limitations et restrictions

  • L'instruction BACKUP n'est pas autorisée dans une transaction explicite ou implicite.

Recommandations

  • Si une base de données utilise le modèle de récupération complète ou journalisée en bloc, vous devez sauvegarder régulièrement le journal des transactions pour protéger vos données et empêcher le remplissage du journal des transactions. Cela tronque le journal et prend en charge la restauration de la base de données à un point spécifique 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 le journal très fréquemment, ces messages de réussite s’accumulent rapidement, ce qui entraîne des journaux d’erreurs énormes qui peuvent rendre les autres messages difficiles. 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. Pour plus d’informations, consultez Indicateurs de trace (Transact-SQL).

Sécurité

Autorisations

Les autorisations BACKUP DATABASE et BACKUP LOG reviennent 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 .

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. Ces problèmes sur le fichier physique de l’appareil de sauvegarde peuvent ne pas apparaître tant que la ressource physique n’est pas accessible lorsque la sauvegarde ou la restauration est tentée.

Utilisation de SQL Server Management Studio

Pour sauvegarder un journal des transactions

  1. Après vous être connecté à l’instance appropriée du moteur de base de données SQL Server, dans l’Explorateur d’objets, cliquez sur 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 cliquez sur 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 modèle de récupération est FULL ou BULK_LOGGED.

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

  7. Vous pouvez si vous le souhaitez sélectionner Sauvegarde de copie uniquement 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. Pour plus d’informations, consultez Copy-Only Sauvegardes (SQL Server).

    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. Si vous le souhaitez, dans la zone de texte Description, entrez une description du jeu de sauvegarde.

  10. Spécifiez quand le jeu de sauvegarde expirera :

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

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

    • Pour que le jeu de sauvegarde expire à une date spécifique, cliquez sur le bouton "On", puis entrez la date à laquelle le jeu expirera.

  11. Choisissez le type de destination de sauvegarde en cliquant sur 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 support, cliquez sur 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 cliquez sur Supprimer. Pour afficher le contenu d’une destination de sauvegarde, sélectionnez-le, puis cliquez sur Contenu.

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

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

    • Sauvegarder sur l'ensemble de supports existant

      Pour cette option, cliquez sur Ajouter au jeu de sauvegarde existant ou remplacer tous les jeux de sauvegarde existants. Pour plus d’informations, consultez Media Sets, Media Families et Backup Sets (SQL Server).

      Si vous le souhaitez, sélectionnez Vérifier le nom du support multimédia 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.

      Si vous le souhaitez, entrez un nom dans la zone de texte Nom du jeu de supports . Si aucun nom n’est spécifié, un jeu de médias avec un nom vide est créé. Si vous spécifiez un nom de jeu de supports média, le média (bande ou disque) est vérifié pour s'assurer que le nom réel correspond au nom que vous entrez ici.

      Si vous laissez le nom du média vide et cochez la case pour le comparer au média, la réussite consiste à ce que le nom du média sur ce média soit également vide.

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

      Pour cette option, entrez un nom dans la zone de texte Nouveau nom du jeu de médias et, si nécessaire, décrivez le jeu de médias dans la zone de texte Nouvelle description du jeu de médias. Pour plus d’informations, consultez Media Sets, Media Families et Backup Sets (SQL Server).

  14. Dans la section Fiabilité , vérifiez éventuellement :

  15. Dans la section Journal des transactions :

    • Pour les sauvegardes de journaux de routine, conservez la sélection par défaut, tronquez le journal des transactions en supprimant les entrées inactives.

    • Pour sauvegarder la fin du journal (autrement dit, le journal actif), vérifiez sauvegarder la fin du journal et laissez la base de données dans l’état de restauration.

      Une sauvegarde de la fin du journal est effectuée après un échec de sauvegarde de la fin du journal afin d’éviter toute perte de travail. Sauvegardez le journal actif (sauvegarde du journal de fin) soit après une défaillance, soit avant de commencer la restauration de la base de données, ou lors du basculement vers une base de données secondaire. La sélection de cette option équivaut à spécifier l’option NORECOVERY dans l’instruction BACKUP LOG de Transact-SQL. Pour plus d’informations sur les sauvegardes de fin de journal, consultez Tail-Log Sauvegardes (SQL Server).

  16. Si vous sauvegardez sur un lecteur de bande (comme spécifié dans la section Destination de la page Général), l'option Décharger la bande après la sauvegarde est active. Cliquez sur cette option pour activer l’option Rembobiner la bande avant déchargement.

  17. SQL Server 2008 Enterprise et versions ultérieures prennent en charge la compression de sauvegarde. Par défaut, si une sauvegarde est compressée 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 vérifiant compresser la sauvegarde et en empêchant la compression en vérifiant Ne pas compresser la sauvegarde.

    Pour afficher la compression de sauvegarde actuelle par défaut

Chiffrement

Pour chiffrer le fichier de sauvegarde, cochez la case Chiffrer la sauvegarde . Sélectionnez un 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 les suivants :

  • AES 128

  • AES 192

  • AES 256

  • Triple DES

Utilisation de Transact-SQL

Pour sauvegarder un journal des transactions

  1. Exécutez l’instruction BACKUP LOG pour sauvegarder le journal des transactions, en spécifiant les éléments suivants :

    • Nom de la base de données à laquelle appartient le journal des transactions que vous souhaitez sauvegarder.

    • Le périphérique de sauvegarde où est écrite la sauvegarde du journal des transactions.

Exemple (Transact-SQL)

Important

Cet exemple utilise la base de données AdventureWorks2012 , qui utilise le modèle de récupération simple. Pour autoriser les sauvegardes de journaux, avant d’effectuer une sauvegarde complète de base de données, la base de données a été définie pour utiliser le modèle 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).

Cet exemple crée une sauvegarde du journal des transactions pour la base de données AdventureWorks2012 sur l’appareil de sauvegarde nommé précédemment créé. MyAdvWorks_FullRM_log1

BACKUP LOG AdventureWorks2012  
   TO MyAdvWorks_FullRM_log1;  
GO

Utilisation de PowerShell

Utilisez l’applet Backup-SqlDatabase de commande et spécifiez Log la valeur du -BackupAction paramètre.

L’exemple suivant crée une sauvegarde de journal de la MyDB base de données à l’emplacement de sauvegarde par défaut de l’instance Computer\Instancede serveur.

```powershell
Backup-SqlDatabase -ServerInstance Computer\Instance -Database MyDB -BackupAction Log  
```

Pour configurer et utiliser le fournisseur SQL Server PowerShell, consultez le fournisseur SQL Server PowerShell.

Tâches associées

Voir aussi

BACKUP (Transact-SQL)
Appliquer des sauvegardes de journal des transactions (SQL Server)
Plans de maintenance
Sauvegardes complètes de fichiers (SQL Server)

  • Last updated on