Sauvegarder un journal des transactions (SQL Server)

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

Dans cette rubrique

• Avant de commencer :

  Limitations et restrictions

  Recommandations

  Sécurité

• Pour sauvegarder un journal des transactions, utilisez :

  SQL Server Management Studio

  Transact-SQL

  PowerShell

  [!REMARQUE]

  Vous pouvez également utiliser l'Assistant Plan de maintenance pour créer des sauvegardes.

• Tâches associées

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 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 dans le journal des erreurs SQL Server et dans le journal des événements système. Si vous sauvegardez très 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 de tels cas, vous pouvez supprimer ces entrées de journal en utilisant 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 sont définies 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. De tels problèmes pour le fichier physique de l'unité de sauvegarde peuvent n'apparaître que lorsque la ressource physique est sollicitée au moment de la sauvegarde ou de la restauration.

[Haut de la page]

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 son arborescence.

2. Développez Bases de données puis, selon le cas, 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. Sélectionnez éventuellement 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. 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 des sauvegardes SQL Server conventionnelles. Pour plus d'informations, consultez Sauvegardes de type copie seule (SQL Server).

   [!REMARQUE]

   Lorsque l'option Différentielle est sélectionnée, vous ne pouvez pas créer de 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. Dans la zone de texte Description, vous avez la possibilité de saisir 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, cliquez sur Après (option par défaut), puis entrez le nombre de jours à compter de la création du jeu au terme desquels celui-ci doit expirer. Cette valeur peut varier entre 0 et 99999 jours ; la valeur 0 indique que le jeu de sauvegarde n'expirera 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 (page Paramè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, cliquez sur Le et entrez la date d'expiration souhaitée.

11. Choisissez le type de destination de la sauvegarde : Disque ou Bande. Pour sélectionner les chemins de 64 lecteurs de bande ou disques (maximum) contenant un seul support de sauvegarde, 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-la, puis cliquez sur Sommaire.

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 support en cliquant sur un des éléments suivants :

    • Sauvegarder sur le support de sauvegarde existant

      Pour cette option, cliquez sur Ajouter au jeu de sauvegarde existant ou sur Remplacer tous les jeux de sauvegarde existants. Pour plus d'informations, consultez Jeux de supports, familles de supports et jeux de sauvegarde (SQL Server).

      Vous pouvez aussi activer la case à cocher Vérifier le nom du support de sauvegarde et la date d'expiration du jeu de sauvegarde pour forcer l'opération de sauvegarde à vérifier la date et l'heure de l'expiration du support de sauvegarde ou un jeu de sauvegarde.

      Vous pouvez éventuellement entrer un nom dans la zone de texte Nom du support de sauvegarde. 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 n'entrez pas de nom et que vous activez la case à cocher pour demander la vérification par rapport au support, le nom du support sera également vide sur le support.

    • 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 support de sauvegarde et décrivez éventuellement le support de sauvegarde dans la zone de texte Description du nouveau support de sauvegarde. Pour plus d'informations, consultez Jeux de supports, familles de supports et jeux de sauvegarde (SQL Server).

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

    • Vérifier la sauvegarde en fin d'opération.

    • Effectuer une somme de contrôle avant d'écrire sur le support et éventuellement Continuer lors d'erreurs de somme de contrôle. Pour obtenir des informations sur les sommes de contrôle, consultez Erreurs de support possibles pendant les opérations de sauvegarde et de restauration (SQL Server).

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 (autrement dit le journal actif), activez la case à cocher 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 fichiers journaux après défaillance, consultez Sauvegardes de la fin du journal (SQL Server).

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

17. SQL Server 2008 Enterprise et versions ultérieures prennent en charge la compression de la sauvegarde. Par défaut, la compression d'une sauvegarde dépend de la valeur de l'option de configuration de serveur backup-compression default. Toutefois, quelle que soit la valeur par défaut actuelle au niveau du serveur, vous pouvez compresser une sauvegarde en activant Compresser la sauvegarde, et vous pouvez empêcher la compression en activant Ne pas compresser la sauvegarde.

    Pour consulter la valeur par défaut de compression de la sauvegarde actuelle

    • Afficher ou configurer l'option de configuration du serveur valeur par défaut de compression de la sauvegarde

[Haut de la page]

Utilisation de Transact-SQL

Pour sauvegarder un journal des transactions

• Exécutez l'instruction BACKUP LOG pour sauvegarder le journal des transactions, en spécifiant :

  • 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.

Exemple (Transact-SQL)

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

BACKUP LOG AdventureWorks2012
   TO MyAdvWorks_FullRM_log1;
GO

[Haut de la page]

Utilisation de PowerShell

1. 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 MyDB à l'emplacement de sauvegarde par défaut de l'instance de serveur Computer\Instance.

   --Enter this command at the PowerShell command prompt, C:\PS>
   Backup-SqlDatabase -ServerInstance Computer\Instance -Database MyDB -BackupAction Log
   

Pour configurer et utiliser le fournisseur SQL Server PowerShell

• Fournisseur PowerShell SQL Server

[Haut de la page]

Tâches associées

• Restaurer une sauvegarde de journal des transactions (SQL Server)

• Restaurer une base de données SQL Server jusqu'à une limite dans le temps (mode de récupération complète)

• Résoudre les problèmes liés à un journal des transactions saturé (erreur SQL Server 9002)

[Haut de la page]

Voir aussi

Référence

BACKUP (Transact-SQL)

Concepts

Appliquer les sauvegardes du journal de transactions (SQL Server)

Plans de maintenance

Sauvegardes de fichiers complètes (SQL Server)