0

Having accidentally nullified a column in MS SQL 2012, I'm looking at how to use fn_dblog for the first time. I had previously backed up the table, and deleted it this morning. I am using full recovery mode (code below for anyone in the future who would like to find out):

SELECT name, recovery_model_desc  
   FROM sys.databases  
      WHERE name = 'model' ;  
GO

Is it possible to rollback a DROP TABLE transaction that was committed within the past 12 hours?

I found this transaction which seems to be exactly what I want. But only the last 3431 rows are found:

SELECT [Current LSN],
       [Operation],
       [Transaction ID],
       [Parent Transaction ID],
       [Begin Time],
       [Transaction Name],
       [Transaction SID]
FROM fn_dblog(NULL, NULL)
WHERE [Operation] = 'LOP_BEGIN_XACT'

How can I return earlier transactions using this query?

I am in unfamiliar territory here. What else should I be thinking of?

  • How do I know if logs exist and haven't been truncated?
  • Is it easier to reinstate a table vs a column deletion?

  • What are the dangers of using fn_fblog? On a blog post I found this (https://raresql.com/2013/04/15/sql-server-undocumented-function-fn_dblog/):

    "No doubt fn_dblog is one of the helpful undocumented functions but do not use this function in the production server unless otherwise required." What is the reason for this?

=== EDIT ===

On a side note, a very helpful article is here: A very helpful introduction to MS SQL logging: http://www.sqlshack.com/reading-sql-server-transaction-log/

Zach Smith
  • 8,458
  • 13
  • 59
  • 133
  • What recovery model is the database in? Do you have any backups with the data so you don't have to resort to trying this? – Martin Smith Oct 14 '16 at 17:35
  • It is using Full recovery mode. If there is any risk of losing more data than it is not worth it since the lost column can be restored with several hours of (someone else's) time. By re-importing CSVs – Zach Smith Oct 14 '16 at 17:40

0 Answers0