MSSQL 2005 Express

Its a permissions error. Don't detach it. Back it up and restore on the other server.

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

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

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.

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

Detach and reattach may well be supported, but back up and restore would take a few minutes and works perfectly.

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.

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.

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?

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.

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!

Hi Trevor

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

Regards Marten

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