?
Solved

Can't Attach to Database After Upgrading SQL Server

Posted on 2011-09-19
9
Medium Priority
?
507 Views
Last Modified: 2012-05-12
Hello Experts - I really need your help!

The issue: I upgraded from SQL Server Express to SQL Server 2008 R2 over the weekend, and now I can't connect to the project database from either Visual Web Developer or SQL Server Management Studio.

Background: I started building my first web app a few months ago, using Visual Web Developer Express and SQL Server Express. I built the database from within VWD.  This is on a stand-alone PC; no networking.  I recently discovered that in order to load the db to the commercial host, it had to be a backup (.BAK), and that I couldn't create the backup file in Express. So, I purchased a developer license of SQL Server 2008 R2, and installed it over the weekend. There were so many options in the installation process, I'm sure I messed something up, but I'm not sure where to start.

Now, when I go into VWD and view the Database Explorer, the project DB shows there but "test connection" fails. I tried modifying the connection from there, but have not had luck with either the "Select or Enter a Database Name" or "Attach a Database" options. The first doesn't show my db in the drop-down, and the second has a browse button but I can't navigate to the db file.

I suspect that if I could add the database to the list in SQL Server Management Studio, I'd then be able to connect to it from VWD.  The attached screenshot shows the error I get after clicking Attach Database --> Add...

I have no experience with SQL Server, so am looking for specific direction on what settings I need (I'm guessing from initial research things like Instance Name, Server Name, connection string, etc.), and also where to find them and where to set them so that everything talks to each other.  If I need to provide additional info here just let me know what is needed and how I find it.

Thanks in advance -- I really need to get this resolved so I can get back to coding!

Thank You! Screen Shot - SQL Mgmt Studio
0
Comment
Question by:JMS1965
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 6

Expert Comment

by:jorgedeoliveiraborges
ID: 36563874
It sound like you do not have rights to access the specified path.
Change de directory of .mdf datafiles ("instance"\data) and try again.
Just like that.
0
 

Author Comment

by:JMS1965
ID: 36564185
Hello -

Thanks for your suggestion! I really apologize for my inexperience here, but if you could clarify a couple things I will give it a try ...

1

You have "instance" in quotes, so can you tell me how to find the instance name value?

2

Once I have the instance value, where do I set it?
Thanks for working with me on this!
0
 
LVL 10

Accepted Solution

by:
Ramesh Babu Vavilla earned 1000 total points
ID: 36564918
check the database path in the sql server properits,cheeck the image attached

change the path to you current path
path.jpg
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36566558
Please make sure the user has permission to read the folder. Files PAth is valid etc. etc
0
 
LVL 6

Assisted Solution

by:jorgedeoliveiraborges
jorgedeoliveiraborges earned 1000 total points
ID: 36566907
put the files into path indicated in your environment (see post #36564918)
0
 
LVL 6

Expert Comment

by:jorgedeoliveiraborges
ID: 36567266
... ,please.
0
 

Author Comment

by:JMS1965
ID: 36567900
OK ... we have progress!

I copied the database into the directory specified in the server properties window (thanks for the screen shot).

I can now see the database in SQL Server Management Studio.

I went back to Visual Web Developer, and was able to try enough things that I finally got the db connected there. I then worked on modifying the connection string so the app would work.  I am now stuck here.

I have three connection strings, the first for the database in question and the next two for Entity Framework models.  The first one is now working ... I can log in to the app from web browser.  But, I'm not sure what to do on the next two strings; whenever the app tries to use the Entity models, the error is "Underlying Provider Failed to Open".  Here are the connection strings:

      <add name="ApplicationServices" connectionString="Data Source=JULIESCHWALM-PC;Initial Catalog=ASPNETDB.MDF;Integrated Security=True"/>
      <add name="edmIFAapp" connectionString="metadata=res://*/App_Code.EFmodels.edmIFAapp.csdl|res://*/App_Code.EFmodels.edmIFAapp.ssdl|res://*/App_Code.EFmodels.edmIFAapp.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=JULIESCHWALM-PC;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />
      <add name="edmAcctSettings" connectionString="metadata=res://*/App_Code.EFmodels.edmAcctSettings.csdl|res://*/App_Code.EFmodels.edmAcctSettings.ssdl|res://*/App_Code.EFmodels.edmAcctSettings.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=JULIESCHWALM-PC;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

Open in new window


Any ideas on this part?

What has changed is that the database is now in a different location (the path specified in the server properties), where before this change it was stored in the App_Code folder of the web app (within My Documents).

Thanks!
0
 

Author Comment

by:JMS1965
ID: 36570304
UPDATE ...

Through trial and error, I got the Entity Framework connection strings working also, by replacing:

           AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;

With

           Database=ASPNETDB.MDF;

Thanks all!
0
 

Author Closing Comment

by:JMS1965
ID: 36570320
Thank you again!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question