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.

Join the mailing list