Changing the file location of most SQL Server system databases is generally pretty straight forward. In most cases you can run something like the following
ALTER DATABASE command to reassign the file paths for the associated data and log files, physically move the files and then simply restart the SQL Server service.
ALTER DATABASE tempdb
MODIFY FILE(NAME = tempdev, FILENAME = 'E:\tempdb.mdf')
ALTER DATABASE tempDB
MODIFY FILE(NAME = templog, FILENAME = 'E:\templog.ldf')
The example above is specific to the TempDB system database but can easily be modified to move the msdb and model databases. If you would like to further familiarize yourself with moving these databases you can reference my previous post on Moving TempDB Data and Log Files To A New Drive On Your SQL Server.
Moving the master database can not be done using the above strategy. The location of the master database files are assigned through the registry and must be changed by altering the startup parameters associated with the master database through SQL Server Configuration Manager.
In order to do this you’ll first need to connect to the server hosting SQL Server and open SQL Server Configuration Manager.
Once connected, click on the SQL Server Service on the left side of the window and then locate SQL Server (MSSQLSERVER) on the right side and then right-click and select Properties.
Note: If you used a Named instance instead of the default instance when installing SQL Server you will see the Named instance in parenthesis instead of MSSQLSERVER.
On the Properties window, click on the Startup Parameters tab. This is where the the master database data and log file paths are set.
Generally you will see three existing parameters listed. These reference the
master.ldf (-l) and
ERRORLOG (-e). For the purposes of moving the master database we will be modifying the -d and -l parameters.
First click on the parameter beginning with -d. You will then see the file path for that parameter appear in the Specify a startup parameter text box.
Modify the path as needed but make sure you do not remove the parameter value (-d) in doing so.
Repeat this process for the the parameter beginning with -l. The screenshot below illustrates changing the file path for
master.mdf to point to
Once you have modified both file paths to point to your desired location click OK to go back to SQL Server Configuration Manager.
Locate the SQL Server (MSSQLSERVER) service, right-click it and select Stop.
Once the service has stopped you will need to physically move the
master.ldf files to their new location.
After moving the files go back into SQL Server Configuration Manager, right-click the service again and select Start. If your SQL Server Agent service is set to Manual start mode you will also need to start this service as well.
The last step is to verify the new location of the master database. The simplest way to do this is to query the catalog view using the script below.
state_desc FROM sys.master_files
Check the path displayed under the
physical_name column which corresponds to the master and mastlog files. You should see the newly specified path listed.
That it! You’re all done.
One last thing to note. I’ve seen issues before when moving the master database to a newly created drive which prevented the SQL Server service from starting. In most cases it was due to permissions on the drive itself or on the directory to which the master.mdf and master.ldf files were moved. Make sure the service account running SQL Server has the necessary permissions to the drive before moving the files. It’s a simple fix but will prevent your SQL Server from starting up again.