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
GoAlter 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