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).

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.

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.

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).

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.

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).

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.