Move System Databases in the SQL Server Failover Cluster

Recently, I was assigned to a project – our customer purchased a new SAN, and they wanted us to replace the old one with a new SAN. User and system databases utilized SAN, meanwhile, the operating system and SQL were installed on the server’s local disks. Lucky us..!! The idea was to move the existing user and system databases to a new SAN and keep the downtime as minimum as possible. So, to minimize the downtime, instead of reinstalling OS and SQL, we decided to use the following approach.

1. Attach a new SAN to an existing server, create new drives on it, and add those drives to an existing failover cluster instance.
2. Stop SQL Server, copy master, model, and msdb databases on new drives and start the SQL services.
3. Move User databases to new drives and remove the old SAN.

In my previous article, I explained the step-by-step process of adding new storage to an existing failover cluster. Moreover, I demonstrated the process of moving user databases to new drives.

In this article, I am going to demonstrate the process of moving the system databases to new drives that I utilized in my project. In my previous article, we added the drive to an existing failover cluster and in this article we are going to use the same drive. The volume label of the drive is “E:\”, so I created a directory named “SystemDatabases” in E:\ drive. See the following image:

Currently, system databases are on “F:\MSSQL14.MSSQLSERVER\MSSQL\DATA” directory. We want to move these databases to the “E:\SystemDatabases” directory. Unlike user databases, moving system databases is not a simple process. We must follow the correct sequence and steps otherwise SQL instance won’t start.

 First, let’s review the current location of system databases. Execute the following query to get the current location of the system database.

USE MASTER

GO

SELECT DB_NAME(DATABASE_ID) AS [DATABASE NAME], 

       NAME AS [LOGICAL NAME], 

       PHYSICAL_NAME AS [FILE PATH] 

 FROM SYS.MASTER_FILES 

 WHERE DATABASE_ID < 4

The following is the output:

DatabaseName Logical Name File Path

-------------- ------------------- --------------------------------------------

master master F:\MSSQL14.MSSQLSERVER\MSSQL\DAT\master.mdf

master mastlog F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

tempdb tempdev F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb.mdf 

tempdb templog F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\templog.ldf

model modeldev F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\model.mdf

model modellog F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\modellog.ldf

msdb msdbdata F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\msdbdata.mdf

msdb msdblog F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\msdblog.ldf

Now I will explain the process of moving databases to another directory in the following manner.

1. Explain the steps of moving msdb and model databases.
2. Explain the steps of moving the tempdb database.
3. Explain the steps of moving the master database.

This demo will show you how to move all system databases to E:\ drive, hence to reduce downtime, so I will restart the SQL services at the end of the process.

Steps to move the msdb and model databases

To move the msdb and model databases, first, we need to update the file locations in the system catalog by executing the ALTER Database query. To do that, execute the following query in PowerShell Or SSMS.

USE MASTER; 

 GO 

 ALTER DATABASE MODEL MODIFY FILE (NAME = MODELDEV, FILENAME = 

 'E:\SYSTEMDATABASES\MODEL.MDF'); 

 GO 

 ALTER DATABASE MODEL MODIFY FILE (NAME = MODELLOG, FILENAME = 

 'E:\SYSTEMDATABASES\MODELLOG.LDF'); 

 GO 

 

ALTER DATABASE MSDB MODIFY FILE (NAME = MSDBDATA, FILENAME = 

'E:\SYSTEMDATABASES\MSDBDATA.MDF'); 

 GO 

 

 ALTER DATABASE MSDB MODIFY FILE (NAME = MSDBLOG, FILENAME = 

 'E:\SYSTEMDATABASES\MSDBLOG.LDF'); 

 GO

The output is as follows:

The file "MODELDEV" has been modified in the system catalog. The new path will be used the next time the database is started.

The file "MODELLOG" has been modified in the system catalog. The new path will be used the next time the database is started.

 

The file "MSDBDATA" has been modified in the system catalog. The new path will be used the next time the database is started.

The file "MSDBLOG" has been modified in the system catalog. The new path will be used the next time the database is started.

Now execute the following query to verify the paths are updated in the system catalog. To do that, execute the following query:

SELECT DB_NAME(DATABASE_ID)AS [DATABASE NAME], 

       PHYSICAL_NAME AS [FILE LOCATION] 

 FROM SYS.MASTER_FILES 

 WHERE  DB_NAME(DATABASE_ID) IN ('MSDB', 'MODEL') 

 GO

Following is the output:

DatabaseName Logical Name File Path

-------------- ------------------- --------------------------------------------

model modeldev E:\SystemDatabases\model.mdf

model modellog E:\SystemDatabases\modellog.ldf

msdb msdbdata E:\SystemDatabases\msdbdata.mdf

msdb msdblog E:\SystemDatabases\msdblog.ldf

Steps to move the tempDB database

When we restart the SQL Server – it will automatically recreate the tempdb and log files hence we don’t need to move the tempdb files manually – all we have to do is alter the path of the database files. To alter the path of the tempdb database files, execute the following query in PowerShell / SSMS.

USE MASTER; 

 GO 

ALTER DATABASE TEMPDB MODIFY FILE (NAME = TEMPDEV, FILENAME = 

'E:\SYSTEMDATABASES\TEMPDB.MDF');


 GO 

ALTER DATABASE TEMPDB MODIFY FILE (NAME = TEMPLOG, FILENAME = 

'E:\SYSTEMDATABASES\TEMPLOG.LDF');


 GO

The output is as follows:

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.

The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

Now execute the following query to verify the paths are updated in the system catalog:

SELECT DB_NAME(DATABASE_ID)AS [DATABASE NAME], 

       PHYSICAL_NAME AS [FILE LOCATION] 

 FROM SYS.MASTER_FILES 

 WHERE  DB_NAME(DATABASE_ID) = 'TEMPDB' 

 GO

 Following is the output:

DatabaseName Logical Name File Path

-------------- ------------------- --------------------------------------------

tempdb tempdev E:\SystemDatabases\tempdb.mdf 

tempdb templog E:\SystemDatabases\templog.ldf

Steps to move the master database

The process of moving the master database to another drive is different from moving other system databases. To move the system database, we will perform the following steps:

1. Open SQL Server configuration manager 2017 and expand click on SQL Server Services. See the following image:
   
2. Right-click on SQL Server (MSSQLSERVER) and click on properties. In the properties dialog box, select the “Startup Parameters” tab. See the following image:
   
3. As you can see in the image above, in existing parameters the text box contains the default location of the data file, Log file, and error log. Here -d indicates the default path of the datafile of the master database similarly, -l indicates the default path of the log file of the master database. We must replace these parameters with a new location. So click on the datafile parameter and replace it with “–  dE:\Systemdatabases\master.mdf” value and click on Update. Similarly, click on the log file parameter and replace it with “-lE:\Systemdatabases\mastlog.ldf” value and click on update. See the following image:

        4. Click OK to close the dialog box.

Note:

As we are performing this task on SQL Server failover instance, we must perform all the above changes on secondary nodes (SQL02.Dc.Local)

Restart the SQL Server services.

Once the new path of msdb, model, and tempdb are updated in the SQL Server catalog, we must stop the services to copy the database files on new drives. To do so, open SQL Server configuration manager 2017 >> Click on Services >> Right-click on SQL Server (MSSQLSERVER) and click on “Stop.” See the following image:

Now copy all the system database files on the new drive. See the following image: 

Now start the service from the SQL Server configuration manager. If the services started successfully then the path of the system and the databases have been updated successfully. Execute the following query to review the path of the master database:

SELECT DB_NAME(DATABASE_ID)AS [DATABASE NAME], 

       PHYSICAL_NAME AS [FILE LOCATION] 

 FROM SYS.MASTER_FILES 

 WHERE  DB_NAME(DATABASE_ID) = 'MASTER' 

 GO

Following is the output:

DatabaseName Logical Name File Path

-------------- ------------------- --------------------------------------------

master master E:\SystemDatabases\master.mdf 

master mastlog E:\SystemDatabases\mastlog.ldf

Summary

In this article, I have explained the step by step process of moving system databases to a new clustered disk.