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 article, we are going to walk you through the process of moving
these databases.
Moving the Master Database
There have been occasions
where I needed to move the master database log file to a different driver or I
needed to move the Master database 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:\test\mastlog.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.
Figure A:
Click the Startup Parameter to move your Master database.
Figure B:
Click Remove to remove a parameter that you want to edit.
Figure C:
Add a new Parameter and click Add.
Figure D:
The new location of the Master log file.
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\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.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:\test as shown in Figure E. After running
the query, delete the old file after restarting SQL Server.
Figure E:
Use Query Analyzer to move your tempdb database.
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.
End Sum
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.
»
See All Articles by Columnist Steven S. Warren
IE 7
Firefox 2.0