CODEWITHSUNDEEP
sql-serversql-server-2008ssms
Deepanshu Kalra
Deepanshu Kalra

Reputation: 479

Where are all database file paths stored in SQL?

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.

Upvotes: 1

Views: 3612

Answers (5)

AminRostami
AminRostami

Reputation: 2772

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

Upvotes: 0

Dani
Dani

Reputation: 15081

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.

enter image description here

Upvotes: 0

Laxmi
Laxmi

Reputation: 3810

i think you can use sys.master_files.

Upvotes: 0

Red Devil
Red Devil

Reputation: 2403

You can use sp_helpfile Databasename

Upvotes: 0

user7715598
user7715598

Reputation:

Try this 

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

Upvotes: 1

Related Questions