How do I attach a .mdf if I don't have a .ldf file?

Question

How do I attach a .mdf if I don't have a .ldf file?

Christian Bahnsen

SSC Eights!

Points: 969
More actions
May 3, 2010 at 6:07 pm

#234478

I was going to do Andy Warren's Performance Tuning Puzzle (http://www.sqlshare.com/Media.aspx?vid=758) but can't attach the sample database to my instance of SQL Server 2008 Express Edition. This is the first time I've ever tried to attach a database. The zip file contained a .mdf file but not a .ldf file.
So my question is: How do I attach a .mdf if I don't have a .ldf file?
Thanks in advance for any help. Here's the error message text:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Attach database failed for Server 'G70-460US\SQLEXPRESS'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Could not open new database 'PerfQuiz'. CREATE DATABASE is aborted.
File activation failure. The physical file name "C:\mydata\logs\PerfQuiz_log.LDF" may be incorrect.
The log cannot be rebuilt when the primary file is read-only. (Microsoft SQL Server, Error: 1813)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1600&EvtSrc=MSSQLServer&EvtID=1813&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 1

I gots no experience doing this stuff, so someone else may correct/clarify this, but Pinal Dave just had this situation on his blog. http://blog.sqlauthority.com/2010/04/26/sql-server-attach-mdf-file-without-ldf-file-in-database/[/url]

Sounds like he's saying it's dicey, so you may want to just ask Andy for clarification? Here or at http://www.sqlandy.com/[/url]

hope that helps,

Jon

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Looks like the file has the read-only flag. Right click the file (in explorer), uncheck the ReadOnly checkbox and click OK. Try to attach again.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Christian Bahnsen SSC Eights! Points: 969 More actions · Answer 3

Thanks for the replies. You'd think from the error message that the .mdf file was read-only, but it's not (I'd already checked that).

The problem appears to be OS-related. The box I used last night uses Vista Home Premium. I just tried it this morning on a XP Pro Ver 2002 SP3 box and didn't have any problems. Both boxes are running SQL Server 2008 Express Edition with Advanced Services.

I wonder why the Vista box couldn't/wouldn't attach the file?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

On the Vista box, run management studio as Administrator (Right click -> Run as Administrator). It's due to the UAC.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Christian Bahnsen SSC Eights! Points: 969 More actions · Answer 5

Thanks. That solved it. Ya learn something new everyday! 😉

I've had no troubles creating, backing up, and restoring database objects in that instance, but attaching a database created elsewhere must be a riskier operation in Vista's opinion. Other than the too-frequent requests to authorize operations, Vista hasn't been as bad as I'd heard.

Thanks again.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

There's a kb article on it iirc. I ran into that back when I first installed Vista (year and a half ago). Almost forgot about it until now.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Elliswhite SSCommitted Points: 1783 More actions · Answer 7

Follow these steps to attach MDF file without LDF

1.Open SSMS

2.Go to object explorer and Databases and make a right a click on it

3.Select attach option

4.Attachment window open click on ADD to attach the MDF file

5.Select the MDF file and click on OK

6.Now confirmation windows appears confirm the MDF file and remove the LDF file

7.Then go to add button and checkout the attached databasa file

;-):-) I hope this will help you

SSMS Expert

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Please note: 4 year old (solved) thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass