backupfile (Transact-SQL)

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

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