|
20151029 [FIND DELETED OBJECT THROUGH TRNSACTION LOG]
Posted by
nigel
on
Thursday, October 29, 2015 (PST)
SQL;T-SQL
|
|
The following query will return user name and transaction id, and detail transaction info.
You will not be able to find the deleted object name.
SELECT
SUSER_SNAME([Transaction SID]),
[Transaction ID],
*
FROM
fn_dblog(NULL, NULL)
WHERE
[Transaction Name] = 'DROPOBJ'
To find out deleted object name. You need to use the following query to find it from the backup database with the transaction id.
SELECT
TOP (1) [Lock Information]
FROM
fn_dblog (NULL, NULL)
WHERE
[Transaction Id] = '0000:006d8c63'
AND [Lock Information] LIKE '%SCH_M OBJECT%'
|
|