Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
SQL server point in time restore could come to your rescue here, see msdn.microsoft.com/en-us/library/ms190982(v=sql.90).aspx –  hermiod Oct 22 '11 at 10:29
1  
What recovery model is the database in? Has a full backup ever been taken (even if now lost) as this will determine whether your database is in auto transaction log truncate mode or not. –  Martin Smith Oct 22 '11 at 10:46
2  
possible duplicate of get back deleted record –  Mark Storey-Smith Oct 22 '11 at 10:50
1  
@RPK - Chances are you are hosed then. In theory the data probably still exists in the data file if there hasn't been any other activity since the erroneous delete but I'm not aware of any utility that can help recover this. –  Martin Smith Oct 22 '11 at 11:06
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
show 11 more comments

2 Answers 2

No. There is no way to recover the data.

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

share|improve this answer
    
With the help of sys.fn_dblog, there is hope. –  Nick Chammas Mar 20 '12 at 17:31
add comment

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.

share|improve this answer
    
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 6 '13 at 11:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.