How to use database backups to recover data after SQL Delete and SQL Truncate statements

This article explores the recovery of data removed by SQL Delete and SQL Truncate statements using SQL database backups.

Before you go further with this article, go through the following articles to understand how delete and truncate statements work in detail.

Create a test database environment

Let’s create a database environment for this article demonstration.

Create a database

1
2
3
4

CREATE DATABASE SQLShackDemo;
GO
USE SQLShackDemo;
GO
Create a SQL table. We will use a delete statement for this table

1
2
3
4
5

CREATE TABLE DeletemyData
(id INT IDENTITY(1, 1),
[Name] VARCHAR(40)
);
GO
Create another SQL table. We will use the truncate statement for this table

1
2
3
4
5

CREATE TABLE TruncatemyData
(id INT IDENTITY(1, 1),
[Name] VARCHAR(40)
);
GO

Rollback data demonstration

At this point, we have a SQL database with two empty tables [DeletemyData] and [TruncatemyData]. It is a new database, and we do not have any database backup for it. Let’s take a full database backup using the following query. You can also use the backup wizard in SSMS to do it graphically. It is a small database, so no need to worry about backup compression.

1	Backup database SQLShackdemo to disk='c:\temp\SQLShackdemo.bak'

Execute the following query to retrieve database backup history from the msdb system database.

SELECT bs.database_name AS DatabaseName,

CASE bs.type

WHEN 'D'

THEN 'Full'

WHEN 'I'

THEN 'Differential'

WHEN 'L'

THEN 'Transaction Log'

END AS BackupType,

CAST(bs.first_lsn AS VARCHAR(50)) AS FirstLSN,

CAST(bs.last_lsn AS VARCHAR(50)) AS LastLSN,

bmf.physical_device_name AS PhysicalDeviceName,

