Moving SQL tempdb Database in SQL 2005

1. Launch SQL Server Management Studio and Log onto Server
2. Determine the logical file names for the tempdb database by using sp_helpfile as follows, click on New Query and run following script
   
   use tempdb
   go
   sp_helpfile
   go
   
   The logical name for each file is contained in the name column. This example uses the default file names of tempdev and templog.
3. Use the ALTER DATABASE statement, specifying the logical file name as follows:
   
   use master
   go
   Alter database tempdb modify file (name = tempdev,
   filename = ‘New_Drive:\New_Path\tempdb.mdf’)
   go
   Alter database tempdb modify file (name = templog,
   filename = ‘New_Drive:\New_Path\templog.ldf’)
   go

4. Stop and Restart SQL Server Service

Moving SQL master Database in SQL 2005

1. Launch SQL Server Configuration Manager
2. Click on SQL Server 2005 Services and in the right panel right click on SQL Server (instance_name) and click on Properties
3. On the Advanced tab, in the Startup Parameters box, type the parameters separated by semicolons (;).
4. Current Startup Parameters: -dD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
5. Change the path of master.mdf and mastlog.ldf.
6. New Startup Parameters: -dNew_Drive:\New_Path\master.mdf;-eD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lNew_Drive:\New_Path\mastlog.ldf
7. Stop SQL Server Service
8. Move master.mdf and mastlog.ldf to new location.  Make sure that permission are correctly set on the new directory.  If you do not give appropriate access to SQL, the SQL Server Service will not start.
9. Start SQL Server Service

Moving SQL model and msdb Databases in MS SQL 2005

1. Launch SQL Server Configuration Manager
2. Click on SQL Server 2005 Services and in the right panel right click on SQL Server (instance_name) and click on Properties
3. On the Advanced tab, in the Startup Parameters box, type the parameters separated by semicolons (;).
4. Current Startup Parameters: -dD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
5. Add -m, -c and T3608 to the Startup Parameters.
6. New Startup Parameters: -T3608;-m;-c;-dD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
7. Stop and start SQL Server
8. Detach the model database by using the following commands:
   
   use master
   go
   sp_detach_db ‘model’
   go
   
9. Move the model database from its current location to your desired location.  Make sure that you have the correct permissions in the new directory.
10. Reattach the model database by using the following commands:
    
    use master
    go
    sp_attach_db ‘model’,'E:\Sqldata\model.mdf’,'E:\Sqldata\modellog.ldf’
    go
    
11. Make sure you remove -m -c -T3608 from the Startup Parameters
12. Do the same with the msdb database.

NOTE: If you get the following message when starting a New Query DB is in single-user mode and there’s already someone connected to the DB.  Then make sure that you have disconnected from the object explorer before clicking on New Query.  So when you first launch SQL Server Management Studio, click cancel when it asks you to Connect to Server.  Then click on New Query, this will ensure that you have not logged on twice in single user mode.

The other error message is “Cannot detach an opened database when the server is in minimally configured mode” when trying to sp_detach_db ‘model’.  Best solution is to launch SQL Server Configuration Manager.  Click on SQL Server 2005 Services.  On the right pane, stop every service except the SQL Server (instance_name).  Also disable the network adapter if you still are getting the error message after stopping all services.