I need to find where SQL Server 2008 R2 stores what files back each database. The kicker is that the server has completely crashed (hardware failure). I've managed to recover all the .mdf/.ldf files.

I need to get a single database up and running on another server asap, but whoever set the server up originally didn't match the database names to the file names very well, so I need to somehow extract the mapping between the two without going through a full restore of SQL Server, i.e. I need to know the names of the files for database XXXXX as it was attached to SQL Server.

Is this possible? Was thinking about attached the recovered master database onto another SQL Server instance, but I cannot seem to find any information about data files in another master database, so it might be a waste of time.



1 Answer 1


SQL Server maintains this in sys.master_files, after you have restored the master database
When SQL Server starts up, you'll see a lot of suspect databases.

You can attach the MDFs and LDFs using the info in sys.master_files

  • cool. unfortunately, you cannot look at this table by attaching master onto another server (as another database)... managed to find what I was looking for by opening the master.mdf in notepad...
    – Gareth
    Commented Oct 25, 2011 at 12:31
  • @Gareth: I suspected this. sys.master_files isn't "real" IIRC so can't be backed up or restored
    – gbn
    Commented Oct 25, 2011 at 13:24
  • 1
    Ye, figured as much. Doing a hex trawl through the data file enabled me to find the info...
    – Gareth
    Commented Oct 25, 2011 at 13:31

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.