Comment récupérer les enregistrements de table supprimés dans SQL Server

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.

Was this article helpful?

YES0

NO

A propos de l'auteur

Himanshu Shakya

Himanshu is a Tech Enthusiast & Blogger at Stellar and having knowledge of Japanese Language. And apart from this in his spare time he likes playing Chess.