Solved

ODBC connection is invalid after restoring DB to a new SQL Server Express machine

Posted on 2012-03-13
7
471 Views
Last Modified: 2012-03-14
Short story: after restoring a SqlExpress 2005 LOB db to a new server, the client application cannot connect anymore to the instance (invalid connection).

So I have a LOB db hosted on a sqlexpress 2005 server (to be retired) that has to be moved to a new server. I installed the same version LOB db on the new server: it installed the same sqlexpress 2005 engine (9.0.3042) and created a new empty db. I install the application on a workstation and establish a ODBC connection with the new server. So I have access to the new empty db, where I can start entering data. I then made a backup of the db on the old server using Management Studio / right-clic on db / backup / create new file / and save to a .bak file. In management studio on the new server, I restore the bakcup file to the new empty db using the Tasks / Restore / select .bak file. Restore runs without error.
But then the client workstation cannot connect anymore to the Instance hosting the restored file: ODBC connection is invalid.

I tried to detach the DB from the old server and then attach it to the new without success. Tried with different versions of SQL Express 2005 also without success. Do I need to restore also the system databases (master, model, msdb, tempdb), or proceed differently ?

Environment info: Old server = sbs2003 running Sqlexpress 2005 v.9.0.3042. New server = sbs2011 running Sqlexpress 2005 v9.0.3042 (has also Sql express 2008 R2 running for other instances (sharepoint, wsus).

Thanks.
0
Comment
Question by:ndidomenico
  • 4
  • 3
7 Comments
 
LVL 13

Expert Comment

by:dwkor
ID: 37717297
What is exact error message? Assuming that your ODBC connection is valid, there could be quite a few different reasons. Most common ones are:
1. Firewall
2. SQL Express has network protocols disabled by default. You need to go to server configuration utility, enable them there and restart the service

Also I'd suggest you to try to connect to the instance via Management Studio rather than use ODBC to make sure that there are no problems in ODBC configuration
0
 

Author Comment

by:ndidomenico
ID: 37717328
The error is "ODBC connection invalid". The connection to that database and instance works fine until I do a restore of the database from the backup file. The backup file is restored under the same instance where the new empty database was working just minutes before the restore.
0
 
LVL 13

Accepted Solution

by:
dwkor earned 500 total points
ID: 37717348
Are you using SQL Authentication? In such case make sure that login is mapped to the database user because restore would invalidate that. You can "fix" the problem with sp_change_user_login stored procedure http://msdn.microsoft.com/en-us/library/ms174378.aspx

Error message is way too generic. This is a reason why I suggested to use management studio to test connection first.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:ndidomenico
ID: 37717351
I've allowed the sqlserv.exe of that instance in the Windows firewall, enabled local AND remote in the SQL Surface Area Configuration, started the SQL browser service and allowed sqlbrowser.exe in the firewall.
0
 

Author Comment

by:ndidomenico
ID: 37717357
You mean to use Management Studio from the workstation and try to connect to the sql server ? Will check your last 2 recommendations and get back in a few minutes
0
 
LVL 13

Expert Comment

by:dwkor
ID: 37717683
You can use Management studio on the same box - it will show you if there is obvious problem (for example - login does not have access to the database) but would not help to troubleshoot security problem.

So the better way to use Management Studio (you can install it without SQL Express itself) on the box from which you're trying to connect via odbc
0
 

Author Closing Comment

by:ndidomenico
ID: 37720560
The problem was SQL Authentication. I finally found the proper login name (sa) and password to use when linking to the DB. Your suggestion on using Management Studio to try to login to the Instance got me in the right direction, along with your asking if I was using SQL authentication.

Thanks a lot !
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 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

24 Experts available now in Live!

Get 1:1 Help Now