Default Data Directory

[paulhuts]

Hi,
How can I determine the default data directory property for an Instance of SQL Server.

I need to determine from within an application so I would use SQL-DMO or T-SQL.

-Paul

 

[rajeevnandanmishra]

Hi paul,
If i understood your question, you require to use db_name() to get the currently used database.
If you want some thing else please ignore it.

 

[tlbroadbent]

I found the following info under the topic "File Paths for SQL Server 2000" in SQL 2000 BOL.[ul]Specifying File Paths

In SQL Server 2000, due to multiple instance options, the instance name is used in addition to the user-specified location for program and data files. For tools and other shared files, however, instance names are not required.

Default Instance File Path for Program and Data Files

For the default instance of SQL Server, the default SQL Server directory name (Mssql) is used as the default instance name, along with the directory you specify.

For example, if you specify that the SQL Server default instance be installed at D:\MySqlDir, the file paths are:

D:\MySqlDir\Mssql\Binn (for program files)
D:\MySqlDir\Mssql\Data (for data files)

Named Instance File Path for Program and Data Files

For any named instances, the given name of the instance is used with the directory specified.

For example, if you specify that the instance named MyInstanceA be installed at D:\MySqlDir, the paths are:

D:\MySqlDir\MSSQL$MyInstanceA\Binn (for program files)
D:\MySqlDir\MSSQL$MyInstanceA\Data (for data files)[/ul]Additional info is available in the BOL topic "File Locations for Multiple Instances of SQL Server"[ul]Each named instance of Microsoft® SQL Server™ 2000 has a specific location for its program files and another for its data files that is different from that of the default instance of SQL Server.

For each named instance of SQL Server that you install, the default directories are:

\Program Files\Microsoft SQL Server\MSSQL$InstanceName\Binn for executable files.
\Program Files\Microsoft SQL Server\MSSQL$InstanceName\Data for data files.

Shared tools for all instances, both default and named instances, are located in the \Program Files\Microsoft SQL Server\80\Tools directory. You can specify file paths other than the default locations for program and data file for multiple instances.[/ul] Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin