How to create a database and assign filenames?

[sab4you]

This is quite the newbie question, pritty much because I am one

I want to create a single SQL database on MS SQL 2000. I want to create it with two different filegroups, but I want the files to be stored in the default location to whatever the SQL Server wants.

I figured out this:

USE master
GO
CREATE DATABASE Database
ON PRIMARY
( NAME = SPri1_dat, FILENAME = 'c:\KJT_OPEN_Primary.mdf' ),

FILEGROUP SECONDARY
( NAME = SGrp1Fi1_dat, FILENAME = 'c:\KJT_OPEN_Secondary.ndf' )

But that obviously puts the file's into the directory C:\ I choose. I need them to go into whatever location the SQL server is defaulting to but still need to be able to name em.

GO

 

[abhijit74]

Wouldn't replacing the c:\IChoose with the default sql server directory be a obvious option?

-Abhijit

 

[sab4you]

thankas abhijit74 thats exactly what I want to do.

problem is I do not know how to do this!!

Can you rewrite my line so it uses the defautl sql directory and show me?

 

[tlbroadbent]

If your login has appropriate permissions, you can execute xp_regread to find the default data path.

DECLARE @value varchar(128)
EXEC master..xp_regread
@rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\MSSQLServer\Setup',
@value_name='SQLDataRoot',
@value=@value OUTPUT

Another option is to find the path of the master database or another user database in the sysfiles table.

Use Master
DECLARE @value varchar(128)
Select @value=Left(filename,charindex('\',filename,4)-1)
From Sysfiles
Where fileid=1
Print @value

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[sab4you]

using the first, it always points me to:

C:\Program Files\Microsoft SQL Server\MSSQL

which isnt the default path

and using the second it pointed me to:
C:\Program Files

This is after I went into SQL properties and changed me default data and log directory to e:\ (no reboot/restart though)

 

[sab4you]

Using your lead, I found this will work for me. It will give the path of the databases. If its blank then its using the default location, which you would then use the sysfiles in the master database.

DECLARE @value varchar(128)

EXEC master..xp_regread
@rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
@value_name='DefaultData',
@value=@value OUTPUT



print @value