How to move msdb database files in SQL Server

Detailed steps on how to move msdb database files (mdf and ldf) from one location to another. This change requires a restart of your SQL Server Instance, so make sure to do this during a maintenance window.

1. First find the msdb database files location which we want to move. To do that, execute the following query.

use master
go
select db_name(database_id) 'DatabaseName', name 'LogicalFileName',physical_name 'PhysicalFileName'
from sys.master_files
where database_id = db_id('msdb')
go

2. Now that you have the logical and physical file names, navigate to the new folder or drive where you have to move the files. That is., the destination folder or directory. Right click on the destination folder and give full access to the SQL Server service account.

3. Now update the new file locations in system catalog (master database) using the below query. This will update the new database files location for msdb database in the master database.

use master
go
alter database msdb
modify file (name = MSDBData, filename = 'F:\MSSQL\NewLocation\MSDBData.mdf');
go
alter database msdb
modify file (name = MSDBLog, filename = 'L:\MSSQL\NewLocation\MSDBLog.ldf');
go

4. Stop your SQL Server Instance.

5. Now that the SQL Server Instance is stopped, move the files physically from old location to new location.

6. Once the files are moved, start your SQL Server Instance. Make sure SQL Server Agent is also started successfully.

7. Run the below query to verify the new path of msdb database.

use master
go
select db_name(database_id) 'DatabaseName', name 'LogicalFileName',physical_name 'PhysicalFileName'
from sys.master_files
where database_id = db_id('msdb')
go

8. As a final step test your Database Mail if it is enabled using the below steps.

• Service Broker should be enabled in the msdb database for Database mail to work, run the below query to check

select is_broker_enabled  from sys.databases where name = 'msdb'

• Send a test mail to make sure mails are working. (You may use sp_send_dbmail or Database Mail in SQL Server Management Studio)

Click on the below link to learn more on moving other system databases and user databases in SQL Server.

Move User and System Databases in SQL Server