View backup header information for SQL Server backup sets

To view backup header information for all backup sets on a backup device run this code:

RESTORE HEADERONLY FROM DISK = 'C:\temp\aw.bak'
GO

If you have more backups in one backup file, you will see all the information for each backup.
This information includes: backup set name and description, expiration date for the backup set, backup type and size in bytes, user name that performed the backup, server and database name, database version, backup start and finish date and time, whether the backup set is compressed or not, LSN, compatibility level and recovery model setting of the backed up database, and many more details about the backup set...

The information about the position of the backup set can be used with the FILE = option when you want to List the data and log files contained in the SQL Server backup set

RESTORE HEADERONLY command in SQL Server

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