07 March 2012

Moving MSDB to new location

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
Screen001
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
Screen002
Step 3: Stop SQL Server service
Screen003
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/)

3 comments:

  1. ALTER DATABASE MSDB
    MODIFY FILE
    (
    NAME = MSDBLog,
    FILENAME= 'C:\SQLDB\Demo\MSDBLog.mdf'
    )
    GO

    It should be LDF. If you leave it MDF SQL will not find the Log because logs are LDF

    ReplyDelete
  2. Anne,
    Thanks for drawing my attention, it's a typo. I have made correction to the code.

    Regards
    Hemantgiri

    ReplyDelete
  3. Excellent post.
    Tal

    ReplyDelete

I appreciate your time, thanks for posting your comment. I will review and reply to your comment as soon as I can.

Thank you
Hemantgiri