How to detach the Master DB to move to another logical drive 1

[ralwyn]

Help please! I have to move our SQL DB to another logical drive. How can I detach the Master DB? I can right click the other DB that we have and detach, but the Master DB is grayed out. Not sure what to do next. I have very little experience with SQL please advice.

Thank you all in advance.
ralwyn

 

[mrdenny]

You can't detach the master database.

You have two options.

1. Change the start paramaters to point to the new location.
Stop SQL
Move the physical files to the new location
Start SQL

2. Backup and restore master.

I would recommend number 1. There is less change of problems.

Connect to the SQL Server's console (or go to the console).
Open SQL Enterprise Manager.
Right click on the SQL Server and select properties at the bottom.
On the General Tab, click the Startup Parameters button.
There are 3 paramaters by default. They are -d, -e and -l.

-d is the path to the master.mdf.
-e is the path to the ERRORLOG file.
-l is the path to the master.ldf.

You need to change these paramaters, being sure to leave the -d, -e or -l infront of the path. You also need to be sure that the path you enter is correct. If it's not SQL won't start and you'll have to go digging in the registry to find these settings.

When you are ready, remove the ones you want to change, and add the new values. Click ok, then ok. Then stop the SQL Server and move the files. Then start the SQL Service back up.

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

 

[ralwyn]

Denny:

Thank you so much! It works!

Thanks again,
ralwyn

 

[mrdenny]

no problem.

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