[MSSQL 2005] system database 경로변경 방법

굉장히 간단한 방법으로 시스템 데이터베이스를 이동할 수 있다.
물론 서비스 중에는 안된다.

원본 : http://www.sp-configure.com/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 PATH\mssqlsystemresource.mdf’);
   go
   alter database mssqlsystemresource MODIFY FILE (NAME = log,FILENAME=’NEW PATH\mssqlsystemresource.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.