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 .....

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.