Edit

backupfile (Transact-SQL)

  • Article

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)