Move SQL 2014 System Databases to Different Drive

  • by

SQL 2014 SP1 Servers installed all System Databases in the following default location C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA

We had changed the User Database & Logs, TEMP DB, and Backup Directory during the SQL 2014 Installation for CITRIX-SQL01, but NOT the System Database Directory

CTX-SQL-01

Please refer to the following steps on how to move MSDB, Model & Master Database to D:\DATABASE\SYSTEM 

A. Move MSDB & Model System Databases 

Login to SQL Management Studio, and execute the following SQL Script to move the MSDB & MODEL Database to D:\DATABASE

USE master
ALTER DATABASE msdb
MODIFY FILE (NAME='MSDBData' , FILENAME='D:\DATABASE\SYSTEM\MSDBData.mdf')

ALTER DATABASE msdb
MODIFY FILE (NAME='MSDBLog' , FILENAME='D:\DATABASE\SYSTEM\MSDBLog.ldf')

ALTER DATABASE model
MODIFY FILE (NAME='modeldev' , FILENAME='D:\DATABASE\SYSTEM\model.mdf')

ALTER DATABASE model
MODIFY FILE (NAME='modellog' , FILENAME='D:\DATABASE\SYSTEM\modellog.ldf')

Stop the Services for MSSQLSERVER and copy MDF & LDF Files for MSDB & MODEL  to D:\DATABASE\SYSTEM and start the MSSQLSERVER Service back

Stop-Service MSSQLSERVER -force

Copy-Item "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf" -Destination "D:\Database\System\MSDBData.mdf"
Copy-Item "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf" -Destination "D:\Database\System\MSDBLog.ldf"
Copy-Item "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\model.mdf" -Destination "D:\Database\System\model.mdf"
Copy-Item "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\modellog.ldf" -Destination "D:\Database\System\modellog.ldf"

Start-Service MSSQLSERVER

Confirm MSDB & Model MDF & LDF had been moved to D:\DATABASE\SYSTEM successfully

SELECT name, physical_name AS current_file_location
FROM sys.master_files

CTX-SQL-02

B. Move MASTER System Databases 

Go to SQL Server Configuration Manager -> SQL Server (Properties) –> Update the Location for Master Database -dD:\DATABASE\SYSTEM & -lD:\DATABASE\SYSTEM CTX-SQL-03

Stop MSSQLSERSERVER services and manually copy master.mdf & mastlog.ldf to D:\DATABASE\SYSTEM and start MSSQLSERVER back

Stop-Service MSSQLSERVER -force

Copy-Item "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Master.mdf" -Destination D:\Database\System\Master.mdf
Copy-Item "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Mastlog.ldf" -Destination D:\Database\System\Mastlog.ldf

Start-Service MSSQLSERVER 
Start-Service SQLSERVERAGENT

Confirm MASTER MDF & LDF had been moved to D:\DATABASE\SYSTEM successfully

SELECT name, physical_name AS current_file_location
FROM sys.master_files

CTX-SQL-04

I will applied the same to the 2nd Node of CITRIX-SQL02 Server