Solved

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

Posted on 2012-03-13
7
487 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
[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
  • 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
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.

 

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

624 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