Moving the Tempdb and Master Database in SQL Server

There have been many occasions where I found it necessary to move the database and log files to different drives to increase performance. When you want to move a typical user database to a different drive for performance or to split out the logs, you run sp_detach and sp_attach to perform the move. However, when it comes to moving the Master and Tempdb database, different rules apply. In this post, let’s walk you through the process of moving these databases.

Moving the Master Database

In the past I have had to move the master database log file to a different drive. If you ever have to perform this function, follow these rules to move the master database successfully. First, right-click on SQL Server in Enterprise Manager (EM) and choose Properties. Next, click the Startup Parameters as shown in Figure A. As you can see in Figure A, the following parameters appear in this box:

-d is the fully qualified path for the master database data file.
-e is the fully qualified path for the error log file.
-l is the fully qualified path for the master database log

Now, if you want to move your files you would remove the current entry and recreate your new entry with the correct path. For example, I am going to move the Master database log to (C:Test). At this point, I would delete the -l [path] by highlighting the old parameter and choosing remove (See Figure B). Next, I would add the following entry, (See Figure C), -l (c:Testmastlog.ldf) and click OK twice. Now, you will stop SQL Server and move the mastlog.ldf to its new location.

Note: Please make sure you move the mastlog.ldf to the location you configured in your startup parameters. If you move it to a location that is not specified in the startup parameters, SQL Server WILL NOT start.

Finally, start SQL Server and you have successfully moved your Master database as shown in Figure D.

Moving Tempdb

In order to move the tempdb database, open query analyzer and run the following query:

use master go

Alter database tempdb modify file (name = tempdev, filename = ‘E:Sqldata empdb.mdf’)

go

Alter database tempdb modify file (name = templog, filename = ‘E:Sqldata emplog.ldf’)

Go

Depending on where you are moving Tempdb is where you will specify the filename=parameter. I am going to move

the templog to (c: est) as shown in Figure E. After running the query, delete the old file after restarting SQL Server.

Moving MSDB Database

In order to move the MSDB and Model database, follow these steps. First, right-click the SQL-Server name and click properties. From the General tab, choose your startup parameters. Next, enter the parameter -T3608. Click OK, stop and restart SQL Server. After the restart, detach the database and move them to their appropriate place.

Whenever your drive space is getting tight and you need to move your system database files to different drives, following these simple procedures will allow you to move your databases efficiently and with confidence.