6

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?

6
  • Where is that located? I was only able to attach the temp database, but came up empty.
    – Rick
    May 1, 2020 at 19:25
  • Sorry, by main db I meant my application's database. That is 26 GB. How do you open to view it?
    – Rick
    May 1, 2020 at 19:44
  • Yes, that is correct.
    – Rick
    May 1, 2020 at 19:46
  • 1
    @BobKlimes - sounds like they only have tempdb and msdb files May 1, 2020 at 19:55
  • 1
    Do you have an old backup of that database? runing a RESTORE FILELISTONLY FROM DISK = 'YourBackupPath\YourBackupFile.bak'; would list the path of the database files when the backup was taken.
    – Ronaldo
    May 1, 2020 at 20:48

1 Answer 1

12

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.

8
  • Tried with different offsets, but couldn't find it...
    – Rick
    May 1, 2020 at 20:22
  • This is quite a crucial table to the database - it can't exist without it and needs to be there at initial creation so it is very likely to be early in the file. Maybe just start at offset 0 and load the first 1,000 pages (8,192,000 bytes and use the "find" function to look for the Unicode string .ldf) May 1, 2020 at 20:26
  • 1
    @Rick - Can you try offset 262144 in case sysfiles1 happens to be more reliably on page 32 than sysprufiles is with its location? May 1, 2020 at 20:53
  • 1
    Found it on that one. Thanks!
    – Rick
    May 1, 2020 at 21:19
  • 2
    Another option is the undocumented DBCC command, like DBCC CHECKPRIMARYFILE ( N'D:\SqlFiles\YourDatabase.mdf',1);
    – Dan Guzman
    May 2, 2020 at 0:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.