How to Recover Deleted Records in SQL Server? - Easy Tips

In the world of databases, multiple accidents happen, such as you might accidentally delete a record that holds valuable information. But fear not! SQL Server has got your back with its powerful recovery features. This guide will walk you through the various steps to recover deleted records in SQL Server and get your data back on track. Moreover, the Cigati SQL Recovery Tool helps to repair corrupted files.

Try a Free Demo | Buy Now

Sometimes kinds of stuff can get complex while managing important information. SQL Server comes up with different versions -? 2019, 2017, 2016, 2014, 2012, 2008, and even 2005. No matter which version you're using, restoring process of the records is necessary for further benefits. Now, it's time to discuss the possible solutions to Recover Deleted Rows from SQL Server Table.

How to Recover Deleted Records in SQL Server?

Have you ever accidentally deleted something important from your database? No worries, we've got a way to help you recover it. Let's dive into the first procedure of recovering deleted records using a Log Sequence Number (LSN).

Think of LSN as a unique code for each action your database takes. Every time something significant happens, your database gives it a unique LSN.

NOTE: If your Log file is corrupted or missing, this approach might not work. But no worries, there are other methods to try.

We've got an exclusive example that'll help you wrap your head around using LSN. You create a table, put some data in it, and then accidentally delete some rows. Each action in the database gets an LSN. When you delete stuff, that action gets an LSN too. Now, you can check the LSNs in your transaction log to receive when the deletion happened. LSN will help you to go back to the moment before the deletion using the transaction log. Once the database is back in time, you can use special commands (remember, we call them queries) to grab the deleted data.

Steps to Recover Deleted Table Records in SQL Server:

Here, we'll roll up our sleeves and do some hands-on work to understand how this LSN really works. Don't worry! We'll take it step by step and keep it super simple.

Step 1: Creating a Test Database:

Implement the following command to create a database ‘RecoverDeletedRecords’ and a name of table ‘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’);        

Step 2: Insert Data into The Table

Moving ahead, we’ve created a named table ‘Employee’ and ‘RecoverDeletedRecords’ with three columns. Now, we will insert rows into the new table by running the following command:

USE RecoverDeletedRecords;

GO

INSERT INTO Employee DEFAULT VALUES;

GO 100        

It will help users to Recover Deleted Rows in SQL Server.?

Step 3: Remove Rows From Table

Now let’s remove some rows by implementing the following querry:

USE RecoverDeletedRecords

Go

DELETE Employee

WHERE [Sr.No] < 10

GO

Select * from Employee        

Step 4: Finding the Deleted Data:

After that, we will move further to get detailed information about the removed rows:

USE RecoverDeletedRecords

GO

SELECT

 [Current LSN],   

 [Transaction ID],

     Operation,

     Context,

     AllocUnitName

FROM

    fn_dblog(NULL, NULL)

WHERE

    Operation = ‘LOP_DELETE_ROWS’        

Step 5: Acquire the Log Sequence Number of the LOP_BEGIN_XACT Log Record?

The following section will use the transaction ID to receive 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’        

To Recover Deleted Data in SQL Server, go with the following stage.?

Step 6: Bringing Back the Deleted Data:?

We will convert LSN values from hexadecimal to decimal form.? After that, add ‘0x’ before the log sequence number, as mentioned 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’        

Here is the entire command to recover deleted table records in SQL Server. If you’re not able to bring them back, it simply indicates your file is damaged. To overcome the corruption from your SQL Database file, opt for the Cigati SQL Recovery Tool. It will restore the actual data as it is without losing the valuable data.

End Words

So, when records disappear and your database feels like it's in a bit of a mess, don't worry! SQL Server has your back. No matter which version you're using, you can recover deleted records in SQL Server and get back to your routine.