0

How do I recover deleted rows from a table while having Recovery SIMPLE Model? Database is in Microsoft SQL Server 2014. I have managed to find them in fn_dblog table before data is overwritten with a new one. Can I do anything with it?

SELECT * FROM fn_dblog(NULL, NULL) WHERE Operation = 'LOP_DELETE_ROWS'

I also find out the specific time at which rows were deleted.

USE Databasename
GO
SELECT
[Current LSN],  Operation, [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]
FROM
fn_dblog(NULL, NULL)
WHERE
[Transaction ID] = ‘000:000001f3'
AND
[Operation] = 'LOP_BEGIN_XACT'

I've read SIMPLE Recovery doesn’t support transaction log backup. But is there really no way to restore that data?

Shekar Kola
  • 2,477
  • 2
  • 10
  • 24
SevenFivePE
  • 3
  • 2

1 Answers1

1

I don't think, you get any recommendation on fn_dblog as it's undocumented. So got following options:

  1. Recent Full Backup: Restore recent full backup as copy without overwriting existing database then you should be able to recover particular table or rows within table.
  2. Third-party tools: you can use third-party tools that can directly read your transaction log to recover data from particular transaction, you need to take database offline to read .log file by tool. However, chances are less in this method since the recover model is SIMPLE which truncates the log automatically
Shekar Kola
  • 2,477
  • 2
  • 10
  • 24