backupfile (Transact-SQL)
Applies to: 
SQL Server
Contains one row for each data or log file of a database. The columns describes the file configuration at the time the backup was taken. Whether or not the file is included in the backup is determined by the is_present column. This table is stored in the msdb database.
| Column name | Data type | Description |
|---|---|---|
| backup_set_id | int | Unique identification number of the file containing the backup set. References backupset(backup_set_id). |
| first_family_number | tinyint | Family number of the first media containing this backup file. Can be NULL. |
| first_media_number | smallint | Media number of the first media containing this backup file. Can be NULL. |
| filegroup_name | nvarchar(128) | Name of the filegroup containing a backed up database file. Can be NULL. |
| page_size | int | Size of the page, in bytes. |
| file_number | numeric(10,0) | File identification number unique within a database (corresponds to sys.database_files.file_id). |
| backed_up_page_count | numeric(10,0) | Number of pages backed up. Can be NULL. |
| file_type | char(1) | File backed up, one of: D = SQL Server data file. L = SQL Server log file. F = Full text catalog. Can be NULL. |
| source_file_block_size | numeric(10,0) | Device that the original data or log file resided on when it was backed up. Can be NULL. |
| file_size | numeric(20,0) | Length of the file that is backed up, in bytes. Can be NULL. |
| logical_name | nvarchar(128) | Logical name of the file that is backed up. Can be NULL. |
| physical_drive | nvarchar(260) | Physical drive or partition name. Can be NULL. |
| physical_name | nvarchar(260) | Remainder of the physical (operating system) file name. Can be NULL. |
| state | tinyint | State of the file, one of: 0 = ONLINE 1 = RESTORING 2 = RECOVERING 3 = RECOVERY PENDING 4 = SUSPECT 6 = OFFLINE 7 = DEFUNCT 8 = DROPPED Note: The value 5 is skipped so that these values correspond to the values for database states. |
| state_desc | nvarchar(64) | Description of the file state, one of: ONLINE RESTORING RECOVERING RECOVERY_PENDING SUSPECT OFFLINE DEFUNCT |
| create_lsn | numeric(25,0) | Log sequence number at which the file was created. |
| drop_lsn | numeric(25,0) | Log sequence number at which the file was dropped. Can be NULL. If the file has not been dropped, this value is NULL. |
| file_guid | uniqueidentifier | Unique identifier of the file. |
| read_only_lsn | numeric(25,0) | Log sequence number at which the filegroup containing the file changed from read-write to read-only (the most recent change). Can be NULL. |
| read_write_lsn | numeric(25,0) | Log sequence number at which the filegroup containing the file changed from read-only to read-write (the most recent change). Can be NULL. |
| differential_base_lsn | numeric(25,0) | Base LSN for differential backups. A differential backup includes only data extents having a log sequence number equal to or greater than differential_base_lsn. For other backup types, the value is NULL. |
| differential_base_guid | uniqueidentifier | For a differential backup, the unique identifier of the most recent data backup that forms the differential base of the file; if the value is NULL, the file was included in the differential backup, but was added after the base was created. For other backup types, the value is NULL. |
| backup_size | numeric(20,0) | Size of the backup for this file in bytes. |
| filegroup_guid | uniqueidentifier | ID of the filegroup. To locate filegroup information in the backupfilegroup table, use filegroup_guid with backup_set_id. |
| is_readonly | bit | 1 = File is read-only. |
| is_present | bit | 1 = File is contained in the backup set. |
Remarks
RESTORE VERIFYONLY FROM backup_device WITH LOADHISTORY populates the columns of the backupmediaset table with the appropriate values from the media-set header.
To reduce the number of rows in this table and in other backup and history tables, execute the sp_delete_backuphistory stored procedure.
See Also
Backup and Restore Tables (Transact-SQL)
backupfilegroup (Transact-SQL)
backupmediafamily (Transact-SQL)
backupmediaset (Transact-SQL)
backupset (Transact-SQL)
System Tables (Transact-SQL)