Move SQL System Databases to a new SAN location on a Cluster Server

The path parameters in Sql Configuration must be changed

Being a cluster don't forget to change it in all nodes.

Thanks for your replies.
I like the swapping storage drives approach, but downtime must be kept as short as possible.

Can you please check the following steps are correct? It's a two node active passive cluster.

- Stop SQL Server Agent (instance) on active Cluster Server.

- Change the path parameters in Sql Configuration
These must be changed on both active and passive Cluster servers?

-dC:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\master.mdf
-lC:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

- Run T-Sql Alter database script for tempdb, msdb, model and the master.
This must be run on both active and passive Cluster servers?

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

- Stop SQL Server (instance)

- Move the databases master/masterlog model/modellog MSDBData/MSDBLog to the new location.
No need to move the tempdb/templog, they will be recreated on startup.

- Start SQL Server (instance) and SQL Server Agent (instance).

- Next step is to detach and attach all user databases one after the other.

- Finally Quorum and MSDTC.
These can be recreated with SQL server and Agent running?

Any tips?

Thanks for all the info. I started today with the TEST instance, but there is some bad news here. The instance does not want to come back online after moving the System Databases.

I stopped De SQL Server Agent (TEST) and ran the following scripts:

ALTER DATABASE model MODIFY FILE (NAME = modeldev , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA')
ALTER DATABASE model MODIFY FILE (NAME = modellog , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA')

ALTER DATABASE msdb MODIFY FILE (NAME = MSDBData , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA')
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBLog , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA')

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA')
ALTER DATABASE tempdb MODIFY FILE (NAME = templog , FILENAME = 'N:\MSSQL10.TEST\MSSQL\Data')

Result:
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.
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.

Stopped the SQL Server (TEST) and changed the Startup Parameters:

Original Startup Parameters:
-dY:\MSSQL10.TEST\MSSQL\DATA\master.mdf;-eY:\MSSQL10.TEST\MSSQL\Log\ERRORLOG;-lY:\MSSQL10.TEST\MSSQL\DATA\mastlog.ldf

New Startup Parameters:
-dK:\MSSQL10.TEST\MSSQL\DATA\master.mdf;-eK:\MSSQL10.TEST\MSSQL\Log\ERRORLOG;-lK:\MSSQL10.TEST\MSSQL\DATA\mastlog.ldf

After bringing the TEST instance back Online it didn't want to start anymore. It looks like a rights issue.

Errorlog:
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file K:\MSSQL10.TEST\MSSQL\DATA for file number 1.  OS error: 5(Access is denied.).
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "K:\MSSQL10.TEST\MSSQL\DATA". Operating system error 5: "5(Access is denied.)".
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file K:\MSSQL10.TEST\MSSQL\DATA for file number 2.  OS error: 5(Access is denied.).
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "K:\MSSQL10.TEST\MSSQL\DATA". Operating system error 5: "5(Access is denied.)".
Error: 5105, Severity: 16, State: 1.
A file activation error occurred. The physical file name 'K:\MSSQL10.TEST\MSSQL\DATA' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Error: 945, Severity: 14, State: 2.
Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server.
Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

The folder rights of original and new location are the same, except for a Username MSSQL@TEST.
I can't find this user MSSQL@TEST anywhere in AD. I added the SQL service account, but still the TEST instance doesn't want come back online.

What can be the problem here?

Thanks Daniel,

A big mistake was made, messed it up here. It should be:

ALTER DATABASE model MODIFY FILE (NAME = modeldev , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA\model.mdf')
ALTER DATABASE model MODIFY FILE (NAME = modellog , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA\modellog.ldf')

ALTER DATABASE msdb MODIFY FILE (NAME = MSDBData , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA\MSDBData.mdf')
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBLog , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA\MSDBLog.ldf')

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE (NAME = templog , FILENAME = 'N:\MSSQL10.TEST\MSSQL\Data\templog.ldf')

Select allOpen in new window

There are 3 instances on the server:
SQL01
SQL04\Production
SQL05\TEST

Is the following command correct for startup in master-only mode for the test instance SQL05\TEST?

NET START mssql$SQL05\TEST /f /T3608
or
NET START mssql$TEST /f /T3608
or
NET START "SQL05\TEST" /f /T3608

Select allOpen in new window

Can SSMS T-SQL query be used for this instance in startup master-only mode?

If sqlcmd must be used is the following command correct, when I put the ALTER DATABASE... lines into D:\Script\TEST.sql?

sqlcmd -S "SQL05\TEST" -i "D:\Script\TEST.sql" -o "D:\Script\Output.txt"

Select allOpen in new window

Hi,
Is the following command correct for startup in master-only mode for the test instance SQL05\TEST?
>> NET START mssql$TEST /f /T3608

Can SSMS T-SQL query be used for this instance in startup master-only mode?
>> Unfortunately not

If sqlcmd must be used is the following command correct, when I put the ALTER DATABASE... lines into D:\Script\TEST.sql?
>> Try this one:

sqlcmd -S "SQL05\TEST" -d master -i "D:\Script\TEST.sql" -o "D:\Script\Output.txt"

Select allOpen in new window

Thanky you! That did the trick. Instance TEST is corrected and back online.

I've requested that this question be closed as follows:

Accepted answer: 0 points for NicoNL's comment #a40553189
Assisted answer: 125 points for Vitor Montalvão's comment #a40548877
Assisted answer: 125 points for ryanmccauley's comment #a40549528
Assisted answer: 250 points for Daniel_PL's comment #a40551957

for the following reason:

SQL is mirated to new SAN