Episode II - System Databases

After installing and service packing SQL Server (I'm using SQL 2005), I had to move the system databases off of their default disks.  Again, this is to make sure Windows will have as little disk I/O contention as possible.

For tempDB, the solution is pretty simple.  You can use ALTER DATABASE to move the database, then restart the SQL services.  The code to move it is simple:

USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');
GO 

After the SQL services are restarted, tempDB will be in its new home.

Moving master is a bit trickier.  First, open the SQL Server Configuration Manager (Start-->All Programs-->Microsoft SQL Server 2005-->Configuration Tools-->SQL Server Configuration Manager)

Highlight the SQL Server 2005 Services node on the left pane, then double click the SQL Server (MSSQLSERVER) item in the right pane.  

Scroll down to the Startup Parameters section and modify the switches as needed














Now stop the SQL services and move the master.mdf and mastlog.ldf files to the locations specified above.

At the command line, start the SQL service in recovery mode with NET START MSSQLSERVER /f /T3608.

In SQL studio, execute the following query, making sure to use the correct path for the new locations of the master database files

ALTER DATABASE mssqlsystemresource 
    MODIFY FILE (NAME=data, FILENAME= 'new_path_of_master\mssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource 
    MODIFY FILE (NAME=log, FILENAME= 'new_path_of_master\mssqlsystemresource.ldf');
GO

Now move the mssqlsystemresource.mdf and mssqlsystemresource.ldf files to their new homes, set the Resource database to read only ALTER DATABASE mssqlsystemresource SET READ_ONLY;

Finally, restart the MSSQL service and verify that the files were moved where they should be.