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 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
Reference
backupfilegroup (Transact-SQL)
backupmediafamily (Transact-SQL)