Solved

Can't Attach to Database After Upgrading SQL Server

Posted on 2011-09-19
9
498 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql query to Stored Procedure 6 39
BULK LOGGED - log full 9 25
tempdb latch contention 12 50
How to write a sql query returning a result  with my date time template? 8 33
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

28 Experts available now in Live!

Get 1:1 Help Now