Menu
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?
963
select * from sys. databases shows a lot of information about each database - but unfortunately it doesn't show the files used by each database. select * from sys. database_files shows the mdf/ldf files of the master database - but not the other databases.
yes,you can restore over-top a DB that is in an offline,WITH REPLACE.
You have two native options for finding out where the SQL server stores its database files: either right-click on the instance name in SQL Server Management Studio (SSMS) and navigate to the 'Database Settings' tab, or use a T-SQL query.
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' If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
