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:
- Change the startup parameters of SQL Server service using SQL Server Configuration Manager
- change the path of master.mdf to the new path inside the –d flag
- change the path of mastlog.ldf to the new path inside the –l flag
- net stop mssqlserver
- move the files from the old location to the new location
- net start mssqlserver
msdb database
To move the master database files you have to perform the following steps:
- run a command prompt (cmd)
- net stop mssqlserver
- net start msqlserver /c /m /T3608
- 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)
- on the Sqlcmd prompt execute the following script :
use master
go
sp_detach_db 'msdb'
go
- move the msdbdata.mdf and msdblog.ldf files to the new location
- net stop mssqlserver
- net start mssqlserver
- 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 :
- 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
- net stop mssqlserver
- move tempdb.mdf and templog.ldf files to the new location
- net start mssqlserver
model database
to move model database :
- run a command prompt
- net stop mssqlserver
- net start mssqlserver /c /m /T3608
- run SQLCMD
- detach the model database :
use master
go
sp_detach_db 'model'
go
- move the model.mdf and modellog.ldf files to the new location
- attach the model database :
use master
go
sp_attach_db 'model','<new location>\model.mdf','<new location>\modellog.ldf'
go
- net stop mssqlserver
- net start mssqlserver
