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 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
Share this
Previous story
← Why Would I Upgrade to SQL Server 2012?
You May Also Like
These Related Stories
How to Move Master Database to a New Location in SQL Cluster
How to Move Master Database to a New Location in SQL Cluster
Sep 11, 2012
1
min read
How to move Oracle DB to New Oracle Home on Same Windows Server
How to move Oracle DB to New Oracle Home on Same Windows Server
Nov 25, 2013
3
min read
The life of a happy remote DBA
![](https://www.pythian.com/hubfs/Imported_Blog_Media/IMG_0024.jpg)
The life of a happy remote DBA
Jun 20, 2018
2
min read
No Comments Yet
Let us know what you think