SQL-Server: Why do we use FILE = 1 in scripts for restoring a database

Recently when I came across the script for restoring a database, I got a doubt on why we have to use "FILE = 1"? Can't we restore a database without that statement!? Basically, what it is used for?

You are allowed to save more than one backup in a backup file (i.e. device). The FILE clause lets you access a particular backup operation when there are multiple to choose from within the .bak file.

For more information on the various options of the RESTORE command, please see the following Microsoft documentation for RESTORE Arguments.

If you look under the Backup Set Options section (within the Syntax description), you will find:

FILE ={ backup_set_file_number | @backup_set_file_number }

Identifies the backup set to be restored. For example, a backup_set_file_number of 1 indicates the first backup set on the backup medium and a backup_set_file_number of 2 indicates the second backup set. You can obtain the backup_set_file_number of a backup set by using the RESTORE HEADERONLY statement.

When not specified, the default is 1, except for RESTORE HEADERONLY in which case all backup sets in the media set are processed. For more information, see "Specifying a Backup Set," later in this topic.

Important

This FILE option is unrelated to the FILE option for specifying a database file, FILE = { logical_file_name_in_backup | @logical_file_name_in_backup_var }.

Yes, you should be able to restore without the FILE = 1 as 1 is the default anyway. And, if you only ever have a single backup-set in a backup file, then it shouldn't pose a problem.

An example to help illustrate when you would use the FILE option is the following, which is Example B from the RESTORE command documentation page (linked above). It shows doing two restores from a single backup file: the first RESTORE is the FULL backup, and the second RESTORE is the DIFFerential backup.
RESTORE DATABASE AdventureWorks2012
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak'
WITH FILE = 6
NORECOVERY;
RESTORE DATABASE AdventureWorks2012
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak'
WITH FILE = 9
RECOVERY;


StackExchange Database Administrators Q#118399, answer score: 33