Changing databases default locations

By default the location of SQL Server 2005 databases files is C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data.

Changing user databases default locations

After installing SQL Server 2005 you can change the default location for all user databases that will be created on the server, to do it you have just to modify the database default locations on the server property page.

or by executing the code above :
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'E:\UserDB'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'E:\UserLOG'
GO

Changing system databases default locations

master database

To move the master database files you have to perform the following steps:

1. Change the startup parameters of SQL Server service using SQL Server Configuration Manager
2. 
3. change the path of master.mdf to the new path inside the –d flag
4. change the path of mastlog.ldf to the new path inside the –l flag
5. net stop mssqlserver
6. move the files from the old location to the new location
7. net start mssqlserver       

msdb database

To move the master database files you have to perform the following steps:

1. run a command prompt (cmd)
2. net stop mssqlserver
3. net start msqlserver /c /m /T3608
4. run SQLCMD (be sure that all other SQL Server Services are stopped and that there is no application that will try to connect to SQL Server) 
5. on the Sqlcmd prompt execute the following script : 

   use master
   go
   sp_detach_db 'msdb'
   go




6. move the msdbdata.mdf and msdblog.ldf files to the new location



7. net stop mssqlserver



8. net start mssqlserver



9. execute the script
   
   
   

   use master
   go 
   sp_attach_db 'msdb','<new location>\msdbdata.mdf','<new location>\msdblog.ldf' 
   go

tempdb database

To move tempdb database  :

1. Execute the following SQL script :   USE master
   
   GO
   
   
   ALTER DATABASE tempdb
   
   
   MODIFY FILE (NAME = 'tempdev',FILENAME = '<new location>\tempdb.mdf')
   
   
   GO
   
   
   ALTER DATABASE tempdb
   
   
   MODIFY FILE (NAME = 'templog',FILENAME = '<new location>\templog.ldf')
   
   
   GO
   



2. net stop mssqlserver



3. move tempdb.mdf and templog.ldf files to the new location



4. net start mssqlserver

model database

to move model database :

1. run a command prompt



2. net stop mssqlserver



3. net start mssqlserver /c /m /T3608



4. run SQLCMD



5. detach the model database : 

   use master
   go
   sp_detach_db 'model'
   go




6. move the model.mdf and modellog.ldf files to the new location



7. attach the model database :
   

   use master
   go
   sp_attach_db 'model','<new location>\model.mdf','<new location>\modellog.ldf'
   go




8. net stop mssqlserver



9. net start mssqlserver