Retrieve file list of an SQL server database which is offline.

Question

Hi,

I have some offline databases on a SQL server. I would like to know which files on disc are related to these databases. Is it possible to retrieve the file list of offline databases without taking them online first?

cheers, Achim

Answer 1

This will give you a list of all physical file paths related to any offline databases, along with database name and file type:

SELECT
'DB_NAME' = db.name,
'FILE_NAME' = mf.name,
'FILE_TYPE' = mf.type_desc,
'FILE_PATH' = mf.physical_name
FROM
sys.databases db
INNER JOIN sys.master_files mf
ON db.database_id = mf.database_id
WHERE
db.state = 6 -- OFFLINE