Finding the Deleted table and user info in SQL Server ?

 I showed you how to use a transaction log backup file to identify who deleted an object from a database.   In this tip, I will show you how to find out the actual table name for the object deleted.    

I ran the following code to identify who delete tables from my database in my last tip:

SELECT [Current LSN], [Operation], [Transaction Name], [Transaction ID], SUSER_SNAME ([Transaction SID]) AS DBUser 
FROM fn_dump_dblog (
            NULL, NULL, N'DISK', 1, N'C:\temp\Read_Backup_Logfile_DELETEDemo_Log.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');

When I ran this code, I got the following output:

 Current LSN             Operation                Transaction Name                  Transaction ID DBUser
 ----------------------- ------------------------ --------------------------------- -------------- ------

 00000023:00000100:0001  LOP_BEGIN_XACT           DROPOBJ                           0000:0000032d  TIRU-PC\TIRU

To identify the object_id for the object I drop, I will need to review all the transaction log backup records associated with the DROP TABLE statement, so I can find the object lock transaction.  To do that I need to find all the transaction log backup records with the same Transaction ID value as the DROPOBJ transaction.  From the output above you can see that the Transaction ID value for the DROP TABLE command was “0000:0000032d”.  To find the schema lock information I can run the following code:

SELECT [Transaction ID], [Lock Information]
FROM fn_dump_dblog (
            NULL, NULL, N'DISK', 1, N'C:\temp\Read_Backup_Logfile_DELETEDemo_Log.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 ID] = '0000:0000032d'
              and [Lock Information] like '%ACQUIRE_LOCK_SCH_M OBJECT%';

Transaction ID Lock InformationHere you can see that I used the Transaction ID value I found for the DROPOBJ transaction in the WHERE clause above.   Additionally, this code will constrain the rows returned to only those rows that have text “ACQUIRE_LOCK_SHM_M OBJECT”, in the Lock Information column.    When I run this code, I get the following output:

-------------- -------------------------------------------------------------------------
0000:0000032d  HoBt 0:ACQUIRE_LOCK_SCH_M OBJECT: 0:565577053:0 

In this output, the object_id for the object deleted can be found within the Lock Informationcolumn value.  In this example that object_id value is “565577053”.
In order to find out the object name for object_id “565577053”  I will have to restore my database prior to when I actually dropped the table.   I will do this by running the following code:

ALTER DATABASE [Read_Backup_Logfile_Demo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE [Read_Backup_Logfile_Demo] 
FROM DISK = N'C:\Temp\Read_Backup_Logfile_DELETE_Demo.bak'
WITH REPLACE;

In this code, I restored my Read_Backup_LogFile_Demo database from the full backup I took of my database just prior to the DROP TABLE command.  For simplicity, I restored my database over the top of my existing database.  In reality you might want to perform this restore to a dummy database, if you want to retain the database with the deleted table.
Once my database is restored, I can find the name of the object that was deleted by running the following code:

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

When I run this code, I get the following output:

name                                          object_id
--------------------------------------------- -----------
Read_Backup_Log                               565577053

As you can see the object I deleted was “Read_Backup_Log”.
By using my last tip and this tip you can find who dropped objects in my database, and which objects were dropped.   Next time you need to look at transactions in a backup log file you can use the fn_dump_dblog function.