finding database Path

Question

Is there a way to find the physical path of SQL Server file (.mdf)? I have VS2010 where I browse the SQL Server database in Server Explorer window. I do not have sql Server Management studio.

Thanks in advance

why do you need that ?

Antonio Bakula
– Antonio Bakula

2013-07-11 14:39:34 +00:00
Commented Jul 11, 2013 at 14:39 — Antonio Bakula
– Antonio Bakula, Commented Jul 11, 2013 at 14:39

Avitus · Accepted Answer · 2013-07-11 14:27:21Z

2

This gives the database where name = 'master'. Just change this to the database you're looking for.

SELECT name, physical_name AS current_file_location
FROM sys.master_files
WHERE name = 'master'

You can also try:

SELECT filename FROM sys.sysfiles

This gives the default locations

 DECLARE @defaultDataLocation nvarchar(4000)
DECLARE @defaultLogLocation nvarchar(4000)

EXEC master.dbo.xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'DefaultData', 
    @defaultDataLocation OUTPUT

EXEC master.dbo.xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'DefaultLog', 
    @defaultLogLocation OUTPUT

SELECT @defaultDataLocation,    @defaultLogLocation

edited Jul 11, 2013 at 14:27

answered Jul 11, 2013 at 14:21

Avitus

16k6 gold badges47 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Avitus Over a year ago

if you are in the database try: SELECT * FROM sysfiles

Edward Over a year ago

The second part of the accepted answer (registry read) worked for me to give the DEFAULT location of user databases - this is exactly what I was looking for! Excellent, thank you!

Collectives™ on Stack Overflow

finding database Path

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related