How to Use LOG backup to Find Deleted Objects in SQL Server

In this article, we will learn how to use SQL Server Transaction Log backup files and identify

 1) Name of objects that were deleted from the database

 2) The User who deleted the database objects

SQL Server does not support object-level restore. So, to restore the deleted object, you must restore the entire database with different name and then import the deleted object into the original database.

Step by Step

Use LOG backup To Find Deleted Objects In SQL ServerStep 1: Create a database and add some tables. As it is just a demo, tables are empty. If needed, you can add content to tables. As per the script, the directory for MDF and LDF file is E:\Program Files\MSSQL12.MSSQLSERVER\MSSQL\DATA\. You can change it as per your requirement.

USE [master]
GO
CREATE DATABASE [TestDatabase]
 CONTAINMENT = NONE
 ON PRIMARY 
( NAME = N'TestDatabase', FILENAME = N'E:\Program Files\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDatabase.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TestDatabase_log', FILENAME = N'E:\Program Files\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDatabase_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
USE [TestDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table1](
 [C1] [nchar](10) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table2](
 [C1] [nchar](10) NULL
) ON [PRIMARY]
GO

Step 2: Take a full backup of the database. According to the script, the backup directory is ‘E:\Program Files\MSSQL12.MSSQLSERVER\MSSQL\Backup. You can change it as per your requirement.

Backup database [TestDatabase]
to disk = 'E:\Program Files\MSSQL12.MSSQLSERVER\MSSQL\Backup\TestDatabase_Full.bak'

Step 3: Create a new table and then take a log backup.

CREATE TABLE [dbo].[Table3](
   [C1] [nchar](10) NULL
) ON [PRIMARY]
GO

Backup log [TestDatabase]
to disk = 'E:\Program Files\MSSQL12.MSSQLSERVER\MSSQL\Backup\TestDatabase_Log1.bak'

Step 4: Drop the last table and take a log backup.

Drop TABLE [dbo].[Table3]

Backup log [TestDatabase]
to disk = 'E:\Program Files\MSSQL12.MSSQLSERVER\MSSQL\Backup\TestDatabase_Log2.bak'

Step 5: With the table dropped, we will now use the log backup and identify the user who dropped the table and also identify the transaction ID.

Declare @backupfile as nvarchar(200)
set @backupfile = N'E:\Program Files\MSSQL12.MSSQLSERVER\MSSQL\Backup\TestDatabase_Log2.bak'
SELECT [Current LSN], [Operation], [Transaction Name], [Transaction ID], SUSER_SNAME ([Transaction SID]) AS DBUser FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'E:\Program Files\MSSQL12.MSSQLSERVER\MSSQL\Backup\TestDatabase_Log2.bak',DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) WHERE [Transaction Name] LIKE ('DROPOBJ');

From this result set, the last column shows the User who deleted the object. The transaction ID from this result will be used in the next step.

The Transaction ID

Step 6: The transaction ID ‘0000:000002f2’ from the previous step is used here

Declare @backupfile as nvarchar(200)
set @backupfile = N'E:\Program Files\MSSQL12.MSSQLSERVER\MSSQL\Backup\TestDatabase_Log2.bak'
SELECT [Transaction ID], [Lock Information] FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, @backupfile,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) WHERE [Transaction ID] = '0000:000002f2' and [Lock Information] like '%ACQUIRE_LOCK_SCH_M OBJECT%';

From the result set, the last column gives the ID of the object that was deleted.The Last Column

Step 7: Restore the database with a different name and then you can query the sys.objects table to identify the name of the object that was deleted.

Restore database [TestDatabase_Backup]
from disk = 'E:\Program Files\MSSQL12.MSSQLSERVER\MSSQL\Backup\TestDatabase_Full.bak'
with move 'TestDatabase' to 'E:\Program Files\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDatabase_Backup.mdf',
move 'TestDatabase_log' to 'E:\Program Files\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDatabase_Backup_log.ldf', norecovery, stats = 1
go
restore log [TestDatabase_Backup]
from disk = 'E:\Program Files\MSSQL12.MSSQLSERVER\MSSQL\Backup\TestDatabase_Log1.bak'
with norecovery
go

USE [TestDatabase_Backup];
GO
SELECT name, object_id from sys.objects
WHERE object_id = '277576027';

Identify The Name Of The Object That Was Deleted

Backup files are only option to restore a deleted object. They also help you to perform a mdf fix when data disaster occurs unexpectedly.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including recover pst and excel recovery software products. For more information visit www.datanumen.com