How can I attach AdventureWorks Database to SQL Server Management Studio Express?

Hi all,

I just downloaded AdventureWorks_Data.mdf, AdventureWorks_Data.ldf, AdventureWorksDW_Data.mdf, and AdventureWorksDW_Data.LDF files to the C:\Program Files\Microsoft SQL Server\MSSQL.1|\MSSQL\Data folder of my Windows XP Pro PC. I tried to attach the AdventureWorks database in my SQL Server Management Studio Express in the following way: I clicked on SQL Server 2005 Express=>SQL Server Management Studio Express=>Right clicked on Databases=> clicked on "Attach..."=> "Attach Databese" showed up. But I can not type in anything under the MDF File Location, Database Name, Attach As, Owner, Status, Message!!!? What is wrong in my way of doing the attachment of AdventureWorks database (described above)? Please help and advise.

Thanks,

Scott Chang

P. S. I do not have Microsoft Visual Studio 2005. Walkthrough: Install the AdventureWorks Database (http://msdn2.microsoft/en-us/library/ms31035.aspx) : To confirm your SQL Server data directory (Start=>All Programs, Microsoft Visual Studio 2005, Visual Studio Tools, Visual Studio 2005 Command Prompt) steps 1 thru 4 can not be applied to my SQL Server Management Studio Express. I read an SQL Express post dated 19 April 2006 and learned the above-mentioned way to attach the AdventureWorks database in the SQL Server Management Studio Express. But that way does not work for me.

Hi Scott,

You can't type in the name of the database, you need to click the Add button, which will open a browse window onto the Data folder, where you can select the MDF file you desire. Once added, you can click OK to complete the attach.

Mike

|||

Why are some folders unbrowsable in SSMSE? I have an .mdf file located under c:\documents and settings\myusername\blah blah blah.... and when I try to browse to my mdf file I get no further than c:\mydocuments\myusername. As if there are no subfolders. Is this a folder rights issue?

|||

Yes, it's a folder rights issue.

SQL Express runs as the Network Service account, which doesn't have permissions to any of the User profile directories such as My Documents. If you want to have your database always attached to SQL Express and available to everyone, then you should put the file in the SQL Data directory and attach it from there. If you want the database to be reserved for just a single user, then I would suggest you explore User Instances, which is a special way to run SQL Express under specific users accounts so that it does have permission to the User profile directories.

User Instances are used by default when you insert a database into a Visual Studio project. You can find more information here.

Mike