problem copying database from sql server 2000 to sql server 2005

Hi,

I have right now sql server 2000 instance installed on a server (serverone) which is accessed on all nodes through an ODBC connection.

Now we have upgraded our server to windows server 2005 which will be on other machine servertwo. I have right now installed sql server 2005 in servertwo. Wish to copy the database from serverone to servertwo. Once it is copied and everything is working fine. I want to uninstal sql server 2000 from serverone.

What is the best way to do this? I am trying different things like tried running code Advisor on serverone which is not recognising the sql server 2005 instance of servertwo. It's giving message as since the sql server is installed on default settings it can't be accessed remotely. I see that SQL server 2000 database is accessible on all nodes including servertwo. Why is it that servertwo sql server is no where accessible?

What it is that I am missing?

Kindly help me.

Thanks, regards.

Shobha

You're really asking two questions, I think.

1. Why is servertwo not accessible?

With SQL Server 2005, we disabled a number of features by default to provide better security. You can easily adjust the settings at any time by using the Surface Area Configuration tool, available from the Start menu.

2. How do I move my databases over to servertwo?

There are several ways to do this. One way is to use sp_detach_db on serverone, then copy mdf and ldf files to servertwo and use sp_attach_db.

Paul

|||

Thanks for the reply Paul.

Is it that the datatbase should be exclusively available when doing sp_detach_db?

thanks

Shobha

|||

Hmm, good question, and a glance at BOL didn't pop the answer.

I don't believe you need exclusive access; I assume the operation is transactional so it'll wait for open transactions to complete before executing. But as I said above, the documentation of sp_detach_db doesn't make this clear. The documentation does list a few other requirements (e.g., if the database is replicated, it must be unpublished).

|||

Thanks Paul for that answer.

If you don't mind can I have sample code or instruction list on how to use the sp_deattach_db and sp_attach_db commands, this will be the first time that I will be using these commands since earlier I was working only with MSAccess database.

will be greatful if you can give me directions.

thanks