Move System and user databases

1. Master: For master database we have to take SQL offline and them modify startup parameter to point it to new drive letter where we want to move data/log file.

2. Model, msdb, tempdb, userdatabases: When SQL is online run below command to get current location detail for model database, and replicate physical drive path to new location and run alter database script, after take sql offline and move model data and log file to new location and start sql.
--For tempdb no need to copy old files since its recreated at start.

2a. msdb and user databases: You can detach and move files and attach it as well, when you detach msdb sql agent needs to be taken offline.


USE master;
GO
SELECT 'ALTER DATABASE '+QUOTENAME(DB_NAME(database_id))+' MODIFY FILE ( NAME = '''+name+''' , FILENAME = N'''+physical_name+''')',
       *
FROM   sys.master_files
WHERE  database_id = 3;