2

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?

Mike Flynn
  • 22,342
  • 54
  • 182
  • 341
  • Have you tried [sys.master_files](https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-master-files-transact-sql) ? `SELECT name, physical_name AS current_file_location FROM sys.master_files` – Fmanin Aug 09 '17 at 03:37
  • 1
    If you have any backup files (no matter how old), you can read this information from the bak file. Use SSMS to do Restore Database, load your backup, and without restoring, switch to the files tab. Logical File Name and Original File Name are shown for both data and log files. – Jonathan Willcock Aug 09 '17 at 06:34
  • But I just said that the file names I have now are random numbers. Knowing the original file name would not help me at all. I dont have a backup just MDF and LDF – Mike Flynn Aug 09 '17 at 12:12

1 Answers1

0

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.

Fmanin
  • 519
  • 1
  • 12
  • 25
  • 1
    From the OPs description, it sounds like this is an *unattached* scenario - they have the files, they need to know which ones to match up in order to attach the databases. – Damien_The_Unbeliever Aug 09 '17 at 06:46
  • I do not have the database, nor do I have the same name as the one from previously in the database for the files. Did you read the question? – Mike Flynn Aug 09 '17 at 12:10