Moving MSDB to a New Location

Posted in: Technical Track
In recent past we had a situation where in, we were required to move MSDB, Model and Master databases to a new location, the reason being a faulty drive. While moving the system databases to the new location we needed to be extra cautious. Let’s see the process step-by-step.

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 1

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 1

Step 3: Stop SQL Server service
Step 1

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

email

Interested in working with Hemantgiri? Schedule a tech call.

About the Author

Lead Database Consultant
I am a Database Administrator by profession, and a student of a university called life by heart. I am passionate about SQL Server, photography, reading and sharing. Currently, I'm Lead Database Consultant @Pythian. I have been a Microsoft SQL Server MVP for four years, and a published author of the book - SQL Server 2008 High Availability.Keep in touch with me on twitter @ghemant

4 Comments. Leave new

For log file alter, it should be .ldf

FILENAME= ‘C:\SQLDB\Demo\MSDBLog.ldf’

Reply
Hemantgiri S. Goswami
October 5, 2012 2:07 am

Thanks for drawing my attention Santosh, I will modified the typo.

Regards
Hemantgiri

Reply

You did not modify the log file path from your syntax :)

Reply
Hemantgiri S. Goswami
September 18, 2015 10:04 pm

Hi,
The syntax is updated.

Thank you

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *