SQL Server Performance Forum – Threads Archive

Relocate Entire Instance

Hi, I have just installed SQL Server 2000 and the default database directory is "c:sqldatafilessystemMSSQLdata". Is there an easy way to move the location of the data store to say "c:sqldatafilessystem". Looking through the articles on here, I know that I can use the startup parameters to specify a new location for master and its logfile and also the error log. And I have read the article that explains how to move model and msdb etc. But I was wondering if there is a simpler way. For example 1. Shutdown SQL Server instance.
2. Copy files to new location
3. In oracle you can the startup nomount and alter database rename datafile etc.
4. Startup SQL Server instance with datafiles in the different location. Is there any way to do something like this ? Cheers Rich

http://support.microsoft.com/kb/224071/EN-US/
http://support.microsoft.com/kb/304692/EN-US/ For new databases you can specify a default location when you right-click in Enterprise Manager on the serevr in question -> Properties -> Database Settings -> Default Data (Log) Directory —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Thanks Frank. These are the articles that I have already read. Are these the only was you can relocate the whole instance ? Rich

how many databases you have in your instance. do you want all the databases to move to the new locations or do you need only new databases to be in the new location

At the moment I only have the default databases as I have just re-installed SQL Server. I understand how to change the location for new database, I just want to know if there is a less easier way to relocate the default system databases (master, msdb, model etc) Cheers

Sorry meant less complex not less easier [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by rgregor6 At the moment I only have the default databases as I have just re-installed SQL Server. I understand how to change the location for new database, I just want to know if there is a less easier way to relocate the default system databases (master, msdb, model etc) Cheers <hr height="1" noshade id="quote"></blockquote id="quote">

there are few artciles as well,
http://www.databasejournal.com/features/mssql/article.php/3379901
http://www.nigelrivett.net/SQLAdmin/MoveDatabases.html

Yes, I think these are the only ones. At least can’t I remember having heard of another one right now. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

]]>