Problem: It occasionally becomes necessary to move SQL Server system databases’ file to a new location. In this blog article understand the process of moving tempdb, master, msdb, and model system databases to a different location, it is crucial to keep in mind that moving the resource database files is not possible.

How to create Mount drive in Local system

Steps to assign a mount-point folder path to a drive.

1. Go to Computer Management, Action -> All tasks->, and then click Change Drive Letter and Paths. 

2. To assign a mount-point folder path, click Add. Click Mount in the following empty NTFS folder, type the path to an empty folder on an NTFS volume, or click Browse to locate it. Here I created in E$ (E:\New folder\New Folder) 

Scenario

Named Instance Name: XXXXXXX 
Current location of system database mentioned in below table.

Created Mount Drive in my Local system E$ 

New Physical path in mount drive will be in E:\New folder\New Folder\MSSQL\DATA 

Moving TempDB database

1. Execute below script 
   –Moving TempDB
   
   Use master
   Go
   
   Alter database tempdb modify file (Name =tempdev, filename=’E:\New folder\New Folder\MSSQL\Data1\tempdb.mdf’)   
   Go 
   
   Alter database tempdb modify file (Name =templog, filename=’E:\New folder\New Folder\MSSQL\Data1\templog.ldf’) 
   Go 

2. Restart Services from Configuration manager.
3. Confirm Path of Database files using the query.

Moving model database 

1. Execute below script  
   Before Moving:
   use model 
   go 
   select name,physical_name ,* from sys.database_files 

Use master
go
alter database model modify file (Name =modeldev,filename=’E:\New folder\New Folder\MSSQL\Data1\model.mdf’)
go
alter database model modify file (Name =modellog,filename=’E:\New folder\New Folder\MSSQL\Data1\modellog.ldf’)
go

2. Stop Services from Configuration manager.
3. Copy the files to new location (E:\New folder\New Folder\MSSQL\Data1).
4. Restart services.
5. Confirm Path of Database files using the query.

Considering a career in cloud?

Moving msdb database 

1. Execute below script
   
   Before Moving:
   use msdb
   go
   select name,physical_name ,* from sys.database_files  

Use master
go
alter database msdb modify file (Name =MSDBData,filename=’E:\New folder\New Folder\MSSQL\Data1\MSDBData.mdf’)
go

Use master
go
alter database msdb modify file (Name =MSDBData,filename=’E:\New folder\New Folder\MSSQL\Data1\MSDBData.mdf’)
go
alter database msdb modify file (Name =MSDBLog,filename=’E:\New folder\New Folder\MSSQL\Data1\MSDBLog.ldf’)
go

2. Stop Services from Configuration manager.
3. Copy the files to new location (E:\New folder\New Folder\MSSQL\Data1).
4. Restart services.
5. Confirm Path of Database files using the query.

Moving the master database 

At present master database is present under following path E:\ SystemFile \MSSQL10_50. XXXXXXX \MSSQL\DATA\master.mdf and our requirement is to move its mdf and ldf file to the path name E:\New folder\New Folder\MSSQL\Data1. 

1. To move the master database, follow these steps.
2. From the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.
3. In the SQL Server Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.
4. In the SQL Server (XXXXXXX) Properties dialog box, click the Startup Parameters tab.
5. In the Existing parameters box, select the –d parameter to move the master data file.
6. In the Specify a startup parameter box, change the parameter to the new path of the master database.
7. In the Existing parameters box, select the –l parameter to move the master log file.
8. In the Specify a startup parameter box, change the parameter to the new path of the master database.
9. The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data file.

-dE:\ SystemFile \MSSQL10_50. XXXXXXX \MSSQL\DATA\master.mdf;-eE:\ SystemFile \MSSQL10_50. XXXXXXX \MSSQL\Log\ERRORLOG;-lE:\ SystemFile \MSSQL10_50. XXXXXXX \MSSQL\DATA\mastlog.ldf 

New Location for the master data file is E:\New folder\New Folder\MSSQL\Data1, the parameter values would be changed as follows:
-dE:\New folder\New Folder\MSSQL\Data1\master.mdf;-eE:\Documents + Ebooks\MSSQL10_50.TIFFANY\MSSQL\Log\ERRORLOG;-lE:\New folder\New Folder\MSSQL\Data1\mastlog.ldf 

10. Stop the instance of SQL Server by right-clicking the instance name and choosing Stop.
11. Move the master.mdf and mastlog.ldf files to the new location.
12. Restart the instance of SQL Server. 
13. Verify the file change for the master database by running the following query. 

Follow-up: After Moving All System Databases 

If you have moved all of the system databases to a new drive or volume or to another server with a different drive letter, make the following updates. 

• Change the SQL Server Agent log path. If you do not update this path, SQL Server Agent will fail to start. 
• Change the database default location. Creating a new database may fail if the drive letter and path specified as the default location do not exist. 

Change the SQL Server Agent Log Path 

1. From SQL Server Management Studio, in Object Explorer, expand SQL Server Agent.
2. Right-click Error Logs and click Configure.
3. In the Configure SQL Server Agent Error Logs dialog box, specify the new location of the SQLAGENT.OUT file.

4. The default location is E:\SystemFile\MSSQL10_50. XXXXXXX \MSSQL\Log\SQLAGENT.OUT\
5. Change it to E:\New folder\New Folder\MSSQL\LOG\ SQLAGENT.OUT 

Author: Mayank Minal, Principal Consultant, Fusion Practices