Friday, May 27, 2016

Finding a Dropped Table

I recently needed to find a dumped table that was somewhere in backups, but where??? Obviously, the db stores this in the transaction logs, but its really hard to find if you don't know where to look.

A great query for finding this dropped table is this:

SELECT * FROM fn_dblog (NULL, NULL), (SELECT [Transaction ID] AS [tid] FROM fn_dblog (NULL, NULL) WHERE [Transaction Name] LIKE '%DROPOBJ%') [fd] WHERE [Transaction ID] = [fd].[tid];

This will return back everything you need to get this table off of your backup:

[Current LSN], 3 [Operation], 4 [Context], 5 [Transaction ID], 6 [Description]

No comments:

Post a Comment