Answer

This article refers to the actions required to move the default databases and transaction logs that are created by a SQL Server 2005 install (as User databases can be moved by detaching + moving + attaching them via SQL Server Management Studio), and should be used if either a default installation of SQL Server 2005 has been run or if the following custom installation action was chosen:

If a default installation was chosen, then the new destination will only have the .\Data\ directory created; however, if a custom install was chosen, then the following directory structure structure is created:

1. Open a DOS computer and run one of each of the following commands:
   • net stop mssqlserver or net start mssql$instancename. If the SQL Server Agent is also running, a confirmation prompt will appear to stop this service. 
   • net start mssqlserver /c /m /T3608 or net start mssql$instancename /c /m /T3608.
     
     /c = Shortens startup time when starting SQL Server from the command prompt. Typically, the SQL Server Database Engine starts as a service by calling the Service Control Manager. Because the SQL Server Database Engine does not start as a service when starting from the command prompt, use -c to skip this step.
     
     /m = Starts an instance of SQL Server in single-user mode. When you start an instance of SQL Server in single-user mode, only a single user can connect, and the CHECKPOINT process is not started. CHECKPOINT guarantees that completed transactions are regularly written from the disk cache to the database device. (Typically, this option is used if you experience problems with system databases that should be repaired.) Enables the sp_configure allow updates option. By default, allow updates is disabled.
     
     /T = Indicates that an instance of SQL Server should be started with a specified trace flag (trace#) in effect. Trace flags are used to start the server with nonstandard behavior.
     
     For more information about the available switches, refer to http://msdn2.microsoft.com/en-us/library/ms190737.aspx.
     Documented trace flags can be found here:  http://msdn2.microsoft.com/en-us/library/ms188396.aspx.
2. Open SQL Server Management Studio but do not allow it to make the first connection. Select new query and then run the following statements for the model and msdb databases:
   
   Use Master
   sp_detach_db 'model'
   GO
   sp_detach_db 'msdb'
   
   
   1. Move the model and MSDB databases and t-logs to their new locations.
   2. Modify the following SQL and then run it:
      
      sp_attach_db 'model','D:\MSSQL.1\MSSQL\Data\model.mdf','T:\MSSQL.1\MSSQL\Data\modellog.ldf'
      GO
      sp_attach_db 'msdb','D:\MSSQL.1\MSSQL\Data\msdbdata.mdf','T:\MSSQL.1\MSSQL\Data\msdblog.ldf'
      
      
   3. Run the following statement after modifying it for the tempdb:
      
      Use Master
      Alter database tempdb modify file (name = tempdev, filename = 'D:\MSSQL.1\MSSQL\Data\tempdb.mdf')
      go
      Alter database tempdb modify file (name = templog, filename = 'T:\MSSQL.1\MSSQL\Data\templog.ldf')
      
      
   4. Open SQL Server Configuration Manager, right-click the SQL Server service, and select Properties. In the Advanced tab, edit the Startup Paramaters value in order to set the new locations for the master database and t-log files:
      
      
      
      
   5. Run the following statement (modify as required) for the Resource database. Note that the Resource database must reside in the same location as the master database:
      
      Use Master
      ALTER DATABASE mssqlsystemresource
          MODIFY FILE (NAME=data, FILENAME= 'D:\MSSQL.1\MSSQL\Data\mssqlsystemresource.mdf')
      GO
      ALTER DATABASE mssqlsystemresource
          MODIFY FILE (NAME=log, FILENAME= 'T:\MSSQL.1\MSSQL\Data\mssqlsystemresource.ldf')
      
      
3. Open a DOS computer and run one of the following commands:
   
   net stop mssqlserver  or net start mssql$instancename
   
4. Delete the tempd.mdf and templog.ldf files from their presnt locations, as these will be created in their new ones.
5. Move the other .mdf and .ldf files to their new locations.
6. Open a DOS computer and run one of the following commands:
   
   net start mssqlserver /c /m /T3608 or net start mssql$instancename /c /m /T3608
   
   
7. ALTER DATABASE mssqlsystemresource SET READ_ONLY.
8. Open a DOS box and run one of the following commands:
   
   net stop mssqlserver  or net start mssql$instancename
   
   
9. Open a DOS box and run one of the following commands:
   
   net start mssqlserver or net start mssql$instancename
   
   
10. If the SQL Server Agent was running before this operation commenced, start it now via the SQL Server Configuration Manager.
11. If a default installation was chosen, then set the new values for both of the "Database default locations" options (Data: & Log:) by right-clicking the SQL Server in SQL Server Management Studio. If a custom installed was performed, then only the Log option requires changing: