?
Solved

Can't Attach to Database After Upgrading SQL Server

Posted on 2011-09-19
9
Medium Priority
?
517 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
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Integration Management Part 2
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

850 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