Determine MDF and LDF file match

Question

Is there a way to determine what MDF goes with what LDF file for SQL Server? We had a server crash and pull these files off and were only named with a random integer for the file name. So now we need to guess which MDF and LDF go together to get them up but what is the best way to do that?

Answer 1

You would find your current database's MDF and LDF with this:

sp_helpdb 'YourDBName'

Or you could see everything you have in your instance:

SELECT name, physical_name AS current_file_location FROM sys.master_files

In case of offline scenario try this:

SELECT DB.name, MF.name, MF.type_desc, MF.physical_name
FROM sys.databases DB
INNER JOIN sys.master_files MF ON db.database_id = mf.database_id
WHERE DB.state = 6 

When DB.State= 6 means offline state.