Rajendra Gupta
View sample data

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

March 4, 2020 by

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

  • Create a SQL table. We will use a delete statement for this table

  • Create another SQL table. We will use the truncate statement for this table

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.

Backup database

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

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

log sequence number

Now, insert ten records in both tables.

View sample data

Now, open two query windows in SSMS.

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

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.

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

Verify 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.

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

Output of undocumented function fn_dblog

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.

Backup log

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

View database backup history

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.

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

Restored database

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.

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.

We get the following output of above RESTORE LOG command.

Convert HEX LSN value in decimal format

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 Delete statement

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

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

  • Verify data in the [TruncatemyData] table

    Recover data deleted from a SQL Truncate statement

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.

Rajendra Gupta
251 Views