sql-server - sql server - Determine MDF and LDF file match - answerstu - answerstu.com answerstu

sql server - Determine MDF and LDF file match

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?

1 Answer

  1. Glen- Reply

    2019-11-13

    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.

Leave a Reply

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

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>