Sauvegarder un journal des transactions (SQL Server)Back Up a Transaction Log (SQL Server)

S’APPLIQUE À : ouiSQL Server nonAzure SQL Database nonAzure SQL Data Warehouse nonParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Cette rubrique explique comment sauvegarder un journal des transactions dans SQL Server 2017SQL Server 2017 à l'aide de SQL Server Management StudioSQL Server Management Studio, de Transact-SQLTransact-SQLou de PowerShell.This topic describes how to back up a transaction log in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQL, or PowerShell.

Limitations et restrictionsLimitations and restrictions

  • L’instruction BACKUP n’est pas autorisée dans une transaction explicite ou implicite .The BACKUP statement is not allowed in an explicit or implicit transaction. Une transaction est explicite si vous définissez le début et la fin de la transaction de manière explicite.An explicit transaction is one in which you explicitly define both the start and end of the transaction.

RecommandationsRecommendations

  • 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.If a database uses either the full or bulk-logged recovery model, you must back up the transaction log regularly enough to protect your data, and to prevent the transaction log from filling. Cela tronque le journal et prend en charge la restauration de la base de données à un point précis dans le temps.This truncates the log and supports restoring the database to a specific point in time.

  • Par défaut, chaque opération de sauvegarde réussie ajoute une entrée au journal des erreurs SQL ServerSQL Server et au journal des événements système.By default, every successful backup operation adds an entry in the SQL ServerSQL Server error log and in the system event log. 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.If you back up the log frequently, these success messages accumulate quickly, resulting in huge error logs, making finding other messages difficult. 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.In such cases you can suppress these log entries by using trace flag 3226, if none of your scripts depend on those entries. Pour plus d’informations, consultez Indicateurs de trace (Transact-SQL).For more information, see Trace Flags (Transact-SQL).

AutorisationsPermissions

Vérifiez que vous disposez des autorisations appropriées avant de commencer !Check for the correct permissions before you begin!

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 .The BACKUP DATABASE and BACKUP LOG permissions needed are granted by default to members of the sysadmin fixed server role, and the db_owner and db_backupoperator fixed database roles.

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.Ownership and permission problems on the backup device's physical file can interfere with a backup operation. SQL ServerSQL Server doit être en mesure de lire et d'écrire sur l'unité ; le compte sous lequel le service SQL ServerSQL Server s'exécute doit avoir des autorisations d'écriture.must be able to read and write to the device; the account under which the SQL ServerSQL Server service runs must have write permissions. 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.However, sp_addumpdevice, which adds an entry for a backup device in the system tables, does not check file access permissions. Les problèmes relatifs aux autorisations portant sur le fichier physique de l’unité de sauvegarde peuvent n’apparaître clairement qu’une fois que vous tentez d’accéder à la ressource physique lors d’une sauvegarde ou d’une restauration.Permissions problems on the backup device's physical file may not become obvious to you until you attemt to access the physical resource when you try to backup or restore. Là encore, vérifiez vos autorisations avant de commencer !So again, check permissions before you begin!

Nous sommes à votre écoute : Si vous trouvez des informations obsolètes ou incorrectes dans cet article, par exemple une étape ou une erreur dans un code, n’hésitez pas à nous en faire part.We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. Vous pouvez cliquer sur le bouton Cette page dans la section Commentaires au bas de cette page.You can click the This page button in the Feedback section at the bottom of this page. Nous lisons chaque commentaire concernant SQL, généralement le jour suivant.We read every item of feedback about SQL, typically the next day. Nous vous remercions.Thanks.

