Physical Database and Log File location

How do I get the Physical Database and Log File location (file system path) of a DB in SQL Server 2005?

I used this to get the mdf file: {SELECT [Name], FileName FROM sysdatabases} but 开发者_StackOverflow社区need to get the log file as well...

Cheers, Conor

---

SELECT * FROM sys.database_files

---

Here is another way

select * from sys.sysaltfiles

---

You should be using sp_helpdb or sp_helpfile for this.

e.g:

sp_helpdb 'master'

---

try this T-SQL Statement provides the logical name and the physical location of the data/log files of all the databases available in the current SQL Server instance:

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

If you are using SQL Server 2000, you could execute the following T-SQL Statement:

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

---

I used the following script to retrieve the data/log file names and paths:

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 for the script goes to 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