[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MSSQL 2005 Express

Posted on 2011-10-04
14
Medium Priority
?
237 Views
Last Modified: 2012-05-12
I am having problems connecting with my application to the database after moving (Attach/detach) it to a new server. Please help?
sql-error.jpg
0
Comment
Question by:Trevor_C
  • 6
  • 3
  • 3
  • +1
14 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 36909095
Its a permissions error. Don't detach it. Back it up and restore on the other server.
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 36909102
Make sure your SQL Server is accepting remote querys, not default for SQL Server EXPRESS. See:
http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/

Make sure your account for accessing on the old server is moved to the new server, or create a new account, and add this with the proper security to your newly attached datbase.

//Marten
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 36909340
Bye the way

Attach/detach is a supported operation, you don't have to do a backup/restore.

But remember, if you attach to a newer sql server, you cannot detatch and reattach on a server thats an older version. This has to do with changes in the database structure. This can be good to know, you cannot detatch, move file to a newer sql version, i e 2005 to 2008 to see if it works, and after this detach and reattach on the old (2005) version, since the db has been updated. But the same thing is true about backup/restore operation.

//Marten
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Trevor_C
ID: 36909776
Thanks Marten, I am using version (2005) on both servers just operating systems are different, I am looking at your solution to see if it works for my problem and will keep you posted.
0
 

Author Comment

by:Trevor_C
ID: 36911095
Hi Marten I followed your instruction but am still getting errors when I try to setup the data source. First I could not see the instance of the database but after restarting the server I can now see the instance but cannot setup the data source , screenshot attached.

thank you for your help so far.
sql-error-2.jpg
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 36911107
Detach and reattach may well be supported, but back up and restore would take a few minutes and works perfectly.
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 36911380
You need to enable TCP IP I belive:
http://support.microsoft.com/kb/818047

Use SQL Server Configuration Manager under program\Microsoft Sql\Configuration tools or something like it.
Find protocol TCP/IP and enable it.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36911502
lsavidge,

>>Detach and reattach may well be supported, but back up and restore would take a few minutes and works perfectly. <<
You may want to check out the images the author has posted.  You will see that the question has nothing to do with backup/restore vs. detach/attach, but has to do with the fact the author is having connection problems with the database that has been moved to a new server.

If you still feel that your comment is appropriate, why don't you explain step by step why you think this is the case.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36911522
Trevor_C,

Have you ever connected to the new server after installing MS SQL Server Express?
Have you verified the SQL Server Express service is actually running?
0
 

Author Comment

by:Trevor_C
ID: 36915700
I did enable TCP/IP, I did verify that the SQL Express is running, I did not connect to the new server since the MS SQL Server Express install.
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 36916193
Look in your sql server installed directory, in a folder called log (or is it logs) there is a fila called errorlog (no extension).
Open this in notepad, and youll find a

My path to my sql 2008 Dev ed installation is:
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log
And my errorlog file says:
2011-07-02 23:14:08.50 Server      Server is listening on [ 'any' <ipv6> 1433].
2011-07-02 23:14:08.50 Server      Server is listening on [ 'any' <ipv4> 1433].
2011-07-02 23:14:08.50 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2011-07-02 23:14:08.50 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
2011-07-02 23:14:08.63 Server      Server is listening on [ ::1 <ipv6> 1434].
2011-07-02 23:14:08.63 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].

Thereby I can see that my instance is listening on port 1433 (the default port by the way).
So I can connect using my Ip nr and a comma and space and portnr like:
192.168.0.33, 1433
This will connect to my instance, regardless of instance name.

See what your errorlog file says and post it here!
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 37018976
Hi Trevor

Can you provide me with the portion from the errorlog and I'll be able to help you.

Regards Marten
0
 
LVL 20

Accepted Solution

by:
Marten Rune earned 2000 total points
ID: 37083017
Hi Trevor
Did the errorlog help you firure out what ports and protocols your SQL is using?

Regards Marten
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 37083099
acperkins,

>>If you still feel that your comment is appropriate, why don't you explain step by step why you think this is the case.

It is appropriate because the question asker said that they had move the database suggesting that it was them that performed the move. I was merely suggesting an alternative to detach/reattach.

Steps here: http://msdn.microsoft.com/en-us/library/ms187048%28v=SQL.90%29.aspx
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

872 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