Dans l’article Récupérer des données supprimés à partir d’un backup ou d’une base de données en ligne, nous avons montré deux scénarios de récupération, l’un à partir d’un backup complet et l’autre à partir du fichier de données de base de données en ligne, le fichier MDF. Outre ces deux scénarios, il existe une autre ressource que vous pouvez utiliser quand il s’agit de la récupération de données SQL Server – un journal de transactions.
Chaque changement dans le schéma ou sur les données d’une base de données SQL Server est enregistré dans un journal de transactions en ligne. Si les opérations de suppression que vous voulez annuler sont dans un journal des transactions en ligne, dans un journal de transactions détaché ou dans une sauvegarde de journal de transactions, vous pouvez les lire et les annuler si votre base de données est en :
- Modèle de récupération complet, les opérations de suppression sont enregistrées dans le journal de transactions en ligne ou dans une sauvegarde du journal des transactions, à condition qu’elles aient été créées après les opérations de suppressions.
- Modèle de récupération journalisé par bloc, les opérations de suppression sont entièrement enregistrées dans le journal de transactions en ligne, sauf pour les données BLOB supprimés. Comme les suppressions de type BLOB ne sont pas enregistrés dans le journal des transactions et que les insertions de type BLOB ne sont pas enregistrés en raison du modèle de récupération journalisé par bloc, leur récupération ne sera pas possible. Pour les autres types de données, les opérations de suppression seront enregistrées dans le journal de transactions en ligne ou présent dans une sauvegarde du journal de transactions créée après la suppression.
- Modèle de récupération simple, s’il y a eu de nombreuses transactions exécutées sur la base de données après la suppression d’enregistrements, les opérations de suppression peuvent ne plus être dans le journal de transactions en ligne. Il est donc préférable d’exécutez au plus tôt une lecture du journal de transactions en ligne pour avoir une chance de les récupérer.
Utilisation de ApexSQL Log
Pour lire le journal de transactions d’une base de données en ligne, les journaux de transactions détachés ou sauvegardés (compressés ou non) utilisez ApexSQL Log. C’est un lecteur de journaux de transactions pour SQL Server, il permet d’auditer, de revenir en arrière et de rejouer des modifications sur des données et des objets, y compris celles qui ont eu lieu avant que ApexSQL Log ai été installé. Il capture également des informations sur l’utilisateur, l’application et l’hôte ayant effectué des modifications.
- Exécutez ApexSQL Log
-
Connectez-vous à la base de données en ligne sur laquelle les enregistrements ont été supprimés
-
Si les sauvegardes du journal de transactions sont créées après que les enregistrements ont été supprimés, ajoutez-les à l’étape suivante. Notez que les sauvegardes du journal de transactions doivent former une chaîne complète si les documents que vous essayez de récupérer contiennent des données BLOB (voir ci-dessous pour plus de détails à ce sujet)
-
Dans le cas où vous avez un journal de transactions détaché contenant des enregistrements supprimés (par exemple, si vous avez déconnecté la base de données et copié le fichier LDF immédiatement après la suppression), vous pouvez l’utiliser ainsi :
-
Utilisez la section Time range dans les options Filter setup pour affiner les transactions qui se sont produites dans un délai déterminé
-
Utilisez le filtre Operations pour affiner les résultats aux enregistrements supprimés. Pour ce faire, décochez toutes les opérations de schéma (DDL), ainsi que les lignes insérées et actualisées dans la liste des opérations des données (DML)
-
Si possible, indiquez les tables dont les enregistrements ont été supprimés. Sinon, laissez toutes les tables des utilisateurs sélectionnées
-
Cliquez sur Open
-
Toutes les transactions de suppression sont affichées dans la grille principale. Vous n’avez plus qu’à sélectionner celles dont vous avez besoin, et cliquez sur Create Undo Script dans le menu pour créer un script d’annulation
-
Pour créer un script d’annulation pour tous les enregistrements affichés dans la grille, cliquez sur Create undo script dans le menu. Pour créer un script d’annulation pour seulement une partie des enregistrements, sélectionnez-les dans la grille, et dans le menu contextuel de la grille sélectionnez Create undo script
Le script qui insère les enregistrements supprimés sera alors créé
-
Pour exécuter le script, cliquez sur
dans le menu
Utilisation de ApexSQL Log pour créer un script d’annulation
La méthode décrite avec les résultats présentés dans la grille n’est acceptable que pour un petit nombre d’enregistrement. Quand il s’agit de millions d’enregistrements supprimés, nous vous recommandons de créer un script d’annulation. Les étapes 1 à 7 sont les mêmes que ci-dessus
-
Au lieu d’afficher les résultats dans la grille, cliquez sur Export results to a file
-
Sélectionnez Generate rollback (undo) script et spécifiez le nom et le chemin du fichier
-
Cliquez sur OK
Le script d’annulation qui insère tous les enregistrements supprimés sera créé
Utilisation de ApexSQL Recover
C’est un autre outil ApexSQL qui peut lire des journaux de transactions SQL Server – d’un journal de transactions en ligne, de journaux de transactions détachés ainsi que des journaux de transactions sauvegardés (compressés ou non). ApexSQL Recover est un outil de récupération de bases de données SQL Server qui récupère des données supprimés, tronqués, corrompus ou endommagés. Il récupère les objets et les données perdues suite à des opérations de suppression et restaure les BLOBs sous forme de fichiers. Le processus de récupération est assez similaire à celle décrite ici: Recover deleted SQL data from a backup or from online database files – Use ApexSQL Recover
- Lancer ApexSQL Recover
- Sélectionnez Recover lost data due to a DELETE operation
- Spécifiez les instances SQL Server, les droits et la base de données où les enregistrements ont été supprimés
- Sélectionnez Several sources are available for the recovery
- Le journal de transactions en ligne, toutes les sauvegardes du journal des transactions et des sauvegardes de bases de données créées pour la base de données sélectionnée sont automatiquement répertoriées. Pour ajouter d’autres sauvegardes du journal de transactions, ou un journal des transactions détaché qui contient les enregistrements supprimés, utilisez le bouton Add. Notez que les sauvegardes du journal des transactions doivent former une chaîne complète
- Spécifiez le moment où l’instruction de suppression a été exécutée. Si vous ne savez pas quand cela s’est produit, sélectionnez Date and time are unknown
- Utilisez le filtre pour affiner la restauration aux tables nécessaires, si applicable
- Cliquez sur Recover
- Un script qui insère les données supprimées est créé. Vérifiez-le et exécutez-le
La bonne nouvelle concernant la restauration d’enregistrements supprimés est qu’il y a une chance de réussite, même sans une sauvegarde de base de données, ou une chaîne complète de sauvegardes du journal des transactions. Notez que la récupération réussie des données supprimées avec BLOB n’est pas possible sans une chaîne complète des journaux de transactions. Comme l’opération de suppression de BLOBs n’enregistre pas la valeur réelle supprimée, il est nécessaire de passer par la séquence des sauvegardes des journaux de transactions jusqu’au début de la chaîne. Si la chaîne est brisée, la reconstruction pourrait être incorrecte. Par exemple, si les informations sur une mise à jour de données BLOB est manquant, le BLOB inséré à l’origine sera récupérée au lieu de celle supprimée.
Même avec une base de données avec le modèle de récupération simple, il y a une chance de restauration. Dans le modèle de récupération simple, de nouvelles transactions enregistrées dans un journal de transactions en ligne remplacent les anciennes. Si le journal des transactions en ligne fait par exemple 100 Go, et vous avez 1 Go de données de journaux de transactions enregistrées chaque jour, le journal des transactions en ligne sera complètement écrasé en 100 jours. Cela signifie que vous pouvez récupérer les données supprimées datant de 100 jours
Bien sûr, cela ne signifie nullement que vous devez compter sur cela et oubliez votre stratégie de reprise après sinistres pour SQL Server.
ApexSQL dispose de deux outils qui peuvent vous aider concernant les journaux de transactions. ApexSQL Log fournit plusieurs options de filtrage des enregistrements récupérés – outre le filtrage par heure et le nom de la table qui est une caractéristique commune pour les deux outils. Il fournit des tableaux filtrés contenant les enregistrements basés sur un certain nombre de valeurs, la description de la transaction par un utilisateur qui a commis les suppressions, et dans le cas où l’écran de connexion ApexSQL Log est en cours d’exécution au moment où les opérations ont eu lieu par l’application et le nom de l’hôte.
ApexSQL Recover a moins d’options de filtrage, mais offre plus d’options de sortie de récupération. Outre la création d’un script T-SQL qui insère des enregistrements, il peut aussi créer une nouvelle base de données et récupérer les enregistrements supprimés de celle-ci.
Article traduit par Romain Casteres
Consultant en informatique décisionnelle et en Big Data chez Dcube. Il est MVP SQL Server, MCSE Data Platform et Business Intelligence.
Blog : http://pulsweb.fr
Twitter : @PulsWeb
October 20, 2014
