Transact-SQL Reference

backupset

Contains a row for each backup set. This table is stored in the msdb database.

Column name Data type Description
backup_set_id int NOT NULL IDENTITY PRIMARY KEY Unique backup set identification number that identifies the backup set.
backup_set_uuid uniqueidentifier
NOT NULL
Unique backup set identification number that identifies the backup set on the media.
media_set_id int NOT NULL REFERENCES backupmediaset
(media_set_id)
Unique media set identification number that identifies the media set containing the backup set.
first_family_number tinyint NULL Family number of the media where the backup set starts.
first_media_number smallint NULL Media number of the media where the backup set starts.
last_family_number tinyint NULL Family number of the media where the backup set ends.
last_media_number smallint NULL Media number of the media where the backup set ends.
catalog_family_
number
tinyint NULL Family number of the media containing the start of the backup set directory.
catalog_media_number smallint NULL Media number of the media containing the start of the backup set directory.
position int NULL Backup set position used in the restore operation to locate the appropriate backup set and files. For more information, see FILE in BACKUP.
expiration_date datetime NULL Date and time the backup set expires.
software_vendor_id int NULL Identification number of the software vendor writing the backup media header.
name nvarchar(128) NULL Name of the backup set.
description nvarchar(255) NULL Description of the backup set.
user_name nvarchar(128) NULL Name of the user performing the backup operation.
software_major_version tinyint NULL Microsoft® SQL Server™ major version number.
software_minor_
version
tinyint NULL SQL Server minor version number.
software_build_version smallint NULL SQL Server build number.
time_zone smallint NULL Difference between local time (where the backup operation is taking place) and Universal Coordinated Time (UCT) 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 5 hours after UCT.
mtf_minor_version tinyint NULL Microsoft Tape Format minor version number.
first_lsn numeric(25,0) NULL Log sequence number of the first or oldest log record in the backup set.
last_lsn numeric(25,0) NULL Log sequence number of the last or newest log record in the backup set.
checkpoint_lsn numeric(25,0) NULL Log sequence number of the log record where recovery must start.
database_backup_lsn numeric(25,0) NULL Log sequence number of the most recent full database backup.
database_creation_date datetime NULL Date and time the database was originally created.
backup_start_date datetime NULL Date and time the backup operation started.
backup_finish_date datetime NULL Date and time the backup operation finished.
type char(1) NULL Backup type. Can be:

D = Database.
I = Database Differential.
L = Log.
F = File or Filegroup.

sort_order smallint NULL Sort order of the server performing the backup operation. For more information about sort orders and collations, see Collations.
code_page smallint NULL Code page of the server performing the backup operation. For more information about code pages, see Collations.
compatibility_level tinyint NULL Compatibility level setting for the database. Can be:

60 = SQL Server version 6.0.
65 = SQL Server 6.5.
70 = SQL Server 7.0.

For more information about compatibility levels, see sp_dbcmptlevel.

database_version int NULL Database version number.
backup_size numeric(20,0) NULL Size of the backup set, in bytes.
database_name nvarchar(128) NULL Name of the database involved in the backup operation.
server_name nvarchar(128) NULL Name of the server running the SQL Server backup operation.
machine_name nvarchar(128) NULL Name of the computer running SQL Server.
flags int NULL Flag bits:

1 = Backup contains minimally logged data.
2 = WITH SNAPSHOT was used.
4 = Database was read-only at time of backup.
8 = Database was in single-user mode at time of backup.

unicode_locale int NULL Unicode locale.
unicode_compare_style int NULL Unicode compare style.
collation_name nvarchar(128) NULL Collation name.