SQL Database Repair

How to Recover Deleted Table Records in SQL Server


Table of Content

    Summary: This blog explains how to recover deleted table records in SQL Server. It demonstrates an example to retrieve deleted records from a SQL table using a Log Sequence Number (LSN). It also explores Stellar Repair for MS SQL software to recover deleted records quickly without any data loss.

    Read full summary

    If you’ve accidentally executed a DROP or DELETE command against a table with a wrong WHERE clause and lost important records, you can recover them by using any of the following methods.

    Note: The truncate operations cannot be recovered. Only the delete operations can be recovered.

    Methods to Recover Deleted Table Records in SQL Server

    Method 1 – Using Log Sequence Number (LSN)

    Note: This method won’t work if the transaction log backup is corrupted or missing. In that case, skip to the next method.

    Each SQL Server transaction log record is uniquely identified by a log sequence number (LSN). You can construct restore sequences by using the LSN of a log record at which a significant event occurred.

    Let’s take an example to understand how you can use the log sequence number method to recover deleted table records. In this example – we’ll create a database and a table, insert rows into the table, delete some rows, get information about deleted data, and recover the data.

    Steps to Recover Deleted Records from SQL Table with LSN

    In this section, we will create a test database and a table on which we will run a DELETE operation. Then, we will find deleted data and recover it using LSN.

    Step 1: Create a Database

    Execute the following query to create a database named ‘RecoverDeletedRecords’ and a table named ‘Employee’:

    USE [master];  
    
    GO   
    
    CREATE DATABASE RecoverDeletedRecords;
    
    GO
    
    USE RecoverDeletedRecords;   
    
    GO
    
    CREATE TABLE [Employee] (
    
    [Sr.No] INT IDENTITY,
    
    [Date] DATETIME DEFAULT GETDATE (),
    
    [City] CHAR (25) DEFAULT 'City1');
    Create Database in SSMS
    Figure 1 – Create Database in SSMS

    Step 2: Insert Data into Table

    We have created a table named ‘RecoverDeletedRecords’ and a table ‘Employee’ with three columns. Now, we’ll insert rows into the table by running the following query:

    USE RecoverDeletedRecords;
    
    GO
    
    INSERT INTO Employee DEFAULT VALUES;
    
    GO 100
    Insert Records into New Table
    Figure 2 – Insert Records into New Table

    Step 3: Delete Rows from Table

    Now let’s delete some rows by executing the following:

    USE RecoverDeletedRecords
    
    Go
    
    DELETE Employee
    
    WHERE [Sr.No] < 10
    
    GO
    
    Select * from Employee
    Delete Table Rows
    Figure 3 – Delete Table Rows

    As shown in the image above, all the rows having Sr.No less than 10 have been deleted from the table ‘Employee’.

    Step 4: Get Information about Deleted Rows

    Next, we will get information about the deleted rows by searching the transaction log:

    USE RecoverDeletedRecords
    
    GO
    
    SELECT
    
     [Current LSN],   
    
     [Transaction ID],
    
         Operation,
    
         Context,
    
         AllocUnitName
    
    FROM
    
        fn_dblog(NULL, NULL)
    
    WHERE
    
        Operation = 'LOP_DELETE_ROWS'
    Transaction ID of Deleted Rows
    Figure 4 – Transaction ID of Deleted Rows

    After getting the Transaction IDs of the deleted rows, we’ll have to find the time when the rows were deleted.

    Step 5: Get Log Sequence Number of the LOP_BEGIN_XACT Log Record

    To find the exact time when the rows were deleted, we must use the transaction ID to get the LSN of the LOP_BEGIN_XACT log record of a transaction:

    USE RecoverDeletedRecords
    
    GO
    
    SELECT
    
     [Current LSN],   
    
     Operation,
    
         [Transaction ID],
    
         [Begin Time],
    
         [Transaction Name],
    
         [Transaction SID]
    
    FROM
    
        fn_dblog(NULL, NULL)
    
    WHERE
    
        [Transaction ID] = '0000:0000020e'
    
    AND
    
        [Operation] = 'LOP_BEGIN_XACT'
    Current LSN of Transaction Log
    Figure 5 – Current LSN of Transaction Log

    In the above screenshot, we can see the current LSN of the transaction along with details like the time ‘2021/03/15 19:36:59:337’ when a DELETE statement was executed with LSN ‘00000014:0000001a:0001’ under transaction ID ‘0000:0000020e’. To recover the deleted SQL database table data, proceed with the next step.

    Step 6: Recover Deleted Records in SQL Server

    To recover the deleted SQL table records, we will need to convert LSN values from hexadecimal to decimal form.  To do so, add ‘0x’ before the log sequence number as shown in the code below:

    --Restoring Full backup with norecovery.
    
    RESTORE DATABASE RecoverDeletedRecords_COPY
    
        FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords.bak'
    
    WITH
    
        MOVE 'RecoverDeletedRecords' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords.mdf',
    
        MOVE 'RecoverDeletedRecords_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords.ldf',
    
        REPLACE, NORECOVERY;
    
        GO
    
    --Restore Log backup with STOPBEFOREMARK option to recover exact LSN.
    
       RESTORE LOG RecoverDeletedRecords_COPY
    
    FROM
    
        DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords_tlogbackup.trn'
    
    WITH
    
        STOPBEFOREMARK = 'lsn:0x00000014:0000001a:0001'
    Current LSN of Transaction Log
    Figure 6 – Recover Deleted Records

    Now that your data is restored, check whether the deleted records have been recovered. For this, run the following query to see the first ten rows of the table that were deleted (in Step 3 above).

    USE RecoverDeletedRecords_COPY
    
    GO
    
    SELECT * from Employee

    If the deleted table records have not been recovered, try using a SQL recovery tool such as Stellar Repair for MS SQL to get back the deleted data.

    Note: In the code above, replace the path ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords.bak’ with the path where your backup file is located. Also, replace the path of .mdf and .ldf files with the path where you have saved the database files.

    Method 2 – Using SQL Recovery Software

    The SQL recovery software from Stellar® can help you recover all the deleted records in a SQL Server database in a few simple steps. Besides the deleted data, the software can also recover all other objects like views, triggers, stored procedures, etc. while maintaining data integrity.

    Before We Proceed

    Before using the software to restore the records, make sure your system meets the following prerequisites:

    • First, you will need SQL Server installed. You can use any SQL Server installed in Windows.
    • Secondly, make sure you have SQL Server Management Studio (SSMS) installed
    • Stellar Repair for MS SQL software

    Steps to Recover Deleted Table Records using Stellar Repair for MS SQL

    Download the demo version of the SQL recovery software to recover deleted rows or damaged databases:

    Free Download for Windows
    • Launch Stellar Repair for MS SQL software. The software main interface opens with an instruction to stop your SQL Server database and create a copy in a different location. Press OK.
    Stellar Repair for MS SQL Main Interface
    Figure 7 – Stellar Repair for MS SQL Main Interface
    • Browse the SQL database MDF file you want to repair. If you do not know the location of MDF file, click Search to find and select the file:
    Select Database MDF File in Software
    Figure 8 – Select Database MDF File in Software

    Note: If you do not know where your database is located, you can find it in SSMS in database properties, in the data file page (refer to the image below).

    Check database Location in SSMS
    Figure 9 – Check database Location in SSMS
    • After selecting the database file, select the Include Deleted Records option, and then click Repair.
    Include Deleted Records Option
    Figure 10 – Include Deleted Records Option
    • Software prompts to select an appropriate scan mode to repair the database file. Select ‘Standard Scan’ or ‘Advanced Scan’, and then click OK.
    Select Scan Mode
    Figure 11 – Select Scan Mode
    • Click OK when the ‘Repair Complete’ message box opens.
    Repair Complete Message Box
    Figure 12 – Repair Complete Message Box
    • The software shows preview of the recoverable table records. It also restores other db objects like Views, Synonyms, Stored Procedures, Functions, Rules, Defaults, Data Types, Triggers, Sequences, and Assemblies. The software also displays a log report containing information like the database size, collation, path of the mdf file, and list of tables and objects.
    Log Report
    Figure 13 – Log Report
    • Next, click Save on the File menu to save the recovered data.
    save the recovered data
    Figure 14 – Save Database
    • In ‘Save Database’ dialog box, you can choose to save the recovered data in MDF, CSV, HTML or XLS (Excel) file formats. In this example, we will choose MSSQL:
    saving options
    Figure 15 – Saving Options
    • You can create a new database or work with the database online. You can restore in another SQL Server instance and specify the Authentication type, i.e. Windows Authentication or SQL Server Authentication. Also, you can specify the location of the files of the database:
    Choose Authentication Type
    Figure 16 – Choose Authentication Type
    • Click Save.

    The recovered table records and other selected data will be saved in the repaired MDF file.

    Watch the complete working process of the SQL Recovery software:

    Conclusion

    In this article, we learned how to recover deleted records in SQL Server. These solutions can work in any SQL Server version in Windows. If you’ve the Full database backup available, you can try recovering deleted records using the transaction log with Log Sequence Number (LSN). If the backup is corrupted, use Stellar Repair for MS SQL to recover the deleted data in SQL Server or in other formats like Comma Separated Value (CSV) files, HTML, or XLS.

    The SQL database repair software not only helps in recovering deleted rows but also restores the SQL database damaged by virus attack, hardware failure, or other reasons.

    Was this article helpful?

    No NO

    About The Author

    Priyanka Chauhan linkdin

    Priyanka is a technology expert working for key technology domains that revolve around Data Recovery and related software's. She got expertise on related subjects like SQL Database, Access Database, QuickBooks, and Microsoft Excel. Loves to write on different technology and data recovery subjects on regular basis. Technology freak who always found exploring neo-tech subjects, when not writing, research is something that keeps her going in life.

    12 comments

    1. okay, my question is does this app recover the data even if my MS SQL database is hosted in external server and I have deleted some rows by mistake one month ago. Can this app help me to recover my data?

    2. By using Stellar Repair for MS SQL tool, I have easily recovered deleted records in SQL Server 2016 database. This software is absolutely incredible. It just took few minutes to recover deleted records from corrupt MDF file.

      1. We are thankful you trusted on Stellar Repair for MS SQL software to recover deleted records. Share your experience on the Trustpilot website.

      1. Yes, you can restore deleted records by using reliable software Stellar Repair for MS SQL. Follow few above steps and easily recover deleted records in few clicks.

    3. By mistake, I deleted many records in SQL Server. I tried many ways to recover it but none of them work. So, I tried 3rd party software: Stellar Repair for MS SQL which easily recovers the deleted records of the .mdf file.

      I can recommend this software to all users.

    4. I have used this recovery software to recover a large amount of SQL data for my organization. It’s magic for me within a few minutes I got all deleted data.

    5. My Client Accidently Deleted Some Rows From Database Now. Now we are struggling for Almost 2 Days for possible recovery.

      And The Database has no Backup & Running on SQL Server 2008.

    6. I accidentally ran a DELETE command against a table with a wrong WHERE clause. I am using SQL Server. Is there a way that could help me recover the lost data?

    7. I mistakenly deleted records from a SQL Server table. The server is not granting me access to the backup files on the server side.

      Is there any way to get back these records?

      1. Hello Margaret,

        In order to ensure 100% recovery of deleted records, you can try a SQL Database Repair solution by Stellar.

    Leave a comment

    Your email address will not be published. Required fields are marked *

    Image Captcha
    Refresh Image Captcha

    Enter Captcha Here :

    Related Posts

    WHY STELLAR® IS GLOBAL LEADER

    Why Choose Stellar?

    • 0M+

      Customers

    • 0+

      Years of Excellence

    • 0+

      R&D Engineers

    • 0+

      Countries

    • 0+

      PARTNERS

    • 0+

      Awards Received