Restaurer une base de données SQL Server jusqu'à une limite dans le temps (mode de récupération complète)Restore a SQL Server Database to a Point in Time (Full Recovery Model)

S’APPLIQUE À : ouiSQL Server nonAzure SQL Database nonAzure Synapse Analytics (SQL DW) nonParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Cette rubrique explique comment restaurer une base de données à un point précis dans le temps dans SQL Server 2019 (15.x)SQL Server 2019 (15.x) à l'aide de SQL Server Management StudioSQL Server Management Studio ou de Transact-SQLTransact-SQL.This topic describes how to restore a database to a point in time in SQL Server 2019 (15.x)SQL Server 2019 (15.x) by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. Cette rubrique s'applique uniquement aux bases de données SQL ServerSQL Server utilisant le mode de restauration complète ou de récupération utilisant les journaux de transactions.This topic is relevant only for SQL ServerSQL Server databases that use the full or bulk-logged recovery models.

Important

Dans le mode de récupération utilisant les journaux de transactions, si la sauvegarde du journal contient des modifications journalisées en bloc, la récupération jusqu'à une date et heure à un point de cette sauvegarde est impossible.Under the bulk-logged recovery model, if a log backup contains bulk-logged changes, point-in-time recovery is not possible to a point within that backup. La base de données doit être récupérée à la fin de la sauvegarde du journal des transactions.The database must be recovered to the end of the transaction log backup.

Avant de commencerBefore You Begin

RecommandationsRecommendations

  • Utilisation de STANDBY pour retrouver un moment inconnu dans le temps.Use STANDBY to find unknown point in time.

  • Spécification du point d'arrêt suffisamment tôt dans la séquence de restaurationSpecify the point in time early in a restore sequence

SécuritéSecurity

AutorisationsPermissions

Si la base de données restaurée n'existe pas, l'utilisateur doit posséder les autorisations CREATE DATABASE afin de pouvoir exécuter RESTORE.If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. Si la base de données existe, les autorisations RESTORE reviennent par défaut aux membres des rôles serveur fixe sysadmin et dbcreator et au propriétaire (dbo) de la base de données (pour l’option FROM DATABASE_SNAPSHOT, la base de données existe toujours).If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always exists).

Les autorisations RESTORE sont attribuées aux rôles dont les informations d'appartenance sont toujours immédiatement accessibles à partir du serveur.RESTORE permissions are given to roles in which membership information is always readily available to the server. Étant donné que l’appartenance au rôle de base de données fixe ne peut être contrôlée que quand la base de données est accessible et non endommagée, ce qui n’est pas toujours le cas quand RESTORE est exécuté, les membres du rôle de base de données fixe db_owner ne détiennent pas d’autorisations RESTORE.Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.

Utilisation de SQL Server Management StudioUsing SQL Server Management Studio

