How to attach a MS SQL database (.mdf) file with a missing log file (.ldf)

Tonight I ran into a dilemma where I was only supplied with a Microsoft SQL .mdf file. I thought that I would have no problems and just have to attach the database file and Microsoft SQL would rebuild the log file for me.

I was wrong and it didn't take any longer than five minutes for me to figure that out. I thought to myself that quite a few people would have had the same problem as me and a solution would be available the World Wide Web. Thanks to Google, I eventually came across a post where a guy had available the SQL command that will attach the database file and build the log from scratch if it detects any errors.

Here is the MSSQL command needed to attach an .mdf SQL database file with no .ldf file, enjoy!

sp_attach_single_file_db @dbname= 'databasename', @physname= 'C:\Users\Blake\Documents\Databases\databasefile.mdf'

You may get something like the following output to the Messages window

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL\Data\databasefile_log.ldf" may be incorrect.

New log file 'C:\Users\Blake\Documents\Databases\databasefile_log.LDF' was created.

Converting database 'databasename' from version 100 to the current version 200.

3 comment(s)

dkerr wrote on January 23, 2008

Thanks! This should help me.  Where do I enter this MS SQL command to execute it?

dkerr wrote on January 23, 2008

executing via SQL Query Analyzer throws error and does not create log file

Server: Msg 1813, Level 16, State 2, Line 1

Could not open new database 'forms'. CREATE DATABASE is aborted.

Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL\data\FORMS_Log.LDF' may be incorrect.