In this article

backupset (Transact-SQL)

05/16/2013

6 minutes to read

In this article

Contains a row for each backup set. A backup set contains the backup from a single, successful backup operation. RESTORE, RESTORE FILELISTONLY, RESTORE HEADERONLY, and RESTORE VERIFYONLY statements operate on a single backup set within the media set on the specified backup device or devices.

This table is stored in the msdb database.

Column name

Data type

Description

backup_set_id

int

Unique backup set identification number that identifies the backup set. Identity, primary key.

backup_set_uuid

uniqueidentifier

Unique backup set identification number that identifies the backup set.

media_set_id

int

Unique media set identification number that identifies the media set containing the backup set. References backupmediaset(media_set_id).

first_family_number

tinyint

Family number of the media where the backup set starts. Can be NULL.

first_media_number

smallint

Media number of the media where the backup set starts. Can be NULL.

last_family_number

tinyint

Family number of the media where the backup set ends. Can be NULL.

last_media_number

smallint

Media number of the media where the backup set ends. Can be NULL.

catalog_family_number

tinyint

Family number of the media containing the start of the backup set directory. Can be NULL.

catalog_media_number

smallint

Media number of the media containing the start of the backup set directory. Can be NULL.

position

int

Backup set position used in the restore operation to locate the appropriate backup set and files. Can be NULL. For more information, see FILE in BACKUP (Transact-SQL).

expiration_date

datetime

Date and time the backup set expires. Can be NULL.

software_vendor_id

int

Identification number of the software vendor writing the backup media header. Can be NULL.

name

nvarchar(128)

Name of the backup set. Can be NULL.

description

nvarchar(255)

Description of the backup set. Can be NULL.

user_name

nvarchar(128)

Name of the user performing the backup operation. Can be NULL.

software_major_version

tinyint

Microsoft SQL Server major version number. Can be NULL.

software_minor_version

tinyint

SQL Server minor version number. Can be NULL.

software_build_version

smallint

SQL Server build number. Can be NULL.

time_zone

smallint

Difference between local time (where the backup operation is taking place) and Coordinated Universal Time (UTC) in 15-minute intervals. Values can be -48 through +48, inclusive. A value of 127 indicates unknown. For example, -20 is Eastern Standard Time (EST) or five hours after UTC. Can be NULL.

mtf_minor_version

tinyint

Microsoft Tape Format minor version number. Can be NULL.

first_lsn

numeric(25,0)

Log sequence number of the first or oldest log record in the backup set. Can be NULL.

last_lsn

numeric(25,0)

Log sequence number of the next log record after the backup set. Can be NULL.

checkpoint_lsn

numeric(25,0)

Log sequence number of the log record where redo must start. Can be NULL.

database_backup_lsn

numeric(25,0)

Log sequence number of the most recent full database backup. Can be NULL.

database_backup_lsn is the “begin of checkpoint” that is triggered when the backup starts. This LSN will coincide with first_lsn if the backup is taken when the database is idle and no replication is configured.

database_creation_date

datetime

Date and time the database was originally created. Can be NULL.

backup_start_date

datetime

Date and time the backup operation started. Can be NULL.

backup_finish_date

datetime

Date and time the backup operation finished. Can be NULL.

type

char(1)

Backup type. Can be:

D = Database

I = Differential database

L = Log

F = File or filegroup

G =Differential file

P = Partial

Q = Differential partial

Can be NULL.

sort_order

smallint

Sort order of the server performing the backup operation. Can be NULL. For more information about sort orders and collations, see Collation and Unicode Support.

Name of the server running the SQL Server backup operation. Can be NULL.

machine_name

nvarchar(128)

Name of the computer running SQL Server. Can be NULL.

flags

int

In SQL Server, the flags column has been deprecated and is being replaced with the following bit columns:

has_bulk_logged_data

is_snapshot

is_readonly

is_single_user

has_backup_checksums

is_damaged

begins_log_chain

has_incomplete_metadata

is_force_offline

is_copy_only

Can be NULL.

In backup sets from earlier versions of SQL Server, flag bits:

1 = Backup contains minimally logged data.

2 = WITH SNAPSHOT was used.

4 = Database was read-only at the time of backup.

8 = Database was in single-user mode at the time of backup.

unicode_locale

int

Unicode locale. Can be NULL.

unicode_compare_style

int

Unicode compare style. Can be NULL.

collation_name

nvarchar(128)

Collation name. Can be NULL.

Is_password_protected

bit

Is the backup set

password protected:

0 = Not protected

1 = Protected

recovery_model

nvarchar(60)

Recovery model for the database:

FULL

BULK-LOGGED

SIMPLE

has_bulk_logged_data

bit

1 = Backup contains bulk-logged data.

is_snapshot

bit

1 = Backup was taken using the SNAPSHOT option.

is_readonly

bit

1 = Database was read-only at the time of backup.

is_single_user

bit

1 = Database was single-user at the time of backup.

has_backup_checksums

bit

1 = Backup contains backup checksums.

is_damaged

bit

1 = Damage to the database was detected when this backup was created. The backup operation was requested to continue despite errors.

begins_log_chain

bit

1 = This is the first in a continuous chain of log backups. A log chain begins with the first log backup taken after the database is created or when it is switched from the simple to the full or bulk-logged recovery model.