Post
Ask Question

Recover Deleted Table Data "without Backup" in SQL Server

This article is a sub-topic of the previous article, so we have the structure of articles like this.

Introduction

In the previous article, Restore a Table from a Backup Database through Restore/Export. We recovered table records from a previous backuped database because the deleted records were still in the previous backuped database. Now, we discuss the situation; what if the deleted records in a table were not in the previous backup?

  • Introduction
  • Problem
  • Solution
  • Demo Implementation
    1. Create a Database and a Table
    2. Backup Database
    3. Insert Data
    4. Delete Some Data
    5. Backup Datalog
    6. Run Function fn_dblog To Get Transaction Log
    7. Run Function fn_dblog again to Get LSN
    8. Restore the Database from backup
    9. Restore the Database backlog from LSN
    10. Check Results

Note

  1. In the title, we use double quotes to "without Backup". In fact, it is not exactly true. We do need a pre-existing backup file. Otherwise, we cannot finish our job. However, the info we wanted is not in the restored backup. Instead, the info is in the backup log file. We will discuss the details later.
  2. Due to this misunderstanding, I viewed a lot of articles about this topic and tried the ways they introduced to make the recovery happen. I failed a lot and more than about a month. When I completed the process, I realized the recovery procedure was very picky. Therefore, I will make the detailed recovery implementation in this article in the next section, and discuss the logic of the Recovery Function and its properties in another article, say, titled as "Use Transaction Log to Recover the Deleted or Modified Data".

Problem

In the previous article, Restore a Table from a Backup Database through Restore/Export, the situation is

Demo as

Situation 1

  1. We have a database created at Time A
  2. We made a lot of running, including Create, Dro, Insert, Update, and Delete to Manipulate the database, then made a backup at Time B
  3. We accidentally deleted one table or table records after backup at Time B.
  4. We wanted to restore the table or records at Time C. However, we could not restore the previous backup because, besides the deleted table or the wanted restored table, the database changed a lot in another part.
  5. At this point, we need to restore only one table from the previous backup, and actually, the table was in the backup. This has been done by the previous article.

The current issue is that in Step 3, the deleted table or table records were created or inserted after the previous backup at Time B, i.e.there is no info about this table and records in the previous backup. Therefore, we cannot restore the required info from the previous backup.

Demo as

At this point, how can we recover the deleted table or table records.

Solution

Recovery Tool

We will use the Microsoft function, fn_dblog(),to recover the infomation between Time B and Time C, i.e., after the last backup till the current. The info is hidden in the log data file, In this case, RecoverDeletedData.Log.ldf:

We will discuss the details of function, fn_dblog(), and its properties, while in this article, we will mainly concentrate on implementation to recover the deleted Table Records "without back file". Actually, we talk about without backup, the statement is quite confusing, because for complete the recovery, we do need a previous backup file at Time B, without it, we cannot make our recovery.

Recovery Process --- includes two Steps:

  1. Restore the previous fully backup file with NORECOVERY,
  2. Add the Restored Back up LOG file, we back uped from Time B to Time C, into the previous restored full backup.

Framework of the Recovery:

DEMO Implementation

The following will be the step by step implementation. Following the instructions, it will absolutely work.

1. Create a Database and a Table

USE Master    
GO    
CREATE DATABASE [RecoverDeletedData]    
   
GO 
USE RecoverDeletedData  
GO

/****** Object:  Table [dbo].[Resources]    Script Date: 9/10/2023 8:18:25 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Resources](
	[Client_ID] [int] NOT NULL,
	[Resource_ID] [int] NOT NULL,
	[Language_Code] [int] NOT NULL,
	[Entry_Type] [int] NOT NULL,
	[Resource_Description] [nvarchar](4000) NULL,
	[Constant_Name] [nvarchar](400) NULL
) ON [PRIMARY]
GO

2. Backup Database

USE RecoverDeletedData     
GO     
BACKUP DATABASE [RecoverDeletedData]     
TO DISK = N'C:\RecoverDeletedData\RDDFull.bak'       
GO

Recover Delete

We can make a backup by GUI:

Select a page

complete successful

3. Insert Data

USE RecoverDeletedData     
GO     

INSERT INTO dbo.Resources VALUES
	(0, 400000, 1, 2, '', null),
	(0, 400000, 7, 2, '', null),
	(0, 400000, 2, 2, '', null),
	(0, 400001, 1, 2, '', null),
	(0, 400001, 7, 2, '', null),
	(0, 400001, 2, 2, '', null),
	(0, 400002, 1, 2, '', null),
	(0, 400002, 7, 2, '', null),
	(0, 400002, 2, 2, '', null)

USE RecoverDeletedData  
GO     
Select * from Resources

4. Delete Some Data

USE RecoverDeletedData     
GO     

DELETE FROM dbo.Resources
WHERE	Resource_ID = 400000
	OR	Resource_ID = 400001

USE RecoverDeletedData  
GO     
Select * from Resources

Recover data

5. Restore the Database from the backup with NORECOVERY

USE RecoverDeletedData     
GO     
    
RESTORE DATABASE RecoverDeletedData_COPY FROM     
DISK = 'C:\RecoverDeletedData\RDDFull.bak'     
WITH     
MOVE 'RecoverDeletedData' TO 'C:\RecoverDeletedData\RecoverDeletedData_back.mdf',     
MOVE 'RecoverDeletedData_log' TO 'C:\RecoverDeletedData\RecoverDeletedData_log_back.ldf',     
REPLACE, NORECOVERY;     
GO

we need to restore a FULL database backup to another location (database or file), with the NORECOVERY option enabled in order to add transaction log backup.

6. Run Function fn_dblog To Get Transaction Log

USE RecoverDeletedData    

GO     
Select [Current LSN], [Transaction ID], Operation, Context, AllocUnitName     
FROM     
fn_dblog(NULL, NULL)    
WHERE Operation = 'LOP_DELETE_ROWS'

Transaction Log

You can see the current LSN for the DELETE operation against the Transaction Id ‘0000:00000368’ Now, to restore the data, we need to find the LSN of the beginning of the transaction – under which the table records were deleted. For this, we will use the ‘LOP_BEGIN_XACT’ operation.

7. Run Function fn_dblog again to Get LSN

USE RecoverDeletedData     
GO     
    
SELECT    
[Current LSN], Operation, [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]    
FROM    
fn_dblog(NULL, NULL)    
WHERE    
[Transaction ID] = '0000:00000368'    
AND    
[Operation] = 'LOP_BEGIN_XACT'

LSN

The DELETE operation was started at [Begin Time] for the column value with Current LSN 00000025:000001a2:0001.

 8. Backup Datalog

GO     
BACKUP LOG [RecoverDeletedData]     
TO DISK = N'c:\RecoverDeletedData\RDDTrLog.trn'

9. Restore the Database backlog from LSN

USE RecoverDeletedData     
GO     
RESTORE LOG RecoverDeletedData_COPY   
FROM DISK = N'C:\RecoverDeletedData\RDDTrLog.trn'   
WITH STOPBEFOREMARK = 'lsn:0x00000025:000001a2:0001'

Note: The LSN value, '00000025:000001a2:0001', is in hexadecimal format. To restore the deleted records, we need to convert the value to decimal by adding ‘0x’ before the current log sequence number.

10. Check Results

Current Database: We can see the records are after deleted:

Recovered Database: The deleted records are recovered:

 

References:


Similar Articles