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
Solved

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

Posted on 2012-03-13
7
481 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
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

856 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