How to Recover Deleted Table Records in SQL Server

 A log sequence number (LSN) is a unique identifier for each record in a SQL Server transaction log. The LSN of a log record at which an important event happened can be used to build restore sequences.

To further understand how to restore deleted table entries using the log sequence number method, let's look at an example. In this example, a database and table will be created, and rows will be added, deleted, and information about the destroyed data will be obtained. Finally, the data will be recovered.

How to Use LSN to Retrieve Deleted Records from a SQL Table

We will construct a test database and a table in this part, and then we will perform a DELETE operation on it. Next, we will use LSN to locate deleted data and retrieve it.

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');

Step 2: Insert Data into Table

We have made two tables: one with three columns called "Employee" and the other with the name "RecoverDeletedRecords." We will now execute the following query to add rows to the table:

USE RecoverDeletedRecords; GO INSERT INTO Employee DEFAULT VALUES; GO 25

Step 3: Delete Rows from Table

USE RecoverDeletedRecords Go DELETE Employee WHERE [Sr.No] < 10 GO Select * from Employee

When you are done with deleting the records, then a log is made with your transaction type, an action which you performed along with action Id.

Next we will do to get the information about the deleted rows:
USE RecoverDeletedRecords GO SELECT  [Current LSN],     [Transaction ID],      Operation,      Context,      AllocUnitName FROM     fn_dblog(NULL, NULL) WHERE     Operation = 'LOP_DELETE_ROWS'

Here after executing this command you will get the transaction id and the transaction time when you deleted the records. With the help of transaction time we can get the exact records which were deleted:

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

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'

After executing this command you will get Transaction Time and Transaction Name. Now let's follow the below step to recover the data:

Step 6: Recover Deleted Records in SQL Server

The LSN values must be converted from hexadecimal to decimal form in order to retrieve the deleted SQL database records.  To accomplish this, put "0x" before the log sequence number, as the following code illustrates:

--Restoring Full backup with norecovery. RESTORE DATABASE RecoverDeletedRecords_COPY     FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50\MSSQL\Backup\RecoverDeletedRecords.bak' WITH     MOVE 'RecoverDeletedRecords' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50\MSSQL\Backup\RecoverDeletedRecords.mdf',     MOVE 'RecoverDeletedRecords_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50\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'

Step 6: Now run the command to check

USE RecoverDeletedRecords_COPY GO SELECT * from Employee

Note: In the code above, replace the path ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50\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.

Comments

Post a Comment

Popular posts from this blog

Ag Grid Angular

Image
  Creating a Basic Grid Angular An introduction to the key concepts of AG Grid. Overview  In this tutorial you will: Create a basic grid Load external data to grid Configure columns Configure grid features Format cell values Add custom component to cell Hook into grid events Once complete, you'll have an interactive grid, with custom components and formatted data - Try it out for yourself by  sorting ,  filtering ,  resizing ,  selecting , or  editing  data in the grid: Create a Basic Grid  Complete our  Quick Start  (or open the example below in CodeSandbox / Plunker) to start with a basic grid, comprised of: Row Data:  The data to be displayed. Column Definition:  Defines & controls grid columns. Grid Component:  The  ag-grid-angular  component, with Dimensions, Row Data, and Column Definition attributes import  {  Component  }  from   "@angular/core" ; import  {  AgG...
Read more

.NET Watcher in a .NET Core API

1. Set Up the .NET Core API If you don’t already have an API project, create one: dotnet new webapi -n FileWatcherApi  cd FileWatcherApi 2. Add the FileSystemWatcher Implementation Open the project in your favorite editor (e.g., Visual Studio, VS Code). Create a new service class to manage file watching. For example: Add a new folder named Services . Create a file named FileWatcherService.cs . Implement the FileWatcherService : using System; using System.IO; namespace FileWatcherApi.Services {     public class FileWatcherService     {         private readonly FileSystemWatcher _fileWatcher;         public FileWatcherService(string pathToWatch)         {             if (!Directory.Exists(pathToWatch))             {                 throw new DirectoryNotFoundException($"The directory '{pathToWatch}' does n...
Read more

Power Shell UI Forms

 # Load the required assembly Add-Type -AssemblyName System.Windows.Forms # Create the main form $form = New-Object System.Windows.Forms.Form $form.Text = "Sample PowerShell GUI" $form.Size = New-Object System.Drawing.Size(300, 200) $form.StartPosition = "CenterScreen" # Create a label $label = New-Object System.Windows.Forms.Label $label.Text = "Enter your name:" $label.Location = New-Object System.Drawing.Point(10, 20) $label.Size = New-Object System.Drawing.Size(100, 20) $form.Controls.Add($label) # Create a text box $textBox = New-Object System.Windows.Forms.TextBox $textBox.Location = New-Object System.Drawing.Point(120, 20) $textBox.Size = New-Object System.Drawing.Size(150, 20) $form.Controls.Add($textBox) # Create a button $button = New-Object System.Windows.Forms.Button $button.Text = "Submit" $button.Location = New-Object System.Drawing.Point(10, 60) $button.Size = New-Object System.Drawing.Size(75, 30) $form.Controls.Add($button) # Add...
Read more