Moving System Databases — SQL Server 2000

Introduction:

There are lots of articles, Links and sites that describe how to move system databases from the SQL default location to the location described by the DBA but still lot of users find difficult while moving them and land up in trouble. So I thought that I would just do a graphical way of moving the databases so that the users can use the same and enjoy the task.

Moving tempdb database:

The following are the steps to be followed while moving the tempdb database.

1. Check what is the path of your tempdb database exists now. This can be done either using the GUI and looking for the properties of the tempdb database or with TSQL sp_helpdb tempdb

Here the tempdb database is placed in the default data directory of SQL Server installation.

2. Use the alter database command to move the tempdb database to a new location.

alter database tempdb modify file (name = tempdev, filename = 'f:\sugeshdatatempdev.mdf')
alter database tempdb modify file (name = templog, filename = 'f:\sugeshdatatemplog.ldf')

Syntax

USE master;
GO
alter database tempdb MODIFY FILE (NAME = tempdev,FILENAME='NEW PATH');
GO
alter database tempdb MODIFY FILE (NAME = templog,FILENAME='NEW PATH');
GO

3. Restart the SQL server services.

4. Check the file path of the tempdb database and you will find that the database will be moved to a new location.

Moving msdb and model database:

Since the steps to move the msdb and model database are same I shall illustrate it with model database the same can be used to move the msdb database too.

1. Check the file path of the model database as where it exists now. The same can be done either with GUI or TSQL sp_helpdb model.

2. Goto start -> Programs-> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration manager. Move to the Services and right click the SQL Server service to view the properties.

In the properties page tab to advanced as given below

 

Add the following switches in the startup parameters as given below.

-c, -m and T3608.

Restart SQL Server services.

3. use the given command below to detach the database from the server.

Sp_detach_db model

4. Once you have detached the database move the files of the model database to the location where you want to have those files.

5. Once they are moved use the following syntax to attach the database again to the server.

EXEC sp_attach_db @dbname = N'model',
@filename1 = N'f:\sugeshdatamodeldev.mdf',
@filename2 = N'f:\sugeshdatamodellog.ldf'

6. Remove the trace flag that was set on step 2. Restart SQL Server services. Now you can issue the sp_helpdb model to verify if the database has been moved to the other location

Moving master database:

Though moving master database seems to be an easy and straight forward task, it will make hay and helm if there is no proper planning and preparation done for it. The given below are the steps that you should follow to move the master database.

1. Check the current path of the master either with the GUI or TSQL sp_helpdb master.

2. Goto start -> Programs-> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration manager. Move to the Services and right click the SQL Server service to view the properties.

 

In the properties page tab to advanced as given below

Add the following switches in the startup parameters as given below.

-c, -m and T3608.

And edit the following switches switches

-d to reflect the new path of your master database .mdf file

-l to reflect the new path of your master database .ldf file

-e to reflect the new path of the error log file

 

3. Once this is done restart the SQL Server services.

4. Check the path of the master database to verify that it reflects your new path.

5. navigate to the properties page of SQL Server services as you did in step 2 and remove the following switches c, -m and T3608.

6. Restart the SQL Server services.

Conclusion:

Hope this article might have given you a detailed explanation and illustration of how to move system databases to other location than the default data directory. Still if you feel that something is missing in this document or express your views, suggestions and comments mail me to the id given below.