How to find the default database location

Question

How to find the default database location

SQLAJ

Ten Centuries

Points: 1359
More actions
June 12, 2009 at 2:39 pm

#131899

I have created several sql scripts to create a database, the tables, insert data...
I have to maintain this for different developer machines and two different servers.
The problem being each machine/server has a different location for the database files.
The developers mostly have just a C: drive, but some have a D: drive and others have an E: drive.
The servers have different locations as well.
I am trying to find a way (stored proc, or other) to find the default location the databases so I can allow for that in my scripts.
I know it is available some how. I just don't know where it is located or how to get it.
Any thoughts?
Thanks
______________________________
AJ Mendo | @SQLAJ

Viewing 12 posts - 1 through 11 (of 11 total)

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

|--|999 SSC Eights! Points: 827 More actions · Answer 1

Hi

Go to the MS-SQL instance and right click, then Properties>Database Settings. Once there, the last portion will tell you the default location for data and log files.

AlexSQLForums SSChampion Points: 14277 More actions · Answer 2

AlexSQLForums

SSChampion

Points: 14277

June 12, 2009 at 3:12 pm

#1008912

use master

go

sp_helpfile

Alex S

SQLAJ Ten Centuries Points: 1359 More actions · Answer 3

Thanks.

But it seems I was not clear.

I need (want) to do this auto-magically based on the different users/server configurations.

I know how/where to find in SSMS.

It is not feasible to manually check, then change the code before each time.

______________________________
AJ Mendo | @SQLAJ

george sibbald SSC Guru Points: 104210 More actions · Answer 4

If the default database file locations have been changed the values are stored in the registry so use xp_regread for

HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MICROSOFT SQL SERVER\MSSQL.1\MSSQLSERVER

and check the values of defaultdata and defaultlog.

If these values have not been set these entries will not exist so you can then query sys.master_files for the file locations of the model database.

---------------------------------------------------------------------

SQLAJ Ten Centuries Points: 1359 More actions · Answer 5

Thanks. I'll look into that.

______________________________
AJ Mendo | @SQLAJ

Jeffrey Williams SSC Guru Points: 90342 More actions · Answer 6

BTW - if you do not specify file locations in your create database statement, the default location defined for that instance will be used.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004550 More actions · Answer 7

Jeffrey Williams (6/12/2009)
BTW - if you do not specify file locations in your create database statement, the default location defined for that instance will be used.

Voice of reason, right there.

--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)

SQLAJ Ten Centuries Points: 1359 More actions · Answer 8

Hummm. In my haste of trying to figure this out I missed the tree in spite of the forest. I forgot about that.

I think this will be the better solution. That way I don't have to figure out all of the different options and code for the different ones.

Thanks to all. I will try this one.

Cheers!

______________________________
AJ Mendo | @SQLAJ

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

Of course, if you do need to find the default location from within T-SQL, this is what I've been using:

Select @DfltDir = Replace(physical_name, '\master.mdf', '\')

from master.sys.database_files

where name = 'master'

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

george sibbald SSC Guru Points: 104210 More actions · Answer 10

Barry,

should the default database file locations have been set via the database settings in SSMS properties, your query would not be guaranteed to return the default locations for database files.

If the default had not been changed, as create database uses the model database as a template, I would prefer to base the query you posted on the file locations for the model database.

---------------------------------------------------------------------

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

george sibbald (6/14/2009)
Barry,
should the default database file locations have been set via the database settings in SSMS properties, your query would not be guaranteed to return the default locations for database files.
If the default had not been changed, as create database uses the model database as a template, I would prefer to base the query you posted on the file locations for the model database.

Yep, you're right. Hmm, I thought I had a correct one around here somewhere...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

How to find the default database location

Cookies on SQLServerCentral