Move the system databases to different locations in SQL Server

How to move the system databases to different locations in SQL Server:

We do come across some situations where we need to move the system databases to other drive keeping the growth of databases in mind.

Process to follow for the activity:

Steps:

1.       Update the Master data file and log file locations.

a.       Go to SQL Server Configuration manager

b.       Right click on the SQL Services { SQL Server(MSSQLServer) }

c.        Go to Advanced Tab, Modify the location of (-d) Master.mdf and (-l) Mastlog.ldf.

d.      Optional step: change the path of (-e) Errorlog as well.

2.       Use Alter database command to change the file location of MSDB, MODEL and TEMPDB databases.

Below Command to be performed for MSDB, MODEL and TEMPDB:

 Use Master

Go
Alter database database_name
      Modify file
      ( Name=logical_name_MDF, Filename='\\newlocation\physicalfilename.mdf');
Go
Alter database database_name
      Modify file
      ( Name=logical_name_LDF, Filename='\\newlocation\physicalfilename.ldf');
Go

3.       Stop SQL Server

4.       Move the MDF and LDF files of Master, Model, MSDB to the new locations defined in the earlier steps.

5.       Delete the TEMPDB OLD MDF and LDF (Optional Step)

6.       Start the SQL Server and check the new file locations

Command:

Select * from sys.sysaltfiles

GO

Hope the above information helps

Thanks