SQL Server FILELISTONLY command providing incorrect logical database name

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply