0

When using RESTORE FILELISTONLY to restore a database using code the logical_name for the files is coming back as 423.

However when I do a manual restore of the database the logical file name appears to be 3497.

I'm running SQL Server 2008 R2. Has anyone run into anything like this before? How do I repair this?

Improve this question
11
  • When you say "using code" can I assume you mean you are still using all of this jazz? And that you get different results when you use RESTORE FILELISTONLY without using code?
    – Aaron Bertrand
    Commented May 29, 2014 at 15:22
  • By "manual restore" do you mean actually restoring the database and bringing it online? How are you determining the logical file name after doing this restore? sys.database_files?
    – Bob Pusateri
    Commented May 29, 2014 at 15:24
  • @AaronBertrand - You caught me. Yes sir that is what I mean.
    – Lumpy
    Commented May 29, 2014 at 15:30
  • Which question are you answering? I asked two. Can you be explicit?
    – Aaron Bertrand
    Commented May 29, 2014 at 15:31
  • @BobPusateri - by manual I'm referring to using the SSMS GUI. Under Options the Original File Name is listed as 3497. I've found in the past that if I hardcode my procedure to use that same value that the procedure works fine.
    – Lumpy
    Commented May 29, 2014 at 15:31

1 Answer 1

Reset to default
2

So what it sounds like is happening is:

  1. You create a database called foo with a logical data file name of foo.
  2. You back up that database.
  3. You restore that database as a different database (say, bar).
  4. You backup the bar database.
  5. You try to restore the bar backup as yet another database name, and your code assumes that the logical file name for the data file for the database bar should be bar. But it's not, it's still foo!

You could also interject some random ALTER DATABASE x MODIFY FILE (name = 'y', newname = 'z'); in there to make it even more confusing for you (never mind your code). Particularly if - like it turns out is true in your case - you are storing multiple backups in a single backup file. This can be bad regardless of whether it is the same database but the logical file names change over time, or different databases being backed up to the same file. You need to determine which file number you're actually looking for, and specify that in FILELISTONLY's WITH FILE = option. Better yet, stop reusing the same file for multiple backups.

You should be sure that when you restore a new database based on the backup of another, you use WITH MOVE to give the logical file names meaningful physical paths, and then use MODIFY FILE immediately afterward to make the logical names themselves meaningful.

To help figure out where the actual discrepancy is coming for a specific case you are experiencing (I couldn't reproduce a case where RESTORE FILELISTONLY showed different values than the restore UI in SSMS), you could provide a .bak file on some file sharing service, and we could look ourselves. Your description of what the file names are and what they should be is extremely hard to follow.

Improve this answer
9
  • Yep Sorry about the file names. They are actually all 4 digit numbers (and we have 3000 of them) for reasons that predate me. were working on fixing that. If I were to use Modify file on these databases now to make the logical names match the physical names is that likely to fix the issue do you think?
    – Lumpy
    Commented May 29, 2014 at 16:22
  • If they don't currently match, and your code makes assumptions about the relationship between a database's name, its files' logical names, and its files' physical names, yes.
    – Aaron Bertrand
    Commented May 29, 2014 at 16:24
  • @AaronBertand - I just wanted to take a moment and say thanks for your persistence in helping me with this problem. I really appreciate it. - I can use that command to set them to 423 which would allow the procedure to go forward but when I change them back it doesn't change the value coming back from filelist. Where does filelist pull this information from? The logical file is correct in sys.master_files.
    – Lumpy
    Commented May 29, 2014 at 16:39
  • By that command I mean the ALTER DATABASE x MODIFY FILE (name = 'y', newname = 'z'); command.
    – Lumpy
    Commented May 29, 2014 at 16:40
  • 1
    @Lumpy this is one reason I always use WITH INIT and unique file names - much easier to clean up old backups, and much less chance for issues caused by changes to the same database over time, or mixing different databases in the same file. I've never understood the point of storing multiple backups in a single backup file (I know there are arguments for them, I just don't agree that they're good ones).
    – Aaron Bertrand
    Commented May 29, 2014 at 19:29

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.