In my previous post we discussed about moving TEMPDB database to a different location.

In this we will discuss on how to move the MSDB & MODEL database.

As I said we can check the properties of any database using the following command:

SP_HELPDB <database name>

So for MSDB & MODEL databases, you can run the following command:

SP_HELPDB MSDB
GO
SP_HELPDB MODEL
GO

Now going to the process on moving the above databases to a different location follow the below steps:

1. Restart the SQL Server service in single user mode. Do the below to restart the server in a single user mode.

Start -> Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> Notification Services Command Prompt

or

Start -> Run –> Type Command or CMD -> Press OK or Enter

2. In the command prompt, type the following commands in the order.

a. NET STOP MSSQLSERVER (or) NET STOP MSSQL$<Instance_Name>

b. NET START MSSQLSERVER /c /m /T3608 (or) NET START MSSQL$<Instance_Name> /c /m /T3608

Now your SQL Server is in Single User mode and only one administrator can connect.

By default the object explorer is open and it is considered as one connection so if you click new query it is considered as second connection and hence you get the error. Inorder to avoid the error you need to click the disconnect button in the object explorer pane and then close the object explorer window.

3. Now click the New query and you could connect to the server. Once you are connected to it, run the following:

SP_DETACH_DB ‘MSDB’
GO
SP_DETACH_DB ‘MODEL’
GO

4. Now move the model and msdb data and log files to the desired location i.e F:\SystemDatafiles.

5. After you move the data and log files to the new location, attach the files using following:

SP_ATTACH_DB ‘MODEL’,'F:\DATA\MODEL.MDF’,'F:\DATA\MODELLOG.LDF’,
GO
SP_ATTACH_DB ‘MSDB”F:\DATA\MSDBDATA.MDF’,'F:\DATA\MSDBLOG.LDF’,
GO

6. Now start the SQL Server service in a normal mode using the below commands.

In the command prompt, type the following commands in the order.

a. NET STOP MSSQLSERVER (or) NET STOP MSSQL$<Instance_Name>

b. NET START MSSQLSERVER (or) NET START MSSQL$<Instance_Name>

7. Now check the properties of Tempdb like location, size, etc. using the following.

SP_HELPDB MSDB
GO
SP_HELPDB MODEL
GO

Now you can find the modified locations.

This is the way we change the location of MSDB & MODEL database.

In the next post, you can find more on changing the location of other system databases (MASTER).

Moving TempDB database to a different location:

As an administrator, sometimes it requires to move the System database to different location for various reasons like freeing up the space on drive, for improving the performance of the database, reducing the load on the drive, etc.

Now I am also facing the same issue, I need to free up the space on my C drive. Therefore, I am planning to move the System database. Today I tried in moving the Tempdb database to a different drive.

Before going with the process on moving the Tempdb database, I would like to discuss few things that are recommended just like prerequisites.

1. The source and destination drives should be of same type or format. Ex.: Fat32, NTFS, etc.
2. Make sure that the destination drive is not compressed. If it compressed already, you can uncompress by going to the properties of the drive.

Now let us go with process on moving Tempdb to a different location.

Data files of Tempdb are stored in the default location on C drive (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data).

I want to move these files to F drive (F:\SystemDatafiles).

1. Execute the following to see the properties of Tempdb like location, size, etc.

SP_HELPDB TempDB

2. Now execute the following to change the location of data files of TempDB database.

3. Restart the Services of SQL Server.

4. Now check the properties of Tempdb like location, size, etc. using the following.

SP_HELPDB TempDB

Now you can find the modified locations.

This is the way we change the location of TempDb database.

In the next post, you can find more on changing the location of other system databases.