Moving MSDB to a New Location

Step 1: Let’s query sys view and note down the location of the database files

SELECT
NAME,
PHYSICAL_NAME AS ‘PhysicalFilePath’,
STATE_DESC AS ‘DB Status’
FROM SYS.MASTER_FILES

Step 2: Run alter database and specify new location for database

SELECT
ALTER DATABASE MSDB
MODIFY FILE
(
NAME = MSDBData,
FILENAME= ‘C:\SQLDB\Demo\MSDBData.mdf’
)
GO
ALTER DATABASE MSDB
MODIFY FILE
(
NAME = MSDBLog,
FILENAME= ‘C:\SQLDB\Demo\MSDBLog.ldf’
)
GO

Step 3: Stop SQL Server service

Step 4: Once SQL Server service is stopped, move MSDB database to the new location
Step 5: Now, start SQL Server service. This time it will use the new path that we have configured in Step 2.

Note: If you have enabled and configure Database Mail, please make sure it works after you moved MSDB to the new location.

Tomorrow, I will post about how to relocate Master database.

— Hemantgiri S. Goswami