6

My client has accidentally deleted all the records of a table from a test database. This test database is in SQL Server 2005 Express and we don't have a backup.

Is it possible to restore the deleted rows from the transaction log (.ldf) file? If yes, how?

Nick Chammas
  • 14,670
  • 17
  • 75
  • 121
RPK
  • 1,425
  • 6
  • 20
  • 39
  • 2
    possible duplicate of get back deleted record – Mark Storey-Smith Oct 22 '11 at 10:50
  • @MartinSmith Recover Model is set to 'Simple' by default. – RPK Oct 22 '11 at 11:03
  • Too bad you are not using Oracle, you could just flashback the table/database to a specific point in time (provided flashback is on): "FLASHBACK mytable TO TIMESTAMP (SYSDATE -1)" http://download.oracle.com/docs/cd/B12037_01/server.101/b10759/statements_9012.htm – Robert Durgin Oct 23 '11 at 12:53
  • SQL server point in time restore could come to your rescue here, see http://msdn.microsoft.com/en-us/library/ms190982(v=sql.90).aspx – hermiod Oct 22 '11 at 10:29
  • Is Point-In-Time restore enabled by default even in SQL Server 2005 Express Edition? – RPK Oct 22 '11 at 10:42
  • To use point in time recovery the DB has to be in the full or bulk logged recovery model. – StanleyJohns Oct 22 '11 at 13:22
  • Martin Smith - Because he is restoring to a point in the past, the backup can be performed after the deletion has occurred. When the restore occurs, the user defines a point in time prior to the deletion and it will only restore as far as that. I have used this strategy successfully in the past. @stanley - Good catch on recovery mode, I should've mentioned that. – hermiod Oct 22 '11 at 19:48
  • Say you make a mistake at 12.00, you can take the backup after this time and then tell it to just restore up to 11.59. As Stanley said, this is dependent on your having full recovery mode on but it does work. The last time I used this, someone had deleted a batch of rows from a single table. We took a backup afterwards, restored it to a separate database up to a point in time before the incident occurred, and then just copied and pasted the rows from the backup to the original via SQL Management Studio. We could have restored over the live database if we wanted to. – hermiod Oct 23 '11 at 21:30
  • -1 for this. There is no backup and DB is in simple recovery. – gbn Oct 24 '11 at 15:07
  • @gbn down vote is a bit unfair as the comment stating that the database was in simple mode (meaning my solution wouldn't work) was made 30 minutes after my initial post. – hermiod Oct 24 '11 at 17:40
  • @Hermiod: It would show an edit then in the question. It doesn't. See this for example: http://dba.stackexchange.com/q/5666/630 -1 stands – gbn Oct 24 '11 at 18:24
  • 1
    The 3rd comment against the initial post is where RPK first mentions being in simple recovery mode. This comment was posted 30 minutes after my answer. The initial post only mentions absence of a backup, which is not an issue with PiT restore if you are in a recovery mode other than Simple. In short, at the time I posted my answer, the currently available information did not exclude point-in-time restore therefore it was a valid answer. – hermiod Oct 24 '11 at 19:11

2 Answers2

9

If your database is in full recovery mode you can also try third party tools such as ApexSQL Log or SQL Log Rescue.

These tools will attempt to read your transaction log and reconstruct statements.

You can also try reading transaction log manually using fn_dblog function but it’s going to be complex since this is not a well-documented function.

Gunter Wesley
  • 91
  • 1
  • 2
  • I just read through all the comments and saw that you are in a simple recovery mode. In that case chances for recovery are really minimal – Gunter Wesley Jun 06 '13 at 11:32
8

No. There is no way to recover the data.

The LDF is most likely useless even with a 3rd party log reader tool.

gbn
  • 69,809
  • 8
  • 163
  • 243