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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Suppose I have an SQL Server database called bubu and the respective backup file is bubu.bak.

If I want to restore it with the backing store in a custom location, I use the following T-SQL statement:

    MOVE N'bubu' TO N'C:\custom\bubu.mdf', MOVE N'bubu_log' TO N'C:\custom\bubu.LDF'

And so I have a new database bubu2, which happens to be the backup copy of bubu. The important thing here is that I assume that the backup contains the logical files bubu and bubu_log, because the backup file is named bubu.bak.

Now, suppose someone renames bubu.bak to mumu.bak. Doing so, breaks my logic, since now I will try this T-SQL statement:

    MOVE N'mumu' TO N'C:\custom\mumu.mdf', MOVE N'mumu_log' TO N'C:\custom\mumu.LDF'

And of course, I fail, because mumu.bak does not contain the logical files mumu and mumu_log - renaming the backup file does not rename the logical files contained in the backup (of course).

But then, how do I do what I need if the backup file was renamed?

share|improve this question
up vote 5 down vote accepted

You can use the filelistonly option to retrieve the files names in a backup:

restore filelistonly from disk='c:\mumu.bak'
share|improve this answer
This gives a rowset with many columns. Can I select the columns to fetch? – mark Jan 23 '12 at 7:46
You could use insert ... exec to store the results in a temporary table. Then you can select a few columns from that. Lot of code though. – Andomar Jan 23 '12 at 7:50
OK, another question - is it guaranteed that the first row corresponds to the mdf file? Or better yet, can I fetch just the MDF file name from the backup? – mark Jan 23 '12 at 9:12
Not sure, but if you use the insert ... exec solution, you can filter for the MDF filename. – Andomar Jan 23 '12 at 12:57

Your Answer


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.