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.


Posted Oct 06 2007, 11:26 PM by Blake Niemyjski |


Comments 

microsoft » How to attach a MS SQL database (.mdf) file with a missing log file (.ldf) Posted on Oct 06 2007, 11:56 PM

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

Top 10 Contributor
Joined on 04-01-2008
re: How to attach a MS SQL database (.mdf) file with a missing log file (.ldf) Posted on Apr 01 2008, 07:57 AM

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

Top 10 Contributor
Joined on 04-01-2008
re: How to attach a MS SQL database (.mdf) file with a missing log file (.ldf) Posted on Apr 01 2008, 03:24 PM

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.

The LSN Passed to Log Scan in batabase « Resoflex Computer Troubleshooting Guide Posted on Apr 19 2011, 04:00 AM

Pingback from  The LSN Passed to Log Scan in batabase « Resoflex Computer Troubleshooting Guide

Restore SQL Server Database from suspect - Bilim-Teknoloji | Positive Pozitive.NeT Posted on May 12 2011, 06:06 PM

Pingback from  Restore SQL Server Database from suspect - Bilim-Teknoloji | Positive Pozitive.NeT

Restore SQL Server Database from suspect - Bilim-Teknoloji | Positive Pozitive.NeT Posted on May 12 2011, 06:06 PM

Pingback from  Restore SQL Server Database from suspect - Bilim-Teknoloji | Positive Pozitive.NeT

How do we insert an .mdf to sql2005? | Zenk Posted on Nov 21 2014, 07:41 PM

Pingback from  How do we insert an .mdf to sql2005? | Zenk

Restore SQL Server Database from suspect – mfreidge Posted on Mar 22 2016, 12:54 PM

Pingback from  Restore SQL Server Database from suspect – mfreidge

windowscoding.com;
Copyright © 2008 Windows Coding
Microsoft and Microsoft logo's are trademarks of Microsoft Corporation.