Solved

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

Posted on 2012-03-13
7
483 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

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…
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

740 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