What does the FILE clause mean during a SQL Restore

Question: During the execution of  a RESTORE statement from a  SQL Server backup file , I noticed the WITH FILE = 1 as part of the RESTORE DATABASE statement.

Why is the WITH FILE  used? And what is it’s purpose

RESTORE DATABASE [ContentEmail] FROM 

DISK = N'I:\MyContent.bak' WITH  FILE = 1, 

NOUNLOAD,  REPLACE,  STATS = 5

Answer:   A SQL Server backup file – also known as a logical backup  device-  can contain more than one backup set. The FILE clause identifies a specific backup within the backup file.

So if you wanted the restore the backup file ID = 2 , you’d  use  FILE = 2.  This would mean you'd restore the backup set identified as position 2. It could be that there are associated LOG BACKUPS which may be ID=3 , ID = 4 .....

To view the backup file and the backups within  , use the sql command  RESTORE HEADER ONLY.

A quick demonstration will point  out the usage of the FILE CLAUSE. Customise the file paths based on your server drive paths. Assuming you execute the code with success, the RESTORE HEADERONLY command will return 2 backups within the backup device. You’ll notice Position 1 and Position 2. That's the value you use with the FILE = clause.

--create the logical backup device on disk 
USE BackupDB
GO
EXEC sp_addumpdevice 'disk', 'mydiskdump', 'E:\dump1.bak' ;
GO

--create the first backup to the logical backup device
USE BackupDB
GO
BACKUP DATABASE BackupDB TO mydiskdump 
GO
BACKUP DATABASE TO BackupDB TO mydiskdump 

--restore header only 
RESTORE HEADERONLY FROM DISK = N'E:\dump1.bak' 
WITH NOUNLOAD;
GO

For full details on the RESTORE with FILE option - check the MSDN article - https://msdn.microsoft.com/en-us/library/ms186858.aspx#restoring_using_FILE_n_FG

Read More on SQL Server Backup and Restores

SQL Server - RESTORE VERIFYONLY

SQL Server faster restores with instant file initialisation

SQL Server - Predict SQL BACKUP DATABASE finish time with sys.dm_exec_requests