Pour restaurer une base de données à un point dans le tempsTo restore a database to a point in time

  1. Dans l'Explorateur d'objets, connectez-vous à l'instance appropriée du Moteur de base de données SQL ServerSQL Server Database Engine, puis développez l'arborescence du serveur.In Object Explorer, connect to the appropriate instance of the Moteur de base de données SQL ServerSQL Server Database Engine, and expand the server tree.

  2. Développez Bases de données.Expand Databases. Selon la base de données, sélectionnez une base de données utilisateur ou développez Bases de données systèmeet sélectionnez une base de données système.Depending on the database, either select a user database or expand System Databases, and then select a system database.

  3. Cliquez avec le bouton droit sur la base de données, pointez sur Tâches, sur Restaurer, puis cliquez sur Base de données.Right-click the database, point to Tasks, point to Restore, and then click Database.

  4. Dans la page Général , utilisez la section Source pour préciser la source et l'emplacement des jeux de sauvegarde à restaurer.On the General page, use the Source section to specify the source and location of the backup sets to restore. Sélectionnez l'une des options suivantes :Select one of the following options:

    • Sauvegarde de la base de donnéesDatabase

      Sélectionnez la base de données à restaurer dans la liste déroulante.Select the database to restore from the drop-down list. La liste contient uniquement les bases de données qui ont été sauvegardées selon l'historique de sauvegarde msdb .The list contains only databases that have been backed up according to the msdb backup history.

    Notes

    Si la sauvegarde est prise à partir d'un serveur différent, le serveur de destination ne disposera pas des informations d'historique de sauvegarde pour la base de données spécifiée.If the backup is taken from a different server, the destination server will not have the backup history information for the specified database. Dans ce cas, sélectionnez Unité pour spécifier manuellement le fichier ou l'unité à restaurer.In this case, select Device to manually specify the file or device to restore.

    • UnitéDevice

      Cliquez sur le bouton Parcourir ( ... ) pour ouvrir la boîte de dialogue Sélectionner les unités de sauvegarde .Click the browse (...) button to open the Select backup devices dialog box. Dans la zone Type du média de sauvegarde , sélectionnez l'un des types d'unités proposés.In the Backup media type box, select one of the listed device types. Pour sélectionner une ou plusieurs unités pour la zone Support de sauvegarde , cliquez sur Ajouter.To select one or more devices for the Backup media box, click Add.

      Après avoir ajouté les unités souhaitées à la zone de liste Support de sauvegarde , cliquez sur OK pour revenir à la page Général .After you add the devices you want to the Backup media list box, click OK to return to the General page.

      Dans la zone de liste Source : Unité : Base de données, sélectionnez le nom de la base de données à restaurer.In the Source: Device: Database list box, select the name of the database which should be restored.

      Remarque Cette liste n'est disponible que lorsque Unité est sélectionné.Note This list is only available when Device is selected. Seules les bases de données qui ont des copies de sauvegarde sur l'unité sélectionnée seront disponibles.Only databases that have backups on the selected device will be available.

  5. Dans la section Destination , la zone Base de données est automatiquement renseignée avec le nom de la base de données à restaurer.In the Destination section, the Database box is automatically populated with the name of the database to be restored. Pour changer le nom de la base de données, entrez le nouveau nom dans la zone Base de données .To change the name of the database, enter the new name in the Database box.

  6. Cliquez sur Chronologie pour accéder à la boîte de dialogue Chronologie de sauvegarde .Click Timeline to access the Backup Timeline dialog box.

  7. Dans la section Restaurer sur , cliquez sur Date et heure spécifiques.In the Restore to section, click Specific date and time.

  8. Utilisez les zones Date et Heure ou la barre du curseur pour spécifier une date et une heure spécifiques auxquelles la restauration doit s'arrêter.Use either the Date and Time boxes or the slider bar to specify a specific date and time to where the restore should stop. Cliquez sur OK.Click OK.

    Notes

    Utilisez la zone Chronologie - Intervalle pour modifier la durée affichée dans la chronologie.Use the Timeline Interval box to change the amount of time displayed on the timeline.

  9. Après avoir défini un point spécifique dans le temps, l'assistant de récupération de base de données garantit que seules les sauvegardes nécessaires à la restauration à un point précis dans le temps sont sélectionnées dans la colonne Restaurer de la grille Jeux de sauvegarde à restaurer .After you have specified a specific point in time, the Database Recovery Advisor ensures that only backups that are required for restoring to that point in time are selected in the Restore column of the Backup sets to restore grid. Ces sauvegardes sélectionnées constituent le plan de restauration recommandé pour votre limite de restauration dans le temps.These selected backups make up the recommended restore plan for your point-in-time restore. Utilisez uniquement les sauvegardes sélectionnées pour votre opération de restauration jusqu'à une date et heure.You should use only the selected backups for your point-in-time restore operation.

    Pour plus d’informations sur les colonnes de la grille Jeux de sauvegarde à restaurer , consultez Restaurer la base de données (page Général).For information about the columns in the Backup sets to restore grid, see Restore Database (General Page). Pour plus d’informations sur l’Assistant de récupération de base de données, consultez Vue d’ensemble de la restauration et de la récupération (SQL Server).For information about the Database Recovery Advisor, see Restore and Recovery Overview (SQL Server).

  10. Dans la page Options , dans le volet Options de restauration , vous pouvez choisir les options suivantes si elles s'appliquent à votre situation :On the Options page, in the Restore options panel, you can select any of the following options, if appropriate for your situation:

    • Remplacer la base de données existante (WITH REPLACE)Overwrite the existing database (WITH REPLACE)

    • Conserver les paramètres de la réplication (WITH KEEP_REPLICATION)Preserve the replication settings (WITH KEEP_REPLICATION)

    • Restreindre l'accès à la base de données restaurée (WITH RESTRICTED_USER)Restrict access to the restored database (WITH RESTRICTED_USER)

    Pour plus d’informations sur ces options, consultez Restaurer la base de données (page Options).For more information about these options, see Restore Database (Options Page).

  11. Sélectionnez une option pour la zone État de récupération .Select an option for the Recovery state box. Cette zone détermine l'état de la base de données à l'issue de l'opération de restauration.This box determines the state of the database after the restore operation.

    • RESTORE WITH RECOVERY est le comportement par défaut qui laisse la base de données opérationnelle en annulant les transactions non validées.RESTORE WITH RECOVERY is the default behavior which leaves the database ready for use by rolling back the uncommitted transactions. Les journaux des transactions supplémentaires ne peuvent pas être restaurés.Additional transaction logs cannot be restored. Choisissez cette option si vous restaurez toutes les sauvegardes nécessaires maintenant.Select this option if you are restoring all of the necessary backups now.

    • RESTORE WITH NORECOVERY qui laisse la base de données non opérationnelle et n’annule pas les transactions non validées.RESTORE WITH NORECOVERY which leaves the database non-operational, and does not roll back the uncommitted transactions. Les journaux des transactions supplémentaires peuvent être restaurés.Additional transaction logs can be restored. La base de données ne peut pas être utilisée tant qu'elle n'est pas récupérée.The database cannot be used until it is recovered.

    • RESTORE WITH STANDBY qui laisse la base de données en lecture seule.RESTORE WITH STANDBY which leaves the database in read-only mode. Elle annule les transactions non validées, mais enregistre les actions d'annulation dans un fichier afin de rendre réversibles les effets de la récupération.It undoes uncommitted transactions, but saves the undo actions in a standby file so that recovery effects can be reverted.

    Pour obtenir des descriptions des options, consultez Restaurer la base de données (page Options).For descriptions of the options, see Restore Database (Options Page).

  12. L’option Effectuer la sauvegarde de la fin du journal avant la restauration est sélectionnée si elle s’avère nécessaire pour le moment sélectionné.Take tail-log backup before restore will be selected if it is necessary for the point in time that you have selected. Vous n'avez pas besoin de modifier ce paramètre, mais vous pouvez choisir de sauvegarder la fin du journal même si ce n'est pas obligatoire.You do not need to modify this setting, but you can choose to backup the tail of the log even if it is not required.

  13. Les opérations de restauration peuvent échouer s'il existe des connexions actives à la base de données.Restore operations may fail if there are active connections to the database. Activez l'option Fermer les connexions existantes pour garantir que toutes les connexions actives entre Management StudioManagement Studio et la base de données sont fermées.Check the Close existing connections option to ensure that all active connections between Management StudioManagement Studio and the database are closed. Cette case à cocher définit la base de données en mode mono-utilisateur avant d'effectuer les opérations de restauration, et définit la base de données en mode multi-utilisateur une fois l'opération terminée.This check box sets the database to single user mode before performing the restore operations, and sets the database to multi-user mode when complete.

  14. Sélectionnez Demander confirmation avant chaque restauration de sauvegarde si vous souhaitez être invité entre chaque opération de restauration.Select Prompt before restoring each backup if you wish to be prompted between each restore operation. Cela n'est généralement pas nécessaire à moins que la base de données ne soit volumineuse et que vous ne souhaitiez surveiller l'état de l'opération de restauration.This is not usually necessary unless the database is large and you wish to monitor the status of the restore operation.

