Summary: ce blog explique comment récupérer des enregistrements de table supprimés dans SQL Server. Il montre un exemple de récupération des enregistrements supprimés d'une table SQL à l'aide d'un numéro de séquence de journal (LSN). Il explore également le logiciel Stellar Repair for MS SQL pour récupérer rapidement les enregistrements supprimés sans perte de données.
Si vous avez accidentellement exécuté une commande DROP ou DELETE sur une table dont la clause WHERE était erronée et que vous avez perdu des enregistrements importants, vous pouvez les récupérer en utilisant l’une des méthodes suivantes.
Note : Les opérations de troncature ne peuvent pas être récupérées. Seules les opérations de suppression peuvent être récupérées.
Méthodes de récupération des enregistrements de table supprimés dans SQL Server
Méthode 1 – Utilisation du numéro de séquence du journal (LSN)
Remarque : cette méthode ne fonctionnera pas si la sauvegarde du journal des transactions est corrompue ou manquante. Dans ce cas, passez à la méthode suivante.
Chaque enregistrement du journal des transactions du serveur SQL est identifié de manière unique par un numéro de séquence du journal (LSN). Vous pouvez construire des séquences de restauration en utilisant le LSN d’un enregistrement où un événement important s’est produit.
Prenons un exemple pour comprendre comment vous pouvez utiliser la méthode du numéro de séquence du journal pour récupérer des enregistrements de table supprimés. Dans cet exemple, nous allons créer une base de données et une table, insérer des lignes dans la table, supprimer certaines lignes, obtenir des informations sur les données supprimées et récupérer les données.
Etapes pour récupérer les enregistrements supprimés d’une table SQL avec LSN
Dans cette section, nous allons créer une base de données de test et une table sur laquelle nous exécuterons une opération DELETE. Ensuite, nous trouverons les données supprimées et les récupérerons à l’aide de LSN.
Étape 1 : Créer une base de données
Exécutez la requête suivante pour créer une base de données nommée “RecoverDeletedRecords” et une table nommée “Employee” :
USE [master] ;
GO
CREATE DATABASE RecoverDeletedRecords ;
GO
USE RecoverDeletedRecords ;
GO
CREATE TABLE [Employee] (
[N° Sr.] IDENTITÉ INT,
[Date] DATETIME DEFAULT GETDATE (),
[Ville] CHAR (25) DEFAULT ‘Ville1’) ;
Étape 2 : Insérer des données dans un tableau
Nous avons créé une table nommée “RecoverDeletedRecords” et une table “Employee” avec trois colonnes. Nous allons maintenant insérer des lignes dans la table en exécutant la requête suivante :
USE RecoverDeletedRecords ;
GO
INSERT INTO Employee DEFAULT VALUES ;
GO 100
Étape 3 : Supprimer des lignes du tableau
Supprimons maintenant quelques lignes en exécutant ce qui suit :
USE RecoverDeletedRecords
Aller
DELETE Employé
WHERE [Sr.No] < 10
GO
Select * from Employee
Comme le montre l’image ci-dessus, toutes les lignes dont le numéro d’ordre est inférieur à 10 ont été supprimées de la table “Employé”.
Étape 4 : Obtenir des informations sur les lignes supprimées
Ensuite, nous obtiendrons des informations sur les lignes supprimées en consultant le journal des transactions :
USE RecoverDeletedRecords
GO
SELECTIONNER
[LSN actuel],
[Transaction ID],
Fonctionnement,
Contexte,
Nom de l’unité d’allocation
DE
fn_dblog(NULL, NULL)
OÙ
Opération = ‘LOP_DELETE_ROWS’ (en anglais)
Après avoir obtenu les identifiants de transaction des lignes supprimées, nous devons trouver l’heure à laquelle les lignes ont été supprimées.
Étape 5 : Obtenir le numéro de séquence de l’enregistrement LOP_BEGIN_XACT
Pour trouver l’heure exacte à laquelle les lignes ont été supprimées, nous devons utiliser l’ID de la transaction pour obtenir le LSN de l’enregistrement LOP_BEGIN_XACT d’une transaction :
USE RecoverDeletedRecords
GO
SELECTIONNER
[LSN actuel],
Fonctionnement,
[Transaction ID],
[Heure de début],
[Nom de la transaction],
[SID de la transaction]
DE
fn_dblog(NULL, NULL)
OÙ
[ID de la transaction] = ‘0000:0000020e’
ET
[Opération] = ‘LOP_BEGIN_XACT’ (en anglais)
Dans la capture d’écran ci-dessus, nous pouvons voir le LSN actuel de la transaction ainsi que des détails tels que l’heure ‘2021/03/15 19:36:59:337’ lorsqu’une instruction DELETE a été exécutée avec LSN ‘00000014:0000001a:0001’ sous l’ID de transaction ‘0000:0000020e’. Pour récupérer les données supprimées de la table de la base de données SQL, passez à l’étape suivante.
Étape 6 : Récupérer les enregistrements supprimés dans SQL Server
Pour récupérer les enregistrements supprimés de la table SQL, nous devons convertir les valeurs LSN de la forme hexadécimale à la forme décimale. Pour ce faire, ajoutez “0x” avant le numéro de séquence du journal, comme indiqué dans le code ci-dessous :
-Restauration Sauvegarde complète sans récupération.
RESTORE DATABASE RecoverDeletedRecords_COPY
FROM DISK = ‘C:\NProgram Files\NMicrosoft SQL Server\NMSSQL10_50.STELLAR\NMSSQL\NBackup\NRecoverDeletedRecords.bak’ (Sauvegarde de la récupération des enregistrements supprimés)
AVEC
Déplacer ‘RecoverDeletedRecords’ vers ‘C:\NProgram Files\NMicrosoft SQL Server\NMSSQL10_50.STELLAR\NMSSQL\NBackup\NRecoverDeletedRecords.mdf’,
Déplacez ‘RecoverDeletedRecords_log’ vers ‘C:\NProgram Files\NMicrosoft SQL Server\NMSSQL10_50.STELLAR\NMSSQL\NBackup\NRecoverDeletedRecords.ldf’,
REMPLACER, NI RÉCUPÉRER ;
GO
-Restauration de la sauvegarde du journal avec l’option STOPBEFOREMARK pour récupérer le LSN exact.
RESTORE LOG RecoverDeletedRecords_COPY
DE
DISK = N’C:\NProgram Files\NMicrosoft SQL Server\NMSSQL10_50.STELLAR\NMSSQL\NBackup\NRecoverDeletedRecords_tlogbackup.trn’
AVEC
STOPBEFOREMARK = ‘lsn:0x00000014:0000001a:0001’
Maintenant que vos données sont restaurées, vérifiez si les enregistrements supprimés ont été récupérés. Pour ce faire, exécutez la requête suivante pour voir les dix premières lignes du tableau qui ont été supprimées (à l’étape 3 ci-dessus).
USE RecoverDeletedRecords_COPY
GO
SELECT * from Employee
Si les enregistrements de table supprimés n’ont pas été récupérés, essayez d’utiliser un outil de récupération SQL tel que Stellar Repair for MS SQL pour récupérer les données supprimées.
Remarque : dans le code ci-dessus, remplacez le chemin ‘C:\NProgram Files\NMicrosoft SQL Server\NMSSQL10_50.STELLAR\NMSSQL\NBackup\NRecoverDeletedRecords.bak’ par le chemin où se trouve votre fichier de sauvegarde. Remplacez également le chemin des fichiers .mdf et .ldf par le chemin où vous avez enregistré les fichiers de la base de données.
Méthode 2 – Utilisation du logiciel de récupération SQL
Le logiciel de récupération SQL de Stellar® peut vous aider à récupérer tous les enregistrements supprimés dans une base de données SQL Server en quelques étapes simples. Outre les données supprimées, le logiciel peut également récupérer tous les autres objets tels que les vues, les déclencheurs, les procédures stockées, etc. tout en préservant l’intégrité des données.
Avant de commencer
Avant d’utiliser le logiciel pour restaurer les enregistrements, assurez-vous que votre système répond aux conditions préalables suivantes :
- Tout d’abord, vous devez installer le serveur SQL. Vous pouvez utiliser n’importe quel serveur SQL installé sous Windows.
- Deuxièmement, assurez-vous que SQL Server Management Studio (SSMS) est installé sur votre ordinateur.
- Logiciel Stellar Repair for MS SQL
Etapes pour récupérer des enregistrements de table supprimés avec Stellar Repair for MS SQL
Téléchargez la version de démonstration du logiciel de récupération SQL pour récupérer les lignes supprimées ou les bases de données endommagées :
- Lancez le logiciel Stellar Repair for MS SQL. L’interface principale du logiciel s’ouvre avec l’instruction d’arrêter votre base de données SQL Server et de créer une copie dans un autre emplacement. Appuyez sur OK.
- Parcourez le fichier MDF de la base de données SQL que vous voulez réparer. Si vous ne connaissez pas l’emplacement du fichier MDF, cliquez sur Rechercher pour trouver et sélectionner le fichier :
Remarque : si vous ne savez pas où se trouve votre base de données, vous pouvez la trouver dans SSMS, dans les propriétés de la base de données, dans la page du fichier de données (voir l’image ci-dessous).
- Après avoir sélectionné le fichier de base de données, choisissez l’option Inclure les enregistrements supprimés, puis cliquez sur Réparer.
- Le logiciel vous invite à sélectionner un mode d’analyse approprié pour réparer le fichier de base de données. Sélectionnez “Scan standard” ou “Scan avancé”, puis cliquez sur OK.
- Cliquez sur OK lorsque la boîte de message “Réparation terminée” s’ouvre.
- Le logiciel affiche un aperçu des enregistrements de table récupérables. Il restaure également d’autres objets de la base de données tels que les vues, les synonymes, les procédures stockées, les fonctions, les règles, les valeurs par défaut, les types de données, les déclencheurs, les séquences et les assemblages. Le logiciel affiche également un rapport de journal contenant des informations telles que la taille de la base de données, la collation, le chemin d’accès au fichier mdf et la liste des tables et des objets.
- Ensuite, cliquez sur Enregistrer dans le menu Fichier pour enregistrer les données récupérées.
- Dans la boîte de dialogue ‘Save Database’, vous pouvez choisir d’enregistrer les données récupérées aux formats MDF, CSV, HTML ou XLS (Excel). Dans cet exemple, nous choisirons MSSQL :
- Vous pouvez créer une nouvelle base de données ou travailler avec la base de données en ligne. Vous pouvez restaurer dans une autre instance de SQL Server et spécifier le type d’authentification, c’est-à-dire l’authentification Windows ou l’authentification SQL Server. Vous pouvez également spécifier l’emplacement des fichiers de la base de données :
- Cliquez sur Enregistrer.
Les enregistrements de table récupérés et les autres données sélectionnées seront enregistrés dans le fichier MDF réparé.
Regardez le processus de fonctionnement complet du logiciel SQL Recovery :
Conclusion
Dans cet article, nous avons appris comment récupérer des enregistrements supprimés dans SQL Server. Ces solutions peuvent fonctionner avec n’importe quelle version de SQL Server sous Windows. Si vous disposez d’une sauvegarde complète de la base de données, vous pouvez essayer de récupérer les enregistrements supprimés en utilisant le journal des transactions avec le numéro de séquence du journal (LSN). Si la sauvegarde est corrompue, utilisez Stellar Repair for MS SQL pour récupérer les données supprimées dans SQL Server ou dans d’autres formats tels que les fichiers CSV (Comma Separated Value), HTML ou XLS.
Le logiciel de réparation de bases de données SQL aide non seulement à récupérer les lignes supprimées mais aussi à restaurer les bases de données SQL endommagées par une attaque de virus, une panne de matériel ou d’autres raisons.
