How to locate the database folder in sql 2005

We want to locate the database folder/files (where the databases are stored) like SQL Server Management Studio UI does,
when you click on attach database / Add.

The question is how to retrieve this folder/files programmatically (C# or VB, SMO?).

For example we want our application client to connect remotely to an SQL Server and attach a new database, using the folder/files obtained from the retrieved method.

If you run Profiler while you use SSMS you will see the code that it runs to populate the dialogs you open.

The code is t-sql so you will have to write your own C to do the same thing

|||

We tried profiler but we didn't find something to help us.

Thank you Anyway.

|||

Perhaps this will point you in the right direction:

SELECT physical_name
FROM sys.master_files
WHERE name = 'Northwind'

A little string manipulation and you should be fine.

|||Thank you but this is for a database already attached.|||

So..., if the database is not attached, then you can put it just about anywhere you wish.

There are 'default' locations, and there are the locations you decide to use. If you are not concerned about currently attached databases, and their locations, then what is the issue?

All servers will have at least master, tempdb, model, and msdb databases. If you can find where they are stored, then you can store your database there too.

|||I don't know the folder and the name of the database. That is why I want to locate it.|||

Remove the WHERE clause and get a list of all databases attached to the server, and their locations.

SELECT
name,
physical_name
FROM sys.master_files

Now... how to determine which one of these listed databases is the database you are seeking since you don't have a database name... You will be very, very, very lucky if there is nothing more than master, tempdb, model, msdb, -AND only the one database you seek.

Did I say, really, really, lucky...

Perhaps adding the following WHERE clause:

WHERE name = db_name()

But that may not always work, the (file) 'name' could be different from the logical database name; an improved query is:

SELECT
db_name( database_id )
name,
physical_name
FROM sys.master_files
WHERE database_id = db_id()

This last query should give you the logical database name, the filename, and the filepath.

|||

Verry verry sorry..

I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.

|||

GeoB wrote:

Verry verry sorry..

I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.

That would have been GOOD information to have earlier.

Since there is no certainly as to how a database file is named (it does not have to have a [mdf] suffix), you may have quite a problem there.

|||

When use SQL Server Management Studio UI you do know where the databases are stored ok?

I want to make something like that: http://www.kenix.eu/georgebakogiannis/LocateDatabaseFiles.png

|||

If you have a default instance on the box, then the default data and log locations are held in the registry at these locations:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultData
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultLog

However there's no guarantee that all database and log files will be found at these locations.

Chris