I keep seeing this question in many forums. When you fire "CREATE DATABASE <database name>" T-SQL command without any parameters. The database will get created and its file will be located in some path, lets say "C:\Program Files\MS SQL\Data". But if you want to have the files located else where, lets assume "D:\SQL_Data\" then there are two options that you can think of.
1 - specify the file path in the "CREATE DATABASE <database name>" command or
2- set the default path location in server configuration.
For option one refere to BOL, here is how to workout Option-II: again there are two ways to do this task. one, using GUI (Management Studio/Enterprise manager) - right click on server node, select properties from pop-up menu, look for "Database Settings" in that section there two boxes - one for LOG and other for DATA file. Key in the appropriate values and save it. This change does NOT require SQL Server re-start.
The other way to achieve this configuration change is using T-SQL, as show below,
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\SQL_Data'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'D:\SQL_Data'
GO
Yes, its instance specific registery entry that it takes to set this property. Here the command is using extended stored procedure "xp_instance_regwrite" to do the trick. This type of command can be used in cases where you want to standardise you SQL Server setup and you may want to add this step as a post installation procedure/batch file etc.
Update 12:18 PM 3/26/2009
Couple of people have asked me how to find the database file paths, i.e basically they are asking - how do I know which database is linked to which file. Here is the query,
select * from master.dbo.sysaltfiles --where db_name(dbid) = 'x_db_name'
Above query will display the list of all the database files located on that server. But if you want to to be spcific then use
select * from x_db_name.dbo.sysfiles
To understand more on interpreting the results (meaning of each column displayed) you may want to check documentation in "Book Online" aka. BOL !
