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.
3 Answers
Check this. It is explained how to recover the database.
-
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: sql-developer2dba.blogspot.com/2010/04/… Jan 7, 2011 at 2:53
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.
-
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.– yrushkaJan 4, 2011 at 10:37
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.