Utilisation de Transact-SQLUsing Transact-SQL

Before you beginBefore you begin

La restauration d'une heure spécifiée est toujours effectuée à partir d'une sauvegarde de fichier journal.A specified time is always restored from a log backup. Dans chaque instruction RESTORE LOG de la séquence de restauration, vous devez spécifier votre heure cible ou votre transaction dans une clause STOPAT identique.In every RESTORE LOG statement of the restore sequence, you must specify your target time or transaction in an identical STOPAT clause. Comme condition préalable dans une restauration limitée dans le temps, vous devez restaurer une sauvegarde complète de base de données dont le point d'arrêt est antérieur à votre heure de restauration cible.As a prerequisite to a point-in-time restore, you must first restore a full database backup whose end point is earlier than your target restore time. Cette sauvegarde complète de base de données peut être plus ancienne que la sauvegarde complète de base de données la plus récente dans la mesure où vous restaurez ensuite chaque sauvegarde de fichier journal suivante jusqu'à la sauvegarde de fichier journal (comprise) contenant votre limite dans le temps cible.That full database backup can be older than the most recent full database backup as long as you then restore every subsequent log backup, up to and including the log backup that contains your target point in time.

Pour vous aider à identifier la sauvegarde de base à restaurer, vous pouvez éventuellement spécifier votre clause WITH STOPAT dans l'instruction RESTORE DATABASE afin de générer une erreur lorsqu'une sauvegarde de données est trop récente pour l'heure cible spécifiée.To help you identify which database backup to restore, you can optionally specify your WITH STOPAT clause in your RESTORE DATABASE statement to raise an error if a data backup is too recent for the specified target time. La sauvegarde de données complète est toujours restaurée, même si elle contient l'heure cible.The complete data backup is always restored, even if it contains the target time.

