One day or another comes the day to move the location or System Databases in your work as a DBA. Could because you need to fix the initial installation or because you are doing a storage layout reorganization.
System Databases are four:
- Master
- Model
- MSDB
- Temp DB
Since, this is a delicated operation we should move them one by one, and I strongly recommend the following order: TempDB, MSDB, Model and last Master.
In the following steps E:/DATA is the destination path, you will have to change it to your new system databases location.
- Move Temp DB database:
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘E:DATAtempdb.mdf’);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘E:DATAtempLog.ldf’);
GO
Stop the instance, move the mdf and ldf files to the new location and start the instance.
- Move MSDB Database
USE master;
GO
ALTER DATABASE msdb
MODIFY FILE (NAME = MSDBData,FILENAME = ‘E:DATAMSDBData.mdf’);
GO
ALTER DATABASE msdb
MODIFY FILE (NAME = MSDBLog, FILENAME = ‘E:DATAMSDBLog.ldf’);
GO
Stop the instance and SQL Server Agent, move the mdf and ldf files and start the instance. Since MSDB is related to SQL server agent jobs, try to view the history jobs or run a job to verify the MSDB is working properly.
- Move Model Database
USE master;
GO
ALTER DATABASE model
MODIFY FILE (NAME = modeldev,FILENAME = ‘E:DATAmodel.mdf’);
GO
ALTER DATABASE model
MODIFY FILE (NAME = Modellog,FILENAME = ‘E:DATAmodelLog.ldf’);
GO
Stop the instance and SQL Server Agent, move the mdf and ldf files and start the instance. Since the model database is used for creating new databases a good test is to create a new one.
- Move MASTER Database
Open SQL Server Configuration Manager, locate the SQL Server Service, double-click and edit the Advanced Properties tab:
Change the startup parameters to the new location.
- “-d” flag refers to mdf data destination
- “-l” flag refers to ldf log destination
- “-e” flag refers to errorlog folder destination
PD: After moving tempDB, MSDB and/or Model I came across this error when trying to modify a job step:
After restarting the server, it works normally. I need to confirm that this is normal
Albandrod's Memory 