List the data and log files contained in the SQL Server backup set

To list the database and log files (.mdf and .ldf files) contained in the SQL Server backup set, and many other backup file properties (like physical and logical file names, the name of the filegroup that contains the file, file sizes in bytes, the type of file...), run this code:

RESTORE FILELISTONLY FROM DISK = 'C:\temp\aw.bak' WITH FILE = 1
GO

RESTORE FILELISTONLY FROM DISK command in SQL Server

In SQL Server 2008 and later, you need to have CREATE DATABASE permission to get this information.

If there are multiple backup sets on the backup device, specify the backup set number using the "WITH FILE = n" option.
Position of the backup set in the volume can be obtained using RESTORE HEADERONLY command that is explained more in a related article:
View backup header information for SQL Server backup sets

To specify the second backup set on the device run this code:

RESTORE FILELISTONLY FROM DISK = 'C:\temp\aw.bak' WITH FILE = 2
GO