Urgent question about moving databases 2

[sqlgracie]

Hi,

I recently installed 2005 on a new server... if I am not mistaken, there is no option to select the location of the system databases during the install (unless I am really overlooking something here)... so by default the system db's end up in the c:\program files\microsoft sql server\mssql\data directory.

Problem is, I don't want them here. I need to move all of them to the D: drive.

Please, please tell me there is a way to move these databases over. Is is just a simple detach and attach? And how to do this properly, if there is an order involved.

NO user databases have been created yet.

Please help!

THANKS

 

[ptheriault]

Gracie,
You can change the install path during install. You click on the advanced button on the second or 3 rd screen. anyway, no you need to move your system databases.

To move master.
You have to stop the services, change the location of you master database and log file in the start up paramaters. (from sql server configuration manager.)

To move the temp db run this command.

use master
go
Alter database tempdb modify file (name = tempdev, filename = 'D:\your path\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'D:\your path\templog.ldf')
Go

Then restart the services.

You can detach the msdb database if you start the services with a trace flag. I'm not sure which trace flag number it is. Check BOL (books on-line)

You can also move the model db with an alter database command.



- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[sqlgracie]

Thanks Paul!

 

[mutley1]

Paul / Gracie

you've probably got it out of BOL by now, but thought I'd post it as I am just reconfiguring a server while reading the latest posts and seeing as I was at this stage......!!

-T3608 to move the msdb & obviously make sure the Agent isn't running.

Cheers,

M.

 

[ptheriault]

Thanks M.
I hadn't looked it up yet.

- Paul [flush]
- If at first you don't succeed, find out if the loser gets anything.

 

[sqlsadie108]

It took me 2 hours, but I actually managed to move all my db's to the correct location, even the so-called hidden "Resource" database, which incidentally does NOT show up if you query sys.master_files.

However, I still have distmdf and distldf on the c drive, and NO there are no instructions on how to move these on the microsoft site. I realize these have to do with replication, but not sure how to move them.

Anyways, I am so fed up at this point I doing a reinstall!

 

[mrdenny]

You should be able to simply move those files. They shouldn't be attached to your SQL Server until you try to enable replication.

When installing SQL 2005 when you get to the page with the list of services on it you have to click the Advanced button to be able to change where everything is installed.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

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

http://www.mrdenny.com