Matching physical names with databases in SQL Server

Sometimes you find yourself looking for which database use a specific data file on your filesystem. This may happen when restoring/renaming a SQL database on your server and not following your naming convention.

This usually ends up with different physical names that don’t match the database names, and on a larger SQL Server that soon becomes a problem.

This script helps you find which physical files a database have.

SELECT db.name as database_name,files.physical_name,
    files.name as logical_name
FROM master.sys.master_files files
inner join master.sys.databases db on files.database_id = db.database_id

-- and you can narrow it down to just showing the
-- file you are looking for:
where files.physical_name like '%<part of the filename>%'
This entry was posted in DBA, SQL Server, Tips. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *