I was recently on a very challenging project where we had to do some SQL tasks that were both new to me as a DBA and proved to be challenging. I found myself doing certain tasks over and over. When this happens, I usually find myself automating, creating scripts or documenting. On this project, I did all three. I was constantly being tasked with having to move system databases on old or legacy SQL Servers to new storage locations. I am not going to get into why, it was just part of the tasks I needed to complete over and over. Moving database files to a new directory sounds like an easy task, right? Everyone always assumes the process is the same for both system and user databases. Just de-attach/move/attach, right?

Moving system databases are a totally different ballgame and if you do it wrong, SQL Server will not start. Use the same process de-attach/move/attach? WRONG! You couldn’t be more wrong. The system databases must be moved in a completely different way then user databases. The reason for this is because the SQL Server startup procedures and parameters are tied to the location of these databases.

I am only including Master, Model and MSDB system databases in this article because every DBA should know that for TempDB all you need to is update the properties of the file location to the new location and restart the services and the move happens. Of course, there are anomalies for this solution, however, 9 times out of 10, it’s simple as that. On the slight chance that you cannot move TempDB that simply, it is most likely you need to empty the initial files first. But let’s not digress as to the topic of discussion here which is how to move Master, Model and MSDB system databases.

Below I will go through the 10 simple steps for a successful move of the system databases. I have included scripts below that just need to be copied/pasted into your SQL Server Management Studio query window. Because paths and file names are variables, make sure to update the scripts as needed.

Backup the Databases

First and foremost, backup your system databases. This is just common sense. There is a common misconception that nothing important is saved in the system databases, however, that is not the case. According to Microsoft, the Master database records all the system-level information for an instance of SQL Server. MSDB stores agent jobs and scheduling information. The Model database is used as the template for all databases created on the instance of SQL Server. Modifications made to the Model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.

For these reasons, I recommend running a backup first. And seriously, this usually takes less than a minute anyway. Better safe than sorry.

Verify Paths

I always like to verify the paths that the System Databases are using the scripts below before I make any changes. Copy and paste these 3 scripts into a SQL Server Management Studio query window and run them. These will provide the current paths to the MSDB, Model and Master databases.

select name, physical_name from sys.master_files where database_id = DB_ID(N'MSDB')
go
select name, physical_name from sys.master_files where database_id = DB_ID(N'Model')
go
select name, physical_name from sys.master_files where database_id = DB_ID(N'master')
go

Move Msdb and Model

Now that we have the paths where the databases are currently installed, let’s move the MSDB and Model databases first. Again, in SQL Server Management Studio, run the below script from a new query window. Make sure you change the variable <path here> to the new destination for the files.

--MSDB db file
use master;
go
Alter Database MSDB
Modify File (Name = MSDBData, FILENAME = '<path here>\MSDBData.mdf')
GO
--MSDB Log file
ALTER DATABASE MSDB
MODIFY FILE (NAME = MSDBLog, FILENAME = '<path here>\MSDBLog.ldf');
GO
--MODEL db file
use master;
go
Alter Database Model
Modify File (Name = Modeldev, FILENAME = '<path here>\Model.mdf')
GO
--MODEL log file
ALTER DATABASE Model
MODIFY FILE (NAME = Modellog, FILENAME = '<path here>\Modellog.ldf');
GO

Verify the Move

I like to verify what I did by running the verify scripts again in step 1. Note that you have only updated MSDB and Model so far. Again, run the scripts below from a SQL Server Management Studio query window.

--Verify MSDB
select name, physical_name from sys.master_files where database_id = DB_ID(N'MSDB')
--Verify Model
select name, physical_name from sys.master_files where database_id = DB_ID(N'Model')
go

Move the Files

At this point, SQL Server has not physically moved any files but really just points us to the new destination, like most changes in SQL Server, we need to restart the services for the changes to take effect. But not yet, we have a couple more things to do!

Stop these services:

• SQL Server service
• SQL Agent service

Now, we need to physically move files (DB and log for MSDB and Model) from the previous source to the new destination or rename the folder to the new path. This can be done using the Windows file explorer. On a side note, do not forget to add security for the service accounts if you are moving to a completely new path. Services will not start if they cannot access the system databases.  To update security if needed, just right click on the folder from within windows file explorer and click on properties.

Then click on Edit and add the appropriate account that needs access.  Normally, this should be the SQL Service account.

Click add and select the user and click OK.

Then once you return to the previous screen, select the level of security required and click OK.

Restart SQL Server

Restart the SQL Services for the above changes to take effect.

Depending on how you are setup, you may need to restart services from either SQL Configuration Manager or from the Cluster Manager if you are running on a cluster. I am not going to go into how to restart services since all DBA’s should know how to do this.

Change the Master Location

Now for changing the Master database location. This is much different than the MSDB and Model databases. The location of the Master Database is driven by the startup parameters for SQL Server. These parameters are changed from within SQL Configuration Manger, on the startup tab.

First, open SQL Server Configuration Manager.

Right click on the SQL Server Service in the right window and click on properties.

Update the three startup paths to the new path, making sure to click the update button following each change. Click on each line in the Existing Parameters one by one and update the path.

At this point the Add button below will instead be an Update button.  Following each change to the path, make sure you click the Update button.  If the update button is not clicked, the change will not take effect.

Once all paths have been updated click ok to close the screen.

Again, before the changes above take effect, the services will need to be restarted. But not yet!

Change the Registry

Now, here’s where it gets a little ugly. There are 3 registry settings that need to be updated as well. Personally, I hate updating the registry, however, these settings are fairly benign so do not worry. If you are running a cluster, you will need to update these settings on each server within the cluster.  From the Start menu, run RegEdit.  This will open the registry.

Next on the Edit menu go to Find and search for SQLDataRoot, ErrorDumpDir and SQLAgent.Out separately.

I usually just do a search on the registry using the keys below, however, I have also included the full path location as well.  Once you have found each key, you will need to update the path to the new path you have moved the system databases to.  Just double click the key to open it to update.

SQLDataRoot located at

ErrorDumpDir located at

SQLAgent.Out located at

Restart SQL

Restart the SQL Services, again from SQL Configuration Manager if running SQL Server in a non-clustered environment or from Failover Cluster Manager if clustered. Verify that both the SQL Server Service and the SQL Agent have started.

Verify the Paths

Verify the new destinations with the scripts you ran in step 1. The paths should now show the new paths for Master, Model and MSDB system databases.

Conclusion

If for any reason the SQL Service does not come up or if on a cluster, the cluster services do not start, I always trouble shoot by looking at both the SQL Logs and the Event Viewer.  More than not, there was a path not updated or not updated correctly.  It is also a good idea to verify the path as well.  This is a very simple solution to a moderately complex problem. I call this a moderately complex problem because of my almost 18 years as a DBA, I have never been asked to move system databases before. That being said, there must be some other DBA’s in the same boat as me!