Sql-server – SQL Server: Get location of transaction log from MDF file

restoresql serversql-server-2012

Our SQL Server 2012 Express server crashed and lost some data. We managed to rescue the main MDF, but can't find the LDF. Is there a way to get this information from the database MDF file or msdb, or temp mdfs that were found?

Best Answer

This is stored in a couple or locations in the MDF.

  1. the base table sysprufiles - I've checked a few databases installed locally and found it on page 155 and page 153 of the ones I've checked. I imagine this will always be fairly early in the file but it doesn't appear to have a fixed location.
  2. sysfiles1 - which is reliably on page 32 for every database I have checked so far.

The initial version of this answer used a hex editor to read it from the MDF.

Dan Guzman points out there is an undocumented DBCC command that is massively simpler.

DBCC CHECKPRIMARYFILE ( N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\AdventureWorks2012_Data.mdf',1);

Returns a result like 

+--------+---------+-------+---------+--------+---------+------+-------------------------+----------------------------------------------------------------------------------------------+
| fileid | groupid | size  | maxsize | growth | status  | perf |          name           |                                           filename                                           |
+--------+---------+-------+---------+--------+---------+------+-------------------------+----------------------------------------------------------------------------------------------+
|      1 |       1 | 26240 |      -1 |   2048 |       2 |    0 | AdventureWorks2012_Data | C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\AdventureWorks2012_Data.mdf |
|      2 |       0 |   128 |      -1 |     10 | 1048642 |    0 | AdventureWorks2012_Log  | C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\AdventureWorks2012_log.ldf  |
+--------+---------+-------+---------+--------+---------+------+-------------------------+----------------------------------------------------------------------------------------------+

I was curious as to how that approach worked so looked at the activity in process monitor.

enter image description here

This ends up reading the file header (page 0), database boot page (page 9) and page 32 of the file.

I didn't see page 32 mentioned on either of the preceding pages so I think that page is hardcoded to contain the information rather than being looked up from them.

Related Question