Solved

Can't Attach to Database After Upgrading SQL Server

Posted on 2011-09-19
9
497 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:
sqlservr earned 250 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 6

Assisted Solution

by:jorgedeoliveiraborges
jorgedeoliveiraborges earned 250 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now