I ran into this issue the other day, and thought that I’d blog about it. I had copied a database from a co-worker (MDF files only, unbeknown to me) that I needed for my work. However, he forgot to include the Log file for the associated database in the backup that I received(!) Consequently, when I went to restore the database onto my local machine, I was greeted with a very ‘friendly’ message from SQL Server saying that my database could not be restored.
If this had been any other situation, I would have just gone over to my coworker and asked him to give me a copy of the Log file as well (after having chastised him for giving me a bum backup). However, when this situation occurred, we were at two different physical locations, and I didn’t have a way to get over to where he was.
After googling around for a little bit, I ran across a TERRIFIC post that saved my bacon.
Basically what the guy did is created a dummy database, took SQL offline and swapped the MDF files out, brought SQL back online (effectively putting the database into SUSPECT mode), then put the database into EMERGENCY mode, and had SQL re-create the LDF file. Genius!
I can attest to the fact that this works on SQL Server 2008. My database was 27GB in size, so it may take a while to create the LDF file. For me it took about 30 minutes to recreate the file.
Hopefully this post will help someone else out with the issue that I was facing!