Sunday, 25 August 2013

Moving model and msdb databases

Moving model database:
  1. First get the list of model database files by using this query
    select name,physical_name from sys.master_files whereDB_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 whereDB_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 whereDB_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 whereDB_NAME(database_id)='msdb'