SQL Server 2005 Database File Path (mdf ldf files)

CAMSYSTEMS
CAMSYSTEMS used Ask the Experts™
on
Is there a way from an SQL Server 2005 query to find out the path of the Database Files (mdf and ldf files).

I can get this information from right clicking on the database, going to properties and then to Files but I need to get this information using a query to use as part of a backup query.

Any ideas
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
sp_HelpDB 'dBName'  or
sp_HelpFile 'dbNAME'
Senior SAP CRM Consultant
Commented:
use mydb
go
select physical_name
from sys.database_files

Author

Commented:
Is there anyway of getting just the path (without the filename) or do I have to get a substring of the physical_name?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

AneeshDatabase Consultant
Top Expert 2009

Commented:
you need to use substring for this
Sham HaqueSenior SAP CRM Consultant

Commented:
use adventureworks
go
select substring(physical_name,1,charindex([name],physical_name)-1)
from sys.database_files
Sham HaqueSenior SAP CRM Consultant

Commented:
if you need it without the last backslash [\], change -1 to -2
:-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial