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

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 Woodysql

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

Files associated with a DB

Hi all
Is there any way to find the files/filenames associated with the SQL server instance ?. Is there any way to find it programmatically ? Does Microsoft provide any API for that ?

Thanks in Adcance,
mani3727

Maybe you can use WMI.

See what you can list installed software

In another example you can List Specific Files Included in the Indexing Service

|||

Are you talking about binary files or database files?

If you are using SQL Server 2005 you can use the following code to get all databases related to the instance:

Code Snippet

SELECT * FROM sys.master_files

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Hi Wesley

Thanks for the reply.I want to know all the files associated with a specific SQL server instance.Could you tell me how it can be done programmaically ?

Thanks,
mani3727

Files associated with a DB

Hi all
Is there any way to find the files/filenames associated with the SQL server instance ?. Is there any way to find it programmatically ? Does Microsoft provide any API for that ?

Thanks in Adcance,
mani3727

Maybe you can use WMI.

See what you can list installed software

In another example you can List Specific Files Included in the Indexing Service

|||

Are you talking about binary files or database files?

If you are using SQL Server 2005 you can use the following code to get all databases related to the instance:

Code Snippet

SELECT * FROM sys.master_files

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Hi Wesley

Thanks for the reply.I want to know all the files associated with a specific SQL server instance.Could you tell me how it can be done programmaically ?

Thanks,
mani3727