Location of physical database and log file

Question

How to get the physical database and the location of the log file (path to the file system) of the database in SQL Server 2005?

I used this to get the mdf file: {SELECT [Name], FileName FROM sysdatabases}, but I also need to get the log file ...

Cheers, Conor

Answer 1

SELECT * FROM sys.database_files

Answer 2

Here is another way

select * from sys.sysaltfiles

Answer 3

sp_helpdb sp_helpfile.

:

sp_helpdb 'master'

Answer 4

T-SQL / , SQL Server

SELECT db_name (database_id) DatabaseName, name, type_desc, physical_name FROM sys.master_files

If you are using SQL Server 2000, you can run the following T-SQL statement

SELECT db_name (dbid) as DatabaseName, name, file name FROM master.dbo.sysaltfiles

Answer 5

I used the following script to get the names and paths of data / log files:

declare @DBName                 sysname
        , @LogicalDataFile      sysname
        , @LogicalLogFile       sysname
        , @PhysicalDataFile     nvarchar(260)
        , @PhysicalLogFile      nvarchar(260)

set @DBName = '<database-name>'

-- Data file
select  @LogicalDataFile = name
        , @PhysicalDataFile = physical_name
from    sys.master_files
where   database_id = db_id(@DBName)
        and type_desc = 'ROWS'

-- Log file
select  @LogicalLogFile = name
        , @PhysicalLogFile = physical_name
from    sys.master_files
where   database_id = db_id(@DBName)
        and type_desc = 'LOG'

select  @LogicalDataFile as [@LogicalDataFile]
        , @LogicalLogFile as [@LogicalLogFile]
        , @PhysicalDataFile as [@PhysicalDataFile]
        , @PhysicalLogFile as [@PhysicalLogFile]

Credit goes to the script for this blog entry: http://sqlblogcasts.com/blogs/davidwimbush/archive/2009/07/28/how-to-get-the-logical-and-physical-file-names-for- a-database.aspx