SQLServer 2016

 

You know you can store several database backups into one .bak file.  You can even store backups from different databases into the same .bak file, known as a media set.

So when looking from Windows at a .bak file, you might wonder what it actually contains.  In come RESTORE HEADERONLY and RESTORE FILELISTONLY

 

RESTORE HEADERONLY will list the names of the backupsets (whenever you backup, it creates a new backup set, the names of which appear in the "BackupName" column):

By the way, you can run that againt tempdb, it's safer.

Now if you want to see the files in each of those backupsets, you must use RESTORE FILELISTONLY  with the number in the Position column above. Let's look at the files for backupset 2:

 

 

Watchout, if you use RESTORE FILELISTONLY  without mentioning "WITH FILE":

You get the first backupset of that mediaset only!