Afficher ou modifier le modèle de récupération d’une base de données (SQL Server)

Article
08/02/2023

S’applique à :SQL Server

Cet article explique comment afficher ou modifier la base de données à l’aide de SQL Server Management Studio ou de Transact-SQL.

Un mode de récupération est une propriété de base de données qui contrôle la façon dont les transactions sont journalisées, précise si le journal des transactions nécessite (et permet) une sauvegarde et spécifie les types d’opérations de restauration disponibles. Il existe trois modes de récupération : simple, complète et utilisant les journaux de transactions. En règle générale, une base de données utilise le mode de restauration complète ou le mode de récupération simple. Il est possible de modifier le mode de récupération d'une base de données à tout moment. La model base de données définit le modèle de récupération par défaut des nouvelles bases de données.

Pour une explication plus approfondie, consultez modes de récupération.

Avant de commencer

Avantmode de récupération complète ou en mode de récupération utilisant les journaux de transactions , sauvegardez le journal des transactions.
La récupération à un point dans le temps n’est pas possible avec le modèle journalisé en bloc. L’exécution de transactions sous le modèle de récupération journalisé en bloc qui nécessitent une restauration du journal des transactions peut les exposer à la perte de données. Pour optimiser la possibilité de récupérer les données dans un scénario de récupération après sinistre, passez au mode de récupération utilisant les journaux de transactions dans les conditions suivantes :
- Les utilisateurs ne sont pas actuellement autorisés dans la base de données.
- Toutes les modifications effectuées pendant le traitement en bloc sont récupérables sans en fonction de la sauvegarde d’un journal ; par exemple, en réexécutant les processus en bloc.
  
  Si vous remplissez ces deux conditions, vous n’êtes pas exposé à une perte de données lors de la restauration d’un journal des transactions sauvegardé sous le modèle de récupération journalisé en bloc.
Si vous basculez vers le modèle de récupération complète pendant une opération en bloc, les opérations en bloc journalisation passent de la journalisation minimale à la journalisation complète, et vice versa.

Autorisations

Nécessite l'autorisation ALTER sur la base de données.

Utilisez SQL Server Management Studio.

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.
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.
Cliquez avec le bouton droit sur la base de données, puis sélectionnez Propriétés, ce qui ouvre la boîte de dialogue Propriétés de la base de données.
Dans le volet Sélectionner une page, sélectionnez Options.
Le mode de récupération actuel s'affiche dans la zone de liste Mode de récupération .
Au besoin, pour modifier le mode de récupération, sélectionnez un autre mode dans la liste. Les choix sont Complet, Journalisé en blocou Simple.
Cliquez sur OK.

Remarque

Les entrées de cache de plan pour la base de données seront vidées ou effacées.

Utiliser Transact-SQL

Afficher le modèle de récupération

Connectez-vous au Moteur de base de données.
Dans la barre d’outils standard, sélectionnez Nouvelle requête.
Copiez et collez l’exemple suivant dans la fenêtre de requête, puis sélectionnez Exécuter. Cet exemple montre comment interroger la vue catalogue sys.databases pour découvrir le modèle de récupération de la model base de données.

SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = 'model';
GO

Modifier le modèle de récupération

Connectez-vous au Moteur de base de données.
Dans la barre d’outils standard, sélectionnez Nouvelle requête.
Copiez et collez l’exemple suivant dans la fenêtre de requête, puis sélectionnez Exécuter. Cet exemple montre comment modifier le mode de récupération de la base de données model en FULL à l'aide de l'option SET RECOVERY de l'instruction ALTER DATABASE .

USE [master];
GO
ALTER DATABASE [model]
SET RECOVERY FULL;
GO

Remarque

Les entrées de cache de plan pour la base de données seront vidées ou effacées.

Recommandations : Après avoir modifié le mode de récupération

Après un changement de mode de récupération complète ou de mode de récupération utilisant les journaux de transactions

Après avoir effectué les opérations en bloc, revenez immédiatement au modèle de récupération complète.
Après être passé du mode de récupération utilisant les journaux de transactions au mode de récupération complète, sauvegardez le journal.

Votre stratégie de sauvegarde ne change pas : continuez à effectuer régulièrement des sauvegardes des bases de données, des sauvegardes des journaux et des sauvegardes différentielles.

Après basculement à partir du mode de récupération simple

Aussitôt après être passé en mode de restauration complète ou en mode de récupération utilisant les journaux de transactions, procédez à une sauvegarde de base de données complète ou différentielle pour lancer la séquence de journaux.

Le passage au mode de restauration complète ou mode de récupération utilisant les journaux de transactions n'est effectif qu'après la première sauvegarde de base de données.
Planifiez des sauvegardes de journaux régulières et mettez à jour votre plan de restauration en conséquence.

Important

Sauvegardez vos journaux. Si vous ne sauvegardez pas assez souvent le journal, il est susceptible de s'étendre jusqu'à manquer de l'espace disque nécessaire.

Après basculement en mode de récupération simple

Mettez fin à tous les travaux planifiés afin de sauvegarder le journal des transactions.
Assurez-vous que des sauvegardes des bases de données régulières sont planifiées. La sauvegarde de votre base de données est essentielle pour protéger vos données et tronquer la partie inactive du journal des transactions.