Moving SQL Server System Databases

I wrote a post on the same, specifically on User-Defined databases: Moving User-Defined SQL Server Databases files - within the instance. However, the procedure for moving system databases is bit different from that, hence here is the way of doing it.

Let's see how we can move system databases such as msdb, model and tempdb. If you have read the above-mentioned post, you know that we need to make our database offline in order to move files. But for these system database, we cannot make the databases offline, we need to stop the SQL Server Service.

Here are the steps. Let's see how we can move tempdb files as it is same for both model and msdb.

1. Alter the location of its files with new the new location.

USE master;
 GO

 ALTER DATABASE TempDB MODIFY FILE
 (NAME = 'tempdev', FILENAME = 'E:\New Location\tempdb.mdf')
 GO

 ALTER DATABASE TempDB MODIFY FILE
 (NAME = 'templog', FILENAME = 'E:\New Location\templog.ldf') 
GO

2. Make sure you have done the above for all files. Once done, stop the SQL Server Service.

3. Now you can move files as you need. Move mdf and ldf files to the new location.

4. Restart the service. You should see that it starts without any issue.

If you need to move the master database, process is not same. You cannot alter the master database, hence you need to do a small change for startup parameters.

1. Get the properties of SQL Server Service using SQL Server Configuration Manager.

2. Change -d parameter, setting the mdf  file to the new location and -l parameter, setting the ldf file to the new location.

3. Stop the SQL Server Service.

4. Move files to the new location.

5. Restart the service. Now your master database files in new location and SQL Server must work with them without any issue.