Moving model and msdb databases | SansSQL

Thursday, December 30, 2010

Moving model and msdb databases

Moving of model and msdb databases also follow the similar procedure as moving the tempdb database but with some additional steps.
Since these are also system databases, unfortunately we cannot move them just by detach and attach process, as we cannot attach or detach a system database.

Moving model database:
  1. First get the list of model database files by using this query
    select name,physical_name from sys.master_files where DB_NAME(database_id)='model'  
  2. Then for each model database file that you need to move, execute statements like below
    Alter Database model modify
    file (NAME = 'modeldev' ,
    FILENAME = 'Drive:\Path\model.mdf') -- Mention the new location

    Alter Database model modify
    file (NAME = 'modellog' ,
    FILENAME = 'Drive:\Path\modellog.ldf') -- Mention the new location
  3. Stop SQL Services
  4. Move the files manually to the new location
  5. Start SQL Services
  6. Verify the new Location
    select name,physical_name from sys.master_files where DB_NAME(database_id)='model'

Moving msdb database
:
  1. First get the list of msdb files by using this query
    select name,physical_name from sys.master_files where DB_NAME(database_id)='msdb'  
  2. Then for each msdb database file that you need to move, execute statements like below
    Alter Database msdb modify
    file (NAME = 'MSDBData' ,
    FILENAME = 'Drive:\Path\MSDBData.mdf') -- Mention the new location

    Alter Database msdb modify
    file (NAME = 'MSDBLog' ,
    FILENAME = 'Drive:\Path\MSDBLog.ldf') -- Mention the new location
  3. Stop SQL Services
  4. Move the files manually to the new location
  5. Start SQL Services
  6. Verify the new Location
    select name,physical_name from sys.master_files where DB_NAME(database_id)='msdb'
If the SQL Server Instance is configured with Database Mail option, then after the msdb movement you will have to verify that the database mail is working fine by sending a test email.

No comments:

Post a Comment