How to change path of system databases on SQL 2005

Master

  1. Open SQL Server Configuration Manager.
  2. Right Click on SQL Server Service and select properties.
  3. On SQL Server Proterties window select Advanced tab.
  4. Select Startup Parameters
  5. Edit the startup parameters to reflect the new path for –d and -l parameters.
  6. Stop SQL Service.
  7. Move the database files to their new locations.
  8. Start SQL Service.

Model

  1. Run the following query:
    USE master;
    GO
    alter database model MODIFY FILE (NAME = modeldev,FILENAME=’NEW PATH’);
    go
    alter database model MODIFY FILE (NAME = modellog,FILENAME=’NEW PATH’);
    go
  2. Stop SQL Service.
  3. Move the database files to their new locations.
  4. Start SQL Service.

Msdb

  1. Run the following query:
    USE master;
    GO
    alter database msdb MODIFY FILE (NAME = MSDBData,FILENAME=’NEW PATH’);
    go
    alter database msdb MODIFY FILE (NAME = MSDBLog,FILENAME=’NEW PATH’);
    go
  2. Stop SQL Service.
  3. Move the database files to their new locations.
  4. Start SQL Service.

Tempdb

  1. Run the following query:
    USE master;
    GO
    alter database tempdb MODIFY FILE (NAME = tempdev,FILENAME=’NEW PATH’);
    GO
    alter database tempdb MODIFY FILE (NAME = templog,FILENAME=’NEW PATH’);
    GO
  2. Stop SQL Service.
  3. Delete the database files as they will be recreated on new locations.
  4. Start SQL Service.

mssqlsystemresource

  1. Stop SQL Service.
  2. Start the services using NET START MSSQLSERVER /f /T3608 (*MSSQLSERVER is for default instance, if you have installed named instance then you need to use NET START MSSQL$Instancename /f /T3608)
  3. Run the following query from sqlcmd
    USE master;
    GO
    alter database mssqlsystemresource MODIFY FILE (NAME = data,FILENAME=’NEW PATHmssqlsystemresource.mdf’);
    go
    alter database mssqlsystemresource MODIFY FILE (NAME = log,FILENAME=’NEW PATHmssqlsystemresource.ldf’);
    go
    alter database mssqlsystemresource set READ_ONLY;
    go
  4. Stop SQL Service.
  5. Move the database files to their new locations.
  6. Start SQL Service.