Moving a Database 1

[zarkon4]

Currently I have two SQL server 2000 EE running on seperate servers. I want to move a database to the other server.
I tried to detach the database from server1 but the the ok button was greyed out and it would not let me detach.
Am I missing something? All of the other databases did ok.

I need to move this database and at this point I am at a loss on how to do it. What steps do I need to do to get this moved?
I thought this would be simple.

 

[ptheriault]

usually in EM things are grayed out because you don't have permissions to that function. You can try it from Query analyzer.

Here is the syntax.
EXEC sp_dettach_db @dbname = N'pubs'

EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf

 

[zarkon4]

Thanks, that is wierd about the permissions thing. I am connected using the sa login.
The query analyzer approach should work.

 

[SQLBill]

I believe it has to be in single-user mode to detach. Do you have any jobs running on that db? How many connections do you have to the db - if you have Enterprise Manager open and QA open, that's two connections even if you opened QA from Enterprise Manager.

Suggestion.....stop the SQLServerAgent Service. Then open ONLY Enterprise Manager OR Query Analyzer and do the detach.

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

You don't have to been in single user mode to detach the database. However if anyone is logged into the database you won't be able to detach the database.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com