In recent past we have a situation where in we required to move MSDB, Model and Master databases to new location, the reason being faulty drive. While moving system databases to new location we need to be extra cautious. Let’s see the process step-by-step.
Step 1: Let’s query sys view and note down the location for 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 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 new location.
Tomorrow, I will post about how to relocate Master database.
-- Hemantgiri S. Goswami (http://www.sql-server-citation.com/)
