Search code examples
sql-servervisual-studio-2010mdf

sql server mdf file database attachment

I'm having a bear of a time getting visual studio 2010 (ultimate I think) to properly attach to my database. It was moved from it's original spot to #MYAPP#/#MYAPP#.Web/App_Data/#MDF_FILE#.mdf. I have three instances of SQL Server running on this machine. I have tried to replace the old mdf file with my new one and cannot get the connectionstring right for it.

What I'm really trying to do is to just open a DB instance and run a DB create script. Then I can have a DB that was generated via my edmx (generate database from model) in Silverlight business application (c#)

Right now, when I go to Server Explorer in VS, choose add new connection, choose MS SQL Server Database File (SqlClient), choose my file location (app_data directory), use Windows authentication, and hit the Test Connection button, I get the following error:

Unable to open the physical file "". Operating system error 5: "5(Access Denied.)". An attempt to attach to an auto-named database for file"" failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

The mdf file was created on the same machine by

  • Connecting to (local) in SQL Server management studio.
  • Getting a new query.
  • Pasting in the SQL from the generated ddl file.
  • Adding CREATE DATABASE [NcrCarDatabase]; GO; before the pasted SQL and
  • Executing the query.

I then

  • Disconnected from the DB in Management Studio.
  • Closed Management Studio
  • Navigated to the DATA directory for that instance
  • Copied the mdf and ldf files to my application's app_data folder.

I am now trying to connect to the same file inside visual studio.

I hope that gives more clarity to my problems :).

Connection string is:

Data Source=.\SQLEXPRESS;AttachDbFilename=C:\SourceCode\NcrCarDatabase\NcrCarDatabase.Web\App_Data\NcrCarDatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True

Solution

  • I was getting "access denied" until I ran Visual Studio as an administrator. I then had to detach the database from MSSMS before I could get it into Visual Studio.