CAST(CAST(bs.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS BackupSize,

bs.recovery_model AS RecoveryModel

FROM msdb.dbo.backupset AS bs

INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bs.media_set_id = bmf.media_set_id

WHERE bs.database_name = 'SQLShackdemo'

ORDER BY backup_start_date DESC,

backup_finish_date;

It gives first and last log sequence number (LSN) details as well.

Now, insert ten records in both tables.

DECLARE @id INT;

SET @ID = 10;

WHILE(@ID > 0)

BEGIN

INSERT INTO DeletemyData([Name])

VALUES('Delete Data' + ' ' + CAST((@ID) AS VARCHAR));

SET @ID = @ID - 1;

END;

DECLARE @id INT;

SET @ID = 10;

WHILE(@ID > 0)

BEGIN

INSERT INTO TruncatemyData([Name])

VALUES('Truncate data' + ' ' + CAST((@ID) AS VARCHAR));

SET @ID = @ID - 1;

END;

Now, open two query windows in SSMS.

In the first query window, delete a few records from [DeletemyData] table.

1 2	DELETE FROM WHERE id < 5;

In the second query window, truncate the SQL table. We cannot specify the WHERE clause in truncate, so it removes all records from a table.

1	Truncate table TruncatemyData

Verify records in both the tables. We have zero records in the [TruncatemyData] table while [DeletemyData] contains six records.

We can use undocumented function fn_dblog to get information about delete and truncate statements from the transaction log. Refer to this article, How to continuously read Transaction log file data directly in a SQL Server database with fn_dblog and fn_dump_dblog for more detail.

We can filter transaction log entry using the delete and truncate table clause in the where condition.

USE SQLShackDemo;

SELECT [Current LSN],

[transaction ID] tranID,

[begin time],

Description,

operation,

Context

FROM ::fn_dbLog(NULL, NULL)

WHERE [Transaction Name] IN('Delete', 'Truncate table');

It shows two transaction log records. We can segregate transactions using the description column. As per the following screenshot, the first entry is for delete while later entry is for the truncate statement. You can note down the begin time of these transaction.

Delete: 2020/02/26 19:44:27:440
Truncate: 2020/02/26 19:44:45:830

In the full recovery model, transaction log backup maintains the log chain. We can also do point in time recovery using transaction log backup. Let’s execute the following query for log backup. It takes backups of all data changes.

1	Backup log SQLShackdemo to disk='c:\temp\SQLShackdemo_log.trn'

View database backup history using the above query from the msdb database. It shows two entries – full and transaction log backup.

Recover data deleted from a SQL Delete statement

Now, suppose you require to recover the deleted data from the existing backups. We will create a new database from the full backup. We need to restore a backup in NORECOVERY mode so that we can apply further transaction log backup on it.

USE [master];

RESTORE DATABASE [SQLShackDemo_restore] FROM DISK = N'C:\TEMP\SQLShackdemo.bak' WITH FILE = 1,

MOVE N'SQLShackDemo' TO N'C:\sqlshack\Demo\SQLShackDemo.mdf',

MOVE N'SQLShackDemo_log' TO N'C:\sqlshack\Demo\SQLShackDemo_log.ldf',

NORECOVERY, NOUNLOAD, STATS = 5;

Database [SQLShackDemo_restore] is in restoring mode. We cannot access the database while it is in restoring mode.

In the article, we learned about Point in Time Recovery with SQL Server using the STOPAT parameter of Restore log command. We can specify a specific timestamp or LSN in the STOPAT parameter.

Similarly, we can use STOPBEFOREMARK in a restore log statement. As its name suggests, this parameter instructs SQL Server to stop database restore once it reaches a specific timestamp or LSN. You can refer to Microsoft docs for more details on STOPBEFOREMARK.

Convert HEX LSN value in decimal format

In the output of fn_dblog above, we have LSN for delete and truncate statements.

Delete LSN: 00000026:00000230:0001
Truncate LSN: 00000026:00000268:0001

LSN values in fn_dblog are in the hexadecimal format. Restore log command requires LSN in a decimal format. We can use the following query to convert it into the decimal format. Here, specify the LSN in the @LSN parameter.

DECLARE @LSN VARCHAR(22), @LSN1 VARCHAR(11), @LSN2 VARCHAR(10), @LSN3 VARCHAR(5);

SET @LSN = '00000026:00000230:0001';

SET @LSN1 = LEFT(@LSN, 8);

SET @LSN2 = SUBSTRING(@LSN, 10, 8);

SET @LSN3 = RIGHT(@LSN, 4);

SET @LSN1 = CAST(CONVERT(VARBINARY, '0x' + RIGHT(REPLICATE('0', 8) + @LSN1, 8), 1) AS INT);

SET @LSN2 = CAST(CONVERT(VARBINARY, '0x' + RIGHT(REPLICATE('0', 8) + @LSN2, 8), 1) AS INT);

SET @LSN3 = CAST(CONVERT(VARBINARY, '0x' + RIGHT(REPLICATE('0', 8) + @LSN3, 8), 1) AS INT);

SELECT CAST(@LSN1 AS VARCHAR(8)) + CAST(RIGHT(REPLICATE('0', 10) + @LSN2, 10) AS VARCHAR(10)) + CAST(RIGHT(REPLICATE('0', 5) + @LSN3, 5) AS VARCHAR(5));

Using the above query, we get the following LSN values for both delete and truncate statements.

Delete LSN: 38000000056000001
Truncate LSN: 38000000061600001

Now, run the restore log query using the STOPBEFORMARK parameter. This query stops the processing of database restores before the specified LSN.

1 2	Restore log [SQLShackDemo_restore] FROM DISK = N'C:\TEMP\SQLShackdemo_log.trn' with STOPBEFOREMARK ='lsn:38000000056000001'

We get the following output of above RESTORE LOG command.

Once the log backup is restored, we can access the database. Verify the records in the [DeletemyData] table, and it shows data is available. We can use this data and export to an original database using export and import wizard.

Recover data deleted from a SQL Truncate statement

We have recovered data deleted by a delete statement. Let’s perform a similar test for recovering data from the truncate statement.

Restore full database backup in NORECOVERY mode

USE [master];

RESTORE DATABASE [SQLShackDemo_restore_1] FROM DISK = N'C:\TEMP\SQLShackdemo.bak' WITH FILE = 1,

MOVE N'SQLShackDemo' TO N'C:\sqlshack\Demo\SQLShackDemo.mdf',

MOVE N'SQLShackDemo_log' TO N'C:\sqlshack\Demo\SQLShackDemo_log.ldf',

NORECOVERY, NOUNLOAD, STATS = 5;

Restore transaction log backup with the STOPBEFOREMARK parameter. Specify the LSN we derived above from the hex.

1
2

Restore log [SQLShackDemo_restore_1] FROM DISK = N'C:\TEMP\SQLShackdemo_log.trn'
with STOPBEFOREMARK ='lsn:38000000061600001'
Verify data in the [TruncatemyData] table

Conclusion

In this article, we recovered deleted data using SQL Delete and SQL Truncate statements with the help of database backups. You should not perform any tests in the production database. You can create a test environment and explore data recovery.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023