Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Thanks

share|improve this question

migrated from stackoverflow.com Oct 25 '11 at 13:05

This question came from our site for professional and enthusiast programmers.

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

share|improve this answer
    
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 Oct 25 '11 at 12:31
    
@Gareth: I suspected this. sys.master_files isn't "real" IIRC so can't be backed up or restored –  gbn Oct 25 '11 at 13:24
1  
Ye, figured as much. Doing a hex trawl through the data file enabled me to find the info... –  Gareth Oct 25 '11 at 13:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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