MSSQL 2005 Express

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
Trevor_CAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee SavidgeCommented:
Its a permissions error. Don't detach it. Back it up and restore on the other server.
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Trevor_CAuthor Commented:
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
Trevor_CAuthor Commented:
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
Lee SavidgeCommented:
Detach and reattach may well be supported, but back up and restore would take a few minutes and works perfectly.
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
Anthony PerkinsCommented:
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
Anthony PerkinsCommented:
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
Trevor_CAuthor Commented:
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
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Hi Trevor

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

Regards Marten
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Hi Trevor
Did the errorlog help you firure out what ports and protocols your SQL is using?

Regards Marten
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lee SavidgeCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.