Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Database User and Programming Tips

Posted July 3, 2017

WEBINAR: On-demand webcast

How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >

How to Find Out Which Database Object Got Deleted

By Greg Larsen

In my last tip, 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  Greg-PC\GREG

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%';

Here 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:

Transaction ID Lock Information
-------------- -------------------------------------------------------------------------
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 Information column 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.

See all articles by Greg Larsen



Database User and Programming Tips Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.