Files associated with SQL server

Hi all,
Is there any any to find the files that are associated with a SQL server Instance ? Does SQL server provide any API for that ?

Hi,

Each instance installs in a different directory. If you look at you default installation directory [C:\Program Files\Microsoft SQL Server], you will find a dierctory for each instance and for each feature (e.g. SSIS, SSAS, etc). These directories are called MSSQL.x, the x separating out the features and instances. So e.g. You have installed 2 instances of SQL Server, one of SSAS and one of SSRS, you directory structure would look something like:

C:\Program Files\Microsoft SQL Server\90 - SQL Binaries (SQL 2005).

C:\Program Files\Microsoft SQL Server\MSSQL.1 - SQL Database Engine Instance 1

C:\Program Files\Microsoft SQL Server\MSSQL.2 - SQL SSAS

C:\Program Files\Microsoft SQL Server\MSSQL.3 - SQL SSRS

C:\Program Files\Microsoft SQL Server\MSSQL.4 - SQL Database Engine Instance 2

There is nothing stopping users from moving these database files around so to check that you have the correct path you could execute

Code Snippet

select * from sys.database_files

from the instance and that will confirm the location of the database files.

|||

You can run OSQL (sql 2000) or SQLCMD (2005) command to get files details against a instance.

select *from sysaltfiles -- SQL Server 2000

select *From sys.master_files -- SQL Server 2005

Eg. (for SQL 2005)

C:>sqlcmd -SYourInstanceName -UUserName -PPassword -dMaster -q"Select Physical_Name From Sys.Master_Files"

Read more about SQLCMD in BOL (OSQL if its sql 2000)

Madhu

|||

Are you looking for user databases files, if refer to the Madhu's method in this case.

For all associated binary files then they are installed under Program Files (default installation) or any directory you might have chosen when installing as Custom.

|||Hi all,
Thanks for the reply.
Sorry all. I think i didnt post the question correctly.I want to know the files associated with the SQL server programmatically. I want to know whether microsoft provides any API for that or is there any other method to do the same ?
|||

Ah, yes. Have a look at SQL Server Management Objects (SMO), and I quote from BOL:

"SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server. SQL Server Replication Management Objects (RMO) is a collection of objects that encapsulates SQL Server replication management."

|||Hi
Thanks !!!. I will look into it.
|||

The above post is correct - and you can use this link at any time for the default locations:

http://msdn2.microsoft.com/en-us/library/ms143547.aspx

The nice thing about the link is that it is kept up to date as things change.

- Buck Woody