get the data & log file path

Question

get the data & log file path

shubhangi deshmukh

Valued Member

Points: 59
More actions
April 6, 2007 at 2:49 am

#93078

hi!
I need to know the default data file & log file path of database.Is there any stored procedure that can give the desired output or do i need to access registry?I know that i can view data & log file path thr' enterprise mgr.But i need this info in my front end app.
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

You DO need to access the registry... You can either do it from the front end or you can read them using the unsupported xp_RegRead stored procedure. For SQL Server 2000 on an XP box, the registry entries you seek have the following path in the registry...

My_Computer

HKEY_Local_Machine

Software

Microsoft

Microsoft SQL Server

servernamehere

MSSQLServer

DefaultLog

DefaultData

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Cory E. SSCertifiable Points: 7076 More actions · Answer 2

Are you looking for the default db location for a new DB? or, for an existing db?

For a new db, it uses model, so, this would work:

use

model

select

* from dbo.sysfiles

For any other db, this would work (if you just want to know where it is stored):

use <YourDB>

select * from dbo.sysfiles

Right? or, am I missing something?

-- Cory

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Cory,

Great idea but, the Model DB files don't show where new files would be made (as a default)... they only show where the current Model files are.

For the other DB's, then sysfiles will definitely work for where the files are currently located.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Cory E. SSCertifiable Points: 7076 More actions · Answer 4

Jeff,

I have never really looked that deeply into what the model is, but I thought the model was really nothing more than the base shell of any new db. All these options would be changeable, but that is the "template" of new db's.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/4e4f739b-fd27-4dce-8be6-3d808040d8d7.htm says “The model database is used as the template for all databases created on an instance of SQL Server.”

Perhaps I interrupted this wrong.

C

-- Cory

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

You are partially correct... all of the SQL objects will be constructed from what is in the MODEL... However, the listing from sysfiles shows the following file names for the Model database... (changed name of server to "servername" here just 'cuz)

d:\sql_data\MSSQL$servernamedata\model.mdf

d:\sql_data\MSSQL$servernamedata\modellog.ldf

But, the default files in the EM GUI (and in the registry) are...

E:\SQL_Data

D:\SQL_Log\

Totally different that what the Model database says.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Trevor Schmidt SSC Rookie Points: 30 More actions · Answer 6

If I remember correctly, DefaultLog and DefaultData mentioned early in the thread only exist if the admin has chosen paths in Enterprise Manager. Otherwise, you'll have to find the values chosen during setup:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<NamedInstance>\Setup

or

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLSERVER\Setup

...and look for SQLDataRoot

I had to write a script recently to do this check... in the end, I decided to create a dummy database and look at its sysfiles. Not elegant, but it works. (Assuming no one's bumped up the size of model... then it would be painful)

shubhangi deshmukh Valued Member Points: 59 More actions · Answer 7

Thanks Trevor & Cory.

I queried sysfiles & it worked.Actually I was looking for default data & log file path for new database.

Jonathan Bowser SSC Rookie Points: 35 More actions · Answer 8

Good call. I was wondering where this data was stashed just last week.