Retrieve file list of an SQL server database which is offline

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?

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

Or simply

select * from sys.databases where state_desc='OFFLINE'

List all the available, but offline SQL server database files

The following statement will list all the files associated with offline SQL server databases

SELECT
    m.physical_name + '\' + m.name AS [file_path]
FROM
    sys.databases AS d 
    INNER JOIN sys.master_files AS m ON d.database_id = m.database_id
WHERE
    d.state_desc = 'OFFLINE'
    --AND m.type_desc = 'ROWS'
GROUP BY
    m.physical_name + '\' + m.name

Note: Uncomment the line AND m.type_desc = 'ROWS' (delete the --) to filter the list further to include only database files. Otherwise, log files will also be listed.

The GROUP BY clause is there to prevent entries appearing more than once.