Sauvegarder à l’aide de SSMSBack up using SSMS

  1. Après vous être connecté à l'instance appropriée du Moteur de base de données SQL ServerSQL Server Database Engine, dans l'Explorateur d'objets, cliquez sur le nom du serveur pour développer son arborescence.After connecting to the appropriate instance of the Moteur de base de données SQL ServerSQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

  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.Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.

  3. Cliquez avec le bouton droit sur la base de données, pointez sur Tâches, puis cliquez sur Sauvegarder.Right-click the database, point to Tasks, and then click Back Up. La boîte de dialogue Sauvegarder la base de données s'affiche.The Back Up Database dialog box appears.

  4. Dans la zone de liste Base de données , vérifiez le nom de la base de données.In the Database list box, verify the database name. Vous pouvez éventuellement sélectionner une autre base de données dans la liste.You can optionally select a different database from the list.

  5. Vérifiez que le mode de récupération est FULL ou BULK_LOGGED.Verify that the recovery model is either FULL or BULK_LOGGED.

  6. Dans la zone de liste Type de sauvegarde , sélectionnez Journal des transactions.In the Backup type list box, select Transaction Log.

  7. Vous pouvez si vous le souhaitez sélectionner Sauvegarde de copie uniquement pour créer une sauvegarde de copie uniquement.Optionally, you can select Copy Only Backup to create a copy-only backup. Une sauvegarde de copie uniquement est une sauvegarde SQL ServerSQL Server indépendante du mécanisme des sauvegardes SQL ServerSQL Server conventionnelles.A copy-only backup is a SQL ServerSQL Server backup that is independent of the sequence of conventional SQL ServerSQL Server backups. Pour plus d’informations, consultez Sauvegardes de copie uniquement (SQL Server).For more information, see Copy-Only Backups (SQL Server).

    Notes

    Quand l’option Différentielle est sélectionnée, vous ne pouvez pas créer de sauvegarde de copie uniquement.When the Differential option is selected, you cannot create a copy-only backup.

  8. Acceptez le nom du jeu de sauvegarde par défaut proposé dans la zone de texte Nom , ou attribuez-lui un autre nom.Either accept the default backup set name suggested in the Name text box, or enter a different name for the backup set.

  9. Dans la zone de texte Description , vous avez la possibilité de saisir une description du jeu de sauvegarde.Optionally, in the Description text box, enter a description of the backup set.

  10. Indiquez quand le jeu de sauvegarde arrivera à expiration :Specify when the backup set will expire:

    • Pour que le jeu de sauvegarde expire au bout d’un nombre de jours spécifique, cliquez sur Après (option par défaut) et entrez le nombre de jours souhaité pour l’expiration du jeu après sa création.To have the backup set expire after a specific number of days, click After (the default option), and enter the number of days after set creation that the set will expire. Cette valeur doit être comprise entre 0 et 99999 jours ; une valeur de 0 jour signifie que le jeu de sauvegarde n'expirera jamais.This value can be from 0 to 99999 days; a value of 0 days means that the backup set will never expire.

      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 ).The default value is set in the Default backup media retention (in days) option of the Server Properties dialog box (Database Settings page). 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 .To access this dialog box, right-click the server name in Object Explorer and select properties; then select the Database Settings page.

    • Pour que le jeu de sauvegarde expire à une date spécifique, cliquez sur Leet entrez la date d'expiration souhaitée.To have the backup set expire on a specific date, click On, and enter the date on which the set will expire.

  11. Choisissez le type de destination de la sauvegarde en cliquant sur Disque, URL ou Bande.Choose the type of backup destination by clicking Disk, URL or Tape. Pour sélectionner les chemins d'accès à 64 lecteurs de bande ou de disque au maximum contenant un seul support de sauvegarde, cliquez sur Ajouter.To select the paths of up to 64 disk or tape drives containing a single media set, click Add. Les chemins d'accès sélectionnés apparaissent dans la zone de liste Sauvegarde sur .The selected paths are displayed in the Backup to list box.

    Pour supprimer une destination de sauvegarde, sélectionnez-la, puis cliquez sur Supprimer.To remove a backup destination, select it and click Remove. Pour afficher le contenu d'une destination de sauvegarde, sélectionnez-la, puis cliquez sur Sommaire.To view the contents of a backup destination, select it and click Contents.

  12. Pour afficher ou sélectionner les options avancées, cliquez sur Options dans le volet Sélectionner une page .To view or select the advanced options, click Options in the Select a page pane.

  13. Sélectionnez une option Remplacer le support en cliquant sur un des éléments suivants :Select an Overwrite Media option, by clicking one of the following:

    • Sauvegarder sur le support de sauvegarde existantBack up to the existing media set

      Pour cette option, cliquez sur Ajouter au jeu de sauvegarde existant ou sur Remplacer tous les jeux de sauvegarde existants.For this option, click either Append to the existing backup set or Overwrite all existing backup sets. Pour plus d’informations, consultez Jeux de supports, familles de supports et jeux de sauvegarde (SQL Server).For more information, see Media Sets, Media Families, and Backup Sets (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 jeu de supports ou du jeu de sauvegarde.Optionally, select Check media set name and backup set expiration to cause the backup operation to verify the date and time at which the media set and backup set expire.

      Vous pouvez éventuellement entrer un nom dans la zone de texte Nom du support de sauvegarde .Optionally, enter a name in the Media set name text box. Si aucun nom n'est spécifié, un support de sauvegarde avec un nom vide est créé.If no name is specified, a media set with a blank name is created. 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.If you specify a media set name, the media (tape or disk) is checked to see whether the actual name matches the name you enter here.

      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.If you leave the media name blank and check the box to check it against the media, success will equal the media name on the media also being blank.

    • Sauvegarder sur un nouveau support de sauvegarde et effacer tous les jeux de sauvegarde existantsBack up to a new media set, and erase all existing backup sets

      Pour cette option, entrez un nom dans la zone de texte Nouveau nom du support de sauvegarde et décrivez éventuellement le jeu de supports dans la zone de texte Description du nouveau support de sauvegarde .For this option, enter a name in the New media set name text box, and, optionally, describe the media set in the New media set description text box. Pour plus d’informations, consultez Jeux de supports, familles de supports et jeux de sauvegarde (SQL Server).For more information, see Media Sets, Media Families, and Backup Sets (SQL Server).

  14. Dans la section Fiabilité , vous pouvez éventuellement activer les cases à cocher :In the Reliability section, optionally, check:

  15. Dans la section Journal des transactions :In the Transaction log section:

    • Pour les sauvegardes normales du journal, conservez la sélection par défaut, Tronquer le journal des transactions en supprimant les entrées inactives.For routine log backups, keep the default selection, Truncate the transaction log by removing inactive entries.

    • Pour sauvegarder la fin du journal (autrement dit le journal actif), cochez la case Sauvegarder la fin du journal et laisser la base de données dans l’état de restauration.To back up the tail of the log (that is, the active log), check Back up the tail of the log, and leave database in the restoring state.

      Une sauvegarde de la fin du journal est effectuée après une défaillance pour éviter de perdre des données.A tail-log backup is taken after a failure to back up the tail of the log in order to prevent work loss. 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.Back up the active log (a tail-log backup) both after a failure, before beginning to restore the database, or when failing over to a secondary database. Sélectionner cette option équivaut à spécifier l'option NORECOVERY dans l'instruction BACKUP LOG de Transact-SQL.Selecting this option is equivalent to specifying the NORECOVERY option in the BACKUP LOG statement of Transact-SQL. Pour plus d’informations sur les sauvegardes de la fin du journal, consultez Sauvegardes de la fin du journal (SQL Server).For more information about tail-log backups, see Tail-Log Backups (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.If you are backing up to a tape drive (as specified in the Destination section of the General page), the Unload the tape after backup option is active. Vous pouvez cliquer sur cette option pour activer l'option Rembobiner la bande avant de décharger .Clicking this option activates the Rewind the tape before unloading option.

  17. SQL Server 2008 EnterpriseSQL Server 2008 Enterprise et versions ultérieures prennent en charge la compression de la sauvegarde.and later supports backup compression. Par défaut, la compression d’une sauvegarde dépend de la valeur de l’option de configuration de serveur Compression par défaut des sauvegardes .By default, whether a backup is compressed depends on the value of the backup-compression default server configuration option. 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.However, regardless of the current server-level default, you can compress a backup by checking Compress backup, and you can prevent compression by checking Do not compress backup.

    Pour consulter la valeur par défaut de compression de la sauvegarde actuelleTo view the current backup compression default

ChiffrementEncryption

Pour chiffrer le fichier de sauvegarde, activez la case à cocher Chiffrer le fichier de sauvegarde .To encrypt the backup file check the Encrypt backup check box. Sélectionnez l'algorithme de chiffrement à utiliser pour chiffrer le fichier de sauvegarde et fournissez un certificat ou une clé asymétrique.Select an encryption algorithm to use for encrypting the backup file and provide a Certificate or Asymmetric key. Les algorithmes disponibles pour le chiffrement sont :The available algorithms for encryption are:

  • AES 128AES 128

  • AES 192AES 192

  • AES 256AES 256

  • Triple DESTriple DES

Sauvegarder à l’aide de T-SQLBack up using T-SQL

  1. Exécutez l'instruction BACKUP LOG pour sauvegarder le journal des transactions, en spécifiant :Execute the BACKUP LOG statement to back up the transaction log, specifying the following:

    • Le nom de la base de données à laquelle appartient le journal des transactions à sauvegarder.The name of the database to which the transaction log that you want to back up belongs.

    • l'unité de sauvegarde où sera écrite la sauvegarde du journal des transactions.The backup device where the transaction log backup is written.

Exemple (Transact-SQL)Example (Transact-SQL)

IMPORTANT !IMPORTANT!!! Cet exemple utilise la base de données AdventureWorks2012AdventureWorks2012 , qui fait appel au mode de récupération simple.This example uses the AdventureWorks2012AdventureWorks2012 database, which uses the simple recovery model. 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.To permit log backups, before taking a full database backup, the database was set to use the full recovery model. Pour plus d’informations, consultez Afficher ou modifier le mode de récupération d’une base de données (SQL Server).For more information, see View or Change the Recovery Model of a Database (SQL Server).

L'exemple suivant crée une sauvegarde du journal des transactions pour la base de données AdventureWorks2012AdventureWorks2012 sur l'unité de sauvegarde nommée qui a été précédemment créée, MyAdvWorks_FullRM_log1.This example creates a transaction log backup for the AdventureWorks2012AdventureWorks2012 database to the previously created named backup device, MyAdvWorks_FullRM_log1.

BACKUP LOG AdventureWorks2012  
   TO MyAdvWorks_FullRM_log1;  
GO  

Utilisation de PowerShellUsing PowerShell

  1. Utilisez l’applet de commande Backup-SqlDatabase et spécifiez Log comme valeur du paramètre -BackupAction .Use the Backup-SqlDatabase cmdlet and specify Log for the value of the -BackupAction parameter.

    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.The following example creates a log backup of the MyDB database to the default backup location of the server instance 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 PowerShellTo set up and use the SQL Server PowerShell provider

Related tasksRelated tasks

Informations complémentairesMore information

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
Appliquer les sauvegardes du journal des transactions (SQL Server) Apply Transaction Log Backups (SQL Server)
Plans de maintenance Maintenance Plans
Sauvegardes de fichiers complètes (SQL Server)Full File Backups (SQL Server)