I made these several articles as a series as Database Recovery:
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
- Create a Database and a Table
- Backup Database
- Insert Data
- Delete Some Data
- Backup Datalog
- Run Function fn_dblog To Get Transaction Log
- Run Function fn_dblog again to Get LSN
- Restore the Database from backup
- Restore the Database backlog from LSN
- Check Results
Note
- 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.
- 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

- We have a database created at Time A
- We made a lot of running, including Create, Dro, Insert, Update, and Delete to Manipulate the database, then made a backup at Time B
- We accidentally deleted one table or table records after backup at Time B.
- 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.
- 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:
- Restore the previous fully backup file with NORECOVERY,
- 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

We can make a backup by GUI:


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







Join the conversation! Your thoughts help the community grow.