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
.gif "ms190274.note(en-US,SQL.90).gif")
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.
See Also
Reference
backupfilegroup (Transact-SQL)
backupmediafamily (Transact-SQL)
backupmediaset (Transact-SQL)
backupset (Transact-SQL)
System Tables (Transact-SQL)