1.) Run the following Transact SQL to change location of the model, msdb and tempdb.
ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME = 'Type_New_Location_Here\model.mdf' )
GO
ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME = 'Type_New_Location_Here\modellog.ldf' )
GO
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData , FILENAME = 'Type_New_Location_Here\MSDBData.mdf' )
GO
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBLog , FILENAME = 'Type_New_Location_Here\MSDBLog.ldf' )
GO
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev , FILENAME = 'Type_New_Location_Here\tempdb.mdf' )
GO
ALTER DATABASE tempdb MODIFY FILE ( NAME = templog , FILENAME = 'Type_New_Location_Here\templog.ldf' )
GO
2.) Stop SQL Service
3.) Copy Physical Files to new location, no need to copy TempDB files as these are recreated when SQL Server is started.
4.) Start SQL Service
Check new file location for Databases:
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('Database Name Here');
GO
5.) Once you are completely happy that the files are in the correct location, delete them from the old location.
Moving Master and MS Resource Database.
1.) Change Startup Parameter of SQL Service in SQL Server Configuration Manager to specify new file locations for the Master Database.
-dType_New_file_location_here\master.mdf;-eE:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lType_New_file_location_here\mastlog.ldf
2.) Stop SQL Server Service
3.) Start SQL services in master only recovery mode
--For Default Instance
NET START MSSQLSERVER /f /T3608
--For Named Instance
NET START MSSQL$instancename /f /T3608
4.) Run the following using sqlcmd
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= 'Type_New_file_location_here\mssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= 'Type_New_file_location_here\mssqlsystemresource.ldf');
GO
5.) Place MS System Resource Database into Read Only mode by running the following Transact
ALTER DATABASE mssqlsystemresource SET READ_ONLY;
6.) Restart SQL Server Service
7.) verify new file location with the following Transact SQL Statement:
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('master');
GO
