Default datafile locations

[DBADoug]

Is there a way through Query Analyzer, via an sp_xxx, or other method, of determining what the default database file and database transaction log directories are? Right now, I go through Enterprise Manager, and look at the database settings tab for server properties.

Thanks,
Doug

 

[gmmastros]

If you are trying to determine where the data files are for a given database that is already attached to sql server...

Select name, filename From sysfiles



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DBADoug]

Thanks George, but I'm not doing that for any individual database. I can do an sp_helpdb <dbname> for that. What I'm looking for is a way to query the default data directory and the default log directory that SQL Server uses to assign database files upon creation.

Right now, I can only do that by right clicking on the server, selecting properties, and selecting the database settings tab.

 

[gmmastros]

Sorry that I misunderstood. I can't seem to find an answer to your question. I did find a registry entry for it.

hklm\software\microsoft\mssqlserver\mssqlserver

There is a DefaultData and a DefaultLog entry.

I know it's not what you are looking for. Hopefully someone will be able to help you. Good luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrdenny]

This code will return the data and log folders.

master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData'
go
master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultLog'
go

If the settings are not set it will return null.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[DBADoug]

Running Denny's code is what I was looking for. Thanks to both of you, George and Denny, for you help!!

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)