TechNet
Export (0) Print
Expand All

backupfile (Transact-SQL)

 

Updated: February 18, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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 nameData typeDescription
backup_set_idintUnique identification number of the file containing the backup set. References backupset(backup_set_id).
first_family_numbertinyintFamily number of the first media containing this backup file. Can be NULL.
first_media_numbersmallintMedia number of the first media containing this backup file. Can be NULL.
filegroup_namenvarchar(128)Name of the filegroup containing a backed up database file. Can be NULL.
page_sizeintSize of the page, in bytes.
file_numbernumeric(10,0)File identification number unique within a database (corresponds to sys.database_files.file_id).
backed_up_page_countnumeric(10,0)Number of pages backed up. Can be NULL.
file_typechar(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_sizenumeric(10,0)Device that the original data or log file resided on when it was backed up. Can be NULL.
file_sizenumeric(20,0)Length of the file that is backed up, in bytes. Can be NULL.
logical_namenvarchar(128)Logical name of the file that is backed up. Can be NULL.
physical_drivenvarchar(260)Physical drive or partition name. Can be NULL.
physical_namenvarchar(260)Remainder of the physical (operating system) file name. Can be NULL.
statetinyintState 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_descnvarchar(64)Description of the file state, one of:

ONLINE RESTORING

RECOVERING

RECOVERY_PENDING

SUSPECT OFFLINE DEFUNCT
create_lsnnumeric(25,0)Log sequence number at which the file was created.
drop_lsnnumeric(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_guiduniqueidentifierUnique identifier of the file.
read_only_lsnnumeric(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_lsnnumeric(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_lsnnumeric(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_guiduniqueidentifierFor 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_sizenumeric(20,0)Size of the backup for this file in bytes.
filegroup_guiduniqueidentifierID of the filegroup. To locate filegroup information in the backupfilegroup table, use filegroup_guid with backup_set_id.
is_readonlybit1 = File is read-only.
is_presentbit1 = File is contained in the backup set.

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.

Backup and Restore Tables (Transact-SQL)
backupfilegroup (Transact-SQL)
backupmediafamily (Transact-SQL)
backupmediaset (Transact-SQL)
backupset (Transact-SQL)
System Tables (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft