5

We had a drive die and lost the ldf file, but the mdf file is in tact. Is there a process for re-connecting to the mdf file, considering the ldf lost? I have searched without much luck.

Tom Lianza
  • 331
  • 1
  • 3
  • 11

3 Answers3

5

Yes, you can try detaching the database and attaching it using the sp_attach_single_file_db system stored procedure. This will generate a new transaction log file for you. If this procedure fails, you will need to restore from your backup.

Nick Kavadias
  • 10,796
  • 7
  • 37
  • 47
mrdenny
  • 27,174
  • 4
  • 41
  • 69
  • if there was an active transaction than SQL will not be able to create a new log file knowing that a transaction was not committed and the attach will fail. It will be much harder after a detach to attach a corrupted database. The easy way is to use emergency mode. Much safer. – yrushka Jan 04 '11 at 10:37
1

but the mdf file is in tact

Yes, but not consistent. Data files are not kept consistent between checkpoints - this is why you need an LDF file.

I suggest a backup. While you MAY be lucky with the sp_attach_single_file_db it is 99.9% a data loss involved.

Nick Kavadias
  • 10,796
  • 7
  • 37
  • 47
TomTom
  • 51,649
  • 7
  • 54
  • 136
1

Check this. It is explained how to recover the database.

yrushka
  • 600
  • 2
  • 9
  • I'm marking this correct because we had Microsoft Support on the box, and this was basically what they did. Plus they executed the rebuild log command here: http://sql-developer2dba.blogspot.com/2010/04/rebuild-log-in-sql-2005-sql-server.html – Tom Lianza Jan 07 '11 at 02:53