Syntaxe Transact-SQLTransact-SQL de baseBasic Transact-SQLTransact-SQL syntax

RESTORE LOG nom_base_de_données FROM <unité_sauvegarde> WITH STOPAT = heure , RECOVERY...RESTORE LOG database_name FROM <backup_device> WITH STOPAT =time, RECOVERY...

Le point de récupération est la dernière validation de transaction qui s’est produite au plus tard à l’heure datetime spécifiée par time.The recovery point is the latest transaction commit that occurred at or before the datetime value that is specified by time.

Pour restaurer uniquement les modifications apportées avant un moment spécifique dans le temps, spécifiez WITH STOPAT = time pour chaque sauvegarde que vous restaurez.To restore only the modifications that were made before a specific point in time, specify WITH STOPAT = time for each backup you restore. De cette manière, vous êtes certain de ne pas dépasser le moment cible.This makes sure that you do not go past the target time.

Pour restaurer une base de données à un point dans le tempsTo restore a database to a point in time

Notes

Pour obtenir un exemple de cette procédure, consultez Exemple (Transact-SQL)plus loin dans cette section.For an example of this procedure, see Example (Transact-SQL), later in this section.

  1. Connectez -vous à l'instance de serveur sur laquelle vous souhaitez restaurer la base de données.Connect to server instance on which you want to restore the database.

  2. Exécutez l'instruction RESTORE DATABASE à l'aide de l'option NORECOVERY.Execute the RESTORE DATABASE statement using the NORECOVERY option.

    Notes

    Si une séquence de restauration partielle exclut tout groupe de fichiers FILESTREAM , la limite de restauration dans le temps n’est pas prise en charge.If a partial restore sequence excludes any FILESTREAM filegroup, point-in-time restore is not supported. Vous pouvez forcer la séquence de restauration à continuer.You can force the restore sequence to continue. Cependant, les groupes de fichiers FILESTREAM omis de l'instruction RESTORE ne peuvent jamais être restaurés.However the FILESTREAM filegroups that are omitted from your RESTORE statement can never be restored. Pour forcer une limite de restauration dans le temps, spécifiez l'option CONTINUE_AFTER_ERROR avec l'option STOPAT, STOPATMARK ou STOPBEFOREMARKx, que vous devez également spécifier dans vos instructions RESTORE LOG suivantes.To force a point-in-time restore, specify the CONTINUE_AFTER_ERROR option together with the STOPAT, STOPATMARK, or STOPBEFOREMARK option, which you must also specify in your subsequent RESTORE LOG statements. Si vous spécifiez l'option CONTINUE_AFTER_ERROR, la séquence de restauration partielle réussit et le groupe de fichiers FILESTREAM devient irrécupérable.If you specify CONTINUE_AFTER_ERROR, the partial restore sequence succeeds and the FILESTREAM filegroup becomes unrecoverable.

  3. Restaurez la dernière sauvegarde différentielle de base de données, si elle existe, sans récupérer la base de données (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY).Restore the last differential database backup, if any, without recovering the database (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY).

  4. Appliquez chaque sauvegarde du journal des transactions dans l’ordre de sa création, en spécifiant l’heure à laquelle vous avez l’intention d’arrêter la restauration du journal (RESTORE DATABASE database_name FROM <backup_device> WITH STOPAT = time , RECOVERY).Apply each transaction log backup in the same sequence in which they were created, specifying the time at which you intend to stop restoring log (RESTORE DATABASE database_name FROM <backup_device> WITH STOPAT**=time,** RECOVERY).

    Notes

    Options RECOVERY et STOPAT.The RECOVERY and STOPAT options. Si la sauvegarde du journal des transactions ne contient pas l'heure demandée (par exemple, si l'heure spécifiée dépasse la dernière heure figurant dans le journal des transactions), un avertissement est émis et la base de données n'est pas récupérée.If the transaction log backup does not contain the requested time (for example, if the time specified is beyond the end of the time covered by the transaction log), a warning is generated and the database remains unrecovered.

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

L'exemple suivant restaure une base de données dans l'état où elle se trouvait le 12:00 AM à April 15, 2020 et décrit une opération de restauration impliquant plusieurs sauvegardes de fichiers journaux.The following example restores a database to its state as of 12:00 AM on April 15, 2020 and shows a restore operation that involves multiple log backups. Sur l'unité de sauvegarde AdventureWorksBackups, la sauvegarde de base de données complète à restaurer correspond au troisième jeu de sauvegarde (FILE = 3), la première sauvegarde de fichier journal correspond au quatrième jeu de sauvegarde (FILE = 4) et la seconde sauvegarde de fichier journal correspond au cinquième jeu de sauvegarde (FILE = 5).On the backup device, AdventureWorksBackups, the full database backup to be restored is the third backup set on the device (FILE = 3), the first log backup is the fourth backup set (FILE = 4), and the second log backup is the fifth backup set (FILE = 5).

Important

La base de données AdventureWorks2012AdventureWorks2012 utilise le mode de récupération simple.The AdventureWorks2012AdventureWorks2012 database 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 par le biais de ALTER DATABASE AdventureWorks SET RECOVERY FULL.To permit log backups, before taking a full database backup, the database was set to use the full recovery model, using ALTER DATABASE AdventureWorks SET RECOVERY FULL.

RESTORE DATABASE AdventureWorks  
   FROM AdventureWorksBackups  
   WITH FILE=3, NORECOVERY;  
  
RESTORE LOG AdventureWorks  
   FROM AdventureWorksBackups  
   WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';  
  
RESTORE LOG AdventureWorks  
   FROM AdventureWorksBackups  
   WITH FILE=5, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';  
RESTORE DATABASE AdventureWorks WITH RECOVERY;   
GO  
  

Tâches associéesRelated Tasks

Voir aussiSee Also

backupset (Transact-SQL) backupset (Transact-SQL)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
RESTORE HEADERONLY (Transact-SQL)RESTORE HEADERONLY (Transact-SQL)