Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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)