Export (0) Print
Expand All

backupfile (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

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.

Applies to: SQL Server (SQL Server 2008 through current version).

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

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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft