Newbie cannot find the MDF and LDF file he has just copied over

Question

Post reply

Newbie cannot find the MDF and LDF file he has just copied over

Alan Notton

Mr or Mrs. 500

Points: 594
More actions
April 7, 2005 at 7:50 am

#185707

I hope you guys can help an SQL newbie.
I've been sent, on CD, an MDF and LDF file from a colleague to use in developing his website. The name of the files are both called BookData.
Here's my problem - I've created a database, on my local server, called BookData and attempted to copy these two files directly into C:\Program Files\Microsoft SQL Server\MSSQL\Data but they do not show up anywhere within SQL Server Enterprise Manager.
Obviously, I'm doing something wrong here. All I want to know is how do I copy these two files from CD into SQL Server, so that I can see them within SQL Server Enterprise Manager.
Thank you
Alan

Viewing 6 posts - 1 through 5 (of 5 total)

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

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 1

Sounds like you just need to attach the database.

From Enterprise Manager

Right click on databases, select all tasks and then attach database.
Browse to the location that you copied the files to and select the MDF file.
Enter the appropriate DB name (or leave it alone if you want)
Change the owner to SA (saves potential problems later)
Click on OK to attach.
The database should now be visible in Enterprise manager.

If you're connecting as SA you should be able to see everything you need to, if you have to create new logins then that's a completely seperate post as you might encounter orphan login errors (but we'll save that for when/if you need worry about them)

Alan Notton Mr or Mrs. 500 Points: 594 More actions · Answer 2

Thanks Mike, that worked absolutely perfectly.

I would now like to ask a follow-on question, which I hope is okay to include on this thread because it is related to the above scenario.

I have now received further MDF & LDF files which contain completely different tables. I would like to include all of these into the database that I just created, but when I try to use the above method to do this, SQL Enterprise Manager attempts to set up a completely new database, which is not what I'm after.

Is it possible to incorporate these new files into an existing database? And if so, how?

Thank you

Alan

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 3

You would need to attach them as a new database and the use the DTS (Data Transformation Service) to copy Tables/data between the existing database and the new database.

After the new DB is attached right-click the DB in the enterprise manager (EM) and the All tasks -> Export Data.

The wizard should walk you through it.

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 4

couldn't have explained it any better than that (although I could definately have done a much worse job if you'd have prefered )

Alan Notton Mr or Mrs. 500 Points: 594 More actions · Answer 5

Brilliant!

As they say, it's easy when you know how, it's just the 'knowing how' bit that I need to work on.

Jim & Mike, thank you very much.

Alan