Where are all database file paths stored in SQL?

Question

Where are the paths for all the database files stored in SQL? eg: paths for mdf,ldf files for TempDB, and other databases.

I need to find out the file that contains all the paths. I have by mistake changed the TempDB path and I am not able to login into SQL to change it. I need to restore the SQL to previous working state. So I need to know which file to replace inorder to get the correct paths back.

user7715598 · Accepted Answer · 2017-07-31 10:24:26Z

1

Try this

Use [your Database Name]
    SELECT * FROM sys.database_files

answered Jul 31, 2017 at 10:24

user7715598

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

Comments

Red Devil · Accepted Answer · 2017-07-31 10:24:34Z

0

You can use sp_helpfile Databasename

answered Jul 31, 2017 at 10:24

Red Devil

2,4135 gold badges30 silver badges46 bronze badges

Comments

Laxmi · Accepted Answer · 2017-07-31 10:25:54Z

0

i think you can use sys.master_files.

answered Jul 31, 2017 at 10:25

Laxmi

3,81028 silver badges30 bronze badges

Comments

Dani · Accepted Answer · 2017-07-31 10:26:33Z

0

you can access them through the sql management studio (the database properties) do you ask where does it gets the info from ? it's probably in one of the system tables.

answered Jul 31, 2017 at 10:26

Dani

15.1k12 gold badges69 silver badges111 bronze badges

Comments

AminRostami · Accepted Answer · 2024-09-26 10:01:26Z

0

use this query:

SELECT 

    mdf.database_id, 

    mdf.name, 

    mdf.physical_name as data_file, 

    ldf.physical_name as log_file, 

    db_size = CAST((mdf.size * 8.0)/1024 AS DECIMAL(8,2)), 

    log_size = CAST((ldf.size * 8.0 / 1024) AS DECIMAL(8,2))

FROM (SELECT * FROM sys.master_files WHERE type_desc = 'ROWS' ) mdf

JOIN (SELECT * FROM sys.master_files WHERE type_desc = 'LOG' ) ldf

ON mdf.database_id = ldf.database_id

answered Sep 26, 2024 at 10:01

AminRostami

2,7924 gold badges32 silver badges49 bronze badges

Collectives™ on Stack Overflow

Where are all database file paths stored in SQL?

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related