Have you ever accidentally deleted important data in your SQL Server database? Don’t worry, there’s a way to recover it using database backups. In this article, we will explore how to recover data removed by SQL Delete and SQL Truncate statements using database backups.

Understanding Delete and Truncate Statements

Before we dive into the recovery process, let’s quickly recap how delete and truncate statements work in SQL Server. The delete statement is used to remove specific rows from a table based on a condition, while the truncate statement removes all rows from a table. It’s important to note that delete statements can be rolled back, but truncate statements cannot.

Creating a Test Database Environment

Let’s start by creating a test database environment for our demonstration. We’ll create a new database and two tables: “DeletemyData” and “TruncatemyData”. The “DeletemyData” table will be used for the delete statement, while the “TruncatemyData” table will be used for the truncate statement.

CREATE DATABASE SQLShackDemo;
GO

USE SQLShackDemo;
GO

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

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

Taking a Full Database Backup

Now that we have our database environment set up, let’s take a full database backup. This backup will serve as a restore point in case we need to recover the deleted data later on.

BACKUP DATABASE SQLShackDemo TO DISK = 'c:\temp\SQLShackdemo.bak';

Recovering Deleted Data

Suppose we accidentally delete some data from the “DeletemyData” table using a delete statement. We can use the following query to retrieve the deleted data from the transaction log:

USE SQLShackDemo;
GO

SELECT [Current LSN], [transaction ID] tranID, [begin time], Description, operation, Context
FROM ::fn_dbLog(NULL, NULL)
WHERE [Transaction Name] = 'Delete';

This query will show us the log records related to the delete operation. We can then use the log records to recover the deleted data from the database backup.

Similarly, if we accidentally truncate the “TruncatemyData” table, we can use the following query to retrieve the log records:

USE SQLShackDemo;
GO

SELECT [Current LSN], [transaction ID] tranID, [begin time], Description, operation, Context
FROM ::fn_dbLog(NULL, NULL)
WHERE [Transaction Name] = 'Truncate table';

Once we have the log records, we can restore the database backup in NORECOVERY mode and apply the transaction log backup to recover the deleted data.

Conclusion

Accidentally deleting data in SQL Server can be a nightmare, but with the help of database backups, we can recover the lost data. By understanding how delete and truncate statements work and using the transaction log records, we can successfully recover deleted data. Remember to always create a test environment and avoid performing tests on production databases.