What does the FILE clause mean during a SQL Restore

28 January,2016 by Jack Vamvas

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on What does the FILE clause mean during a SQL Restore


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer