backupset (Transact-SQL)backupset (Transact-SQL)

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

每个备份集在表中占一行。Contains a row for each backup set. 备份集 包含来自单个成功备份操作的备份。A backup set contains the backup from a single, successful backup operation. RESTORE、RESTORE FILELISTONLY、RESTORE HEADERONLY 和 RESTORE VERIFYONLY 语句对指定的一个或多个备份设备上的介质集中的单个备份集进行操作。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.

此表存储中msdb数据库。This table is stored in the msdb database.

列名Column name 数据类型Data type 描述Description
backup_set_idbackup_set_id intint 标识备份集的唯一备份集标识号。Unique backup set identification number that identifies the backup set. 标识,主键。Identity, primary key.
backup_set_uuidbackup_set_uuid uniqueidentifieruniqueidentifier 标识备份集的唯一备份集标识号。Unique backup set identification number that identifies the backup set.
media_set_idmedia_set_id intint 标识备份集所在介质集的唯一介质集标识号。Unique media set identification number that identifies the media set containing the backup set. 引用backupmediaset (media_set_id)References backupmediaset(media_set_id).
first_family_numberfirst_family_number tinyinttinyint 备份集中第一个介质簇的编号。Family number of the media where the backup set starts. 可以为 NULL。Can be NULL.
first_media_numberfirst_media_number smallintsmallint 备份集从此处开始的介质的编号。Media number of the media where the backup set starts. 可以为 NULL。Can be NULL.
last_family_numberlast_family_number tinyinttinyint 备份从此处结束的介质的编号。Family number of the media where the backup set ends. 可以为 NULL。Can be NULL.
last_media_numberlast_media_number smallintsmallint 备份集从此处结束的介质的编号。Media number of the media where the backup set ends. 可以为 NULL。Can be NULL.
catalog_family_numbercatalog_family_number tinyinttinyint 包含备份集目录开始部分的介质簇的编号。Family number of the media containing the start of the backup set directory. 可以为 NULL。Can be NULL.
catalog_media_numbercatalog_media_number smallintsmallint 包含备份集目录开始部分介质的介质编号。Media number of the media containing the start of the backup set directory. 可以为 NULL。Can be NULL.
positionposition intint 还原操作中用来定位相应的备份集和文件的备份集位置。Backup set position used in the restore operation to locate the appropriate backup set and files. 可以为 NULL。Can be NULL. 有关详细信息,请参阅中的文件备份(TRANSACT-SQL)For more information, see FILE in BACKUP (Transact-SQL).
expiration_dateexpiration_date datetimedatetime 备份集过期的日期和时间。Date and time the backup set expires. 可以为 NULL。Can be NULL.
software_vendor_idsoftware_vendor_id intint 写入备份介质标头的软件供应商标识号。Identification number of the software vendor writing the backup media header. 可以为 NULL。Can be NULL.
namename nvarchar(128)nvarchar(128) 备份集的名称。Name of the backup set. 可以为 NULL。Can be NULL.
descriptiondescription nvarchar(255)nvarchar(255) 备份集的说明。Description of the backup set. 可以为 NULL。Can be NULL.
user_nameuser_name nvarchar(128)nvarchar(128) 执行备份操作的用户的名称。Name of the user performing the backup operation. 可以为 NULL。Can be NULL.
software_major_versionsoftware_major_version tinyinttinyint MicrosoftMicrosoft SQL ServerSQL Server 主版本号。SQL ServerSQL Server major version number. 可以为 NULL。Can be NULL.
software_minor_versionsoftware_minor_version tinyinttinyint SQL ServerSQL Server 次版本号。minor version number. 可以为 NULL。Can be NULL.
software_build_versionsoftware_build_version smallintsmallint SQL ServerSQL Server 内部版本号。build number. 可以为 NULL。Can be NULL.
time_zonetime_zone smallintsmallint 本地时间(备份操作发生地的时间)和协调世界时 (UTC) 之间的差异(以 15 分钟为单位)。Difference between local time (where the backup operation is taking place) and Coordinated Universal Time (UTC) in 15-minute intervals. 值可介于(含) -48 到 +48 之间。Values can be -48 through +48, inclusive. 值 127 表示未知。A value of 127 indicates unknown. 例如,-20 为美国东部标准时间 (EST),即比 UTC 晚 5 小时。For example, -20 is Eastern Standard Time (EST) or five hours after UTC. 可以为 NULL。Can be NULL.
mtf_minor_versionmtf_minor_version tinyinttinyint MicrosoftMicrosoft 磁带格式的次版本号。Tape Format minor version number. 可以为 NULL。Can be NULL.
first_lsnfirst_lsn numeric(25,0)numeric(25,0) 备份集中第一条或最早的日志记录的日志序列号。Log sequence number of the first or oldest log record in the backup set. 可以为 NULL。Can be NULL.
last_lsnlast_lsn numeric(25,0)numeric(25,0) 备份集之后的下一条日志记录的日志序列号。Log sequence number of the next log record after the backup set. 可以为 NULL。Can be NULL.
checkpoint_lsncheckpoint_lsn numeric(25,0)numeric(25,0) 日志记录中重做必须开始的日志序列号。Log sequence number of the log record where redo must start. 可以为 NULL。Can be NULL.
database_backup_lsndatabase_backup_lsn numeric(25,0)numeric(25,0) 最近的数据库完整备份的日志序列号。Log sequence number of the most recent full database backup. 可以为 NULL。Can be NULL.

database_backup_lsn是"检查点"的备份开始时触发。database_backup_lsn is the "begin of checkpoint" that is triggered when the backup starts. 此 LSN 将与一致first_lsn如果进行备份时数据库空闲且未配置复制。This LSN will coincide with first_lsn if the backup is taken when the database is idle and no replication is configured.
database_creation_datedatabase_creation_date datetimedatetime 数据库最初创建的日期和时间。Date and time the database was originally created. 可以为 NULL。Can be NULL.
backup_start_datebackup_start_date datetimedatetime 备份操作的开始日期和时间。Date and time the backup operation started. 可以为 NULL。Can be NULL.
backup_finish_datebackup_finish_date datetimedatetime 备份操作的结束日期和时间。Date and time the backup operation finished. 可以为 NULL。Can be NULL.
typetype char(1)char(1) 备份类型。Backup type. 可以是:Can be:

D = 数据库D = Database

I = 差异数据库I = Differential database

L = 日志L = Log

F = 文件或文件组F = File or filegroup

G = 差异文件G =Differential file

P = 部分P = Partial

Q = 差异部分Q = Differential partial

可以为 NULL。Can be NULL.
sort_ordersort_order smallintsmallint 执行备份操作的服务器的排序顺序。Sort order of the server performing the backup operation. 可以为 NULL。Can be NULL. 有关排序顺序和排序规则的详细信息,请参阅排序规则和 Unicode 支持For more information about sort orders and collations, see Collation and Unicode Support.
code_pagecode_page smallintsmallint 执行备份操作的服务器的代码页。Code page of the server performing the backup operation. 可以为 NULL。Can be NULL. 有关代码页的详细信息,请参阅排序规则和 Unicode 支持For more information about code pages, see Collation and Unicode Support.
compatibility_levelcompatibility_level tinyinttinyint 数据库的兼容级别设置。Compatibility level setting for the database. 可以是:Can be:

90 = SQL Server 2005 (9.x)SQL Server 2005 (9.x)90 = SQL Server 2005 (9.x)SQL Server 2005 (9.x)

100 = SQL Server 2008SQL Server 2008100 = SQL Server 2008SQL Server 2008

110 = SQL Server 2012 (11.x)SQL Server 2012 (11.x)110 = SQL Server 2012 (11.x)SQL Server 2012 (11.x)

120 = SQL Server 2014 (12.x)SQL Server 2014 (12.x)120 = SQL Server 2014 (12.x)SQL Server 2014 (12.x)

可以为 NULL。Can be NULL.

有关兼容性级别的详细信息,请参阅 ALTER DATABASE 兼容级别 (Transact-SQL)For more information about compatibility levels, see ALTER DATABASE Compatibility Level (Transact-SQL).
database_versiondatabase_version intint 数据库的版本号。Database version number. 可以为 NULL。Can be NULL.
backup_sizebackup_size numeric(20,0)numeric(20,0) 备份集的大小(以字节为单位)。Size of the backup set, in bytes. 可以为 NULL。Can be NULL. 对于 VSS 备份 backup_size 是一个估计的值。For VSS backups, backup_size is an estimated value.
database_namedatabase_name nvarchar(128)nvarchar(128) 备份操作中涉及的数据库的名称。Name of the database involved in the backup operation. 可以为 NULL。Can be NULL.
server_nameserver_name nvarchar(128)nvarchar(128) 运行 SQL ServerSQL Server 备份操作的服务器的名称。Name of the server running the SQL ServerSQL Server backup operation. 可以为 NULL。Can be NULL.
machine_namemachine_name nvarchar(128)nvarchar(128) 运行 SQL ServerSQL Server的计算机的名称。Name of the computer running SQL ServerSQL Server. 可以为 NULL。Can be NULL.
flagflags intint 在中SQL ServerSQL Server,则标志列已被弃用,替换以下 bit 列:In SQL ServerSQL Server, the flags column has been deprecated and is being replaced with the following bit columns:

has_bulk_logged_datahas_bulk_logged_data
is_snapshotis_snapshot
is_readonlyis_readonly
is_single_useris_single_user
has_backup_checksumshas_backup_checksums
is_damagedis_damaged
begins_log_chainbegins_log_chain
has_incomplete_metadatahas_incomplete_metadata
is_force_offlineis_force_offline
is_copy_onlyis_copy_only

可以为 NULL。Can be NULL.

SQL ServerSQL Server 早期版本的备份集中,标志位如下:In backup sets from earlier versions of SQL ServerSQL Server, flag bits:
1 = 备份包含最少的记录数据。1 = Backup contains minimally logged data.
2 = 使用了 WITH SNAPSHOT。2 = WITH SNAPSHOT was used.
4 = 备份时数据库为只读。4 = Database was read-only at the time of backup.
8 = 备份时数据库处于单用户模式。8 = Database was in single-user mode at the time of backup.
unicode_localeunicode_locale intint Unicode 区域设置。Unicode locale. 可以为 NULL。Can be NULL.
unicode_compare_styleunicode_compare_style intint Unicode 比较风格。Unicode compare style. 可以为 NULL。Can be NULL.
collation_namecollation_name nvarchar(128)nvarchar(128) 排序规则名。Collation name. 可以为 NULL。Can be NULL.
Is_password_protectedIs_password_protected bitbit 备份集是否Is the backup set

受密码保护:password protected:

0 = 未受到保护0 = Not protected

1 = 受到保护1 = Protected
recovery_modelrecovery_model nvarchar(60)nvarchar(60) 数据库的恢复模式:Recovery model for the database:

FULLFULL

BULK-LOGGEDBULK-LOGGED

SIMPLESIMPLE
has_bulk_logged_datahas_bulk_logged_data bitbit 1 = 备份包含大容量日志数据。1 = Backup contains bulk-logged data.
is_snapshotis_snapshot bitbit 1 = 备份时使用了 SNAPSHOT 选项。1 = Backup was taken using the SNAPSHOT option.
is_readonlyis_readonly bitbit 1 = 备份时数据库为只读。1 = Database was read-only at the time of backup.
is_single_useris_single_user bitbit 1 = 备份时数据库处于单用户模式。1 = Database was single-user at the time of backup.
has_backup_checksumshas_backup_checksums bitbit 1 = 备份包含备份校验和。1 = Backup contains backup checksums.
is_damagedis_damaged bitbit 1 = 创建此备份时,检测到数据库损坏。1 = Damage to the database was detected when this backup was created. 已要求备份操作忽略错误,继续执行备份。The backup operation was requested to continue despite errors.
begins_log_chainbegins_log_chain bitbit 1 = 这是一个连续的日志备份链中的第一个环节。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.
has_incomplete_metadatahas_incomplete_metadata bitbit 1 = 带不完整元数据的结尾日志备份。1 = A tail log backup with incomplete metadata. 有关详细信息,请参阅结尾日志备份 (SQL Server)For more information, see Tail-Log Backups (SQL Server).
is_force_offlineis_force_offline bitbit 1 = 进行备份时,使用了 NORECOVERY 选项使数据库脱机。1 = Database was taken offline using the NORECOVERY option when the backup was taken.
is_copy_onlyis_copy_only bitbit 1 = 仅复制备份。1 = A copy-only backup. 有关详细信息,请参阅仅复制备份 (SQL Server)For more information, see Copy-Only Backups (SQL Server).
first_recovery_fork_guidfirst_recovery_fork_guid uniqueidentifieruniqueidentifier 起始恢复分叉的 ID。ID of the starting recovery fork. 这对应于FirstRecoveryForkID RESTORE HEADERONLY。This corresponds to FirstRecoveryForkID of RESTORE HEADERONLY.

对于数据备份, first_recovery_fork_guid等于last_recovery_fork_guidFor data backups, first_recovery_fork_guid equals last_recovery_fork_guid.
last_recovery_fork_guidlast_recovery_fork_guid uniqueidentifieruniqueidentifier 结束恢复分叉的 ID。ID of the ending recovery fork. 这对应于RecoveryForkID RESTORE HEADERONLY。This corresponds to RecoveryForkID of RESTORE HEADERONLY.

对于数据备份, first_recovery_fork_guid等于last_recovery_fork_guidFor data backups, first_recovery_fork_guid equals last_recovery_fork_guid.
fork_point_lsnfork_point_lsn numeric(25,0)numeric(25,0) 如果first_recovery_fork_guid不等于last_recovery_fork_guid,这是分叉点的日志序列号。If first_recovery_fork_guid is not equal to last_recovery_fork_guid, this is the log sequence number of the fork point. 否则,该值为 NULL。Otherwise, the value is NULL.
database_guiddatabase_guid uniqueidentifieruniqueidentifier 数据库的唯一 ID。Unique ID for the database. 这对应于BindingID RESTORE HEADERONLY。This corresponds to BindingID of RESTORE HEADERONLY. 还原数据库时,将分配一个新值。When the database is restored, a new value is assigned.
family_guidfamily_guid uniqueidentifieruniqueidentifier 创建时原始数据库的唯一 ID。Unique ID of the original database at creation. 还原数据库时,即使还原为其他名称,此值也保持不变。This value remains the same when the database is restored, even to a different name.
differential_base_lsndifferential_base_lsn numeric(25,0)numeric(25,0) 差异备份的基准 LSN。Base LSN for differential backups. 对于单基准的差异备份;更改的 Lsn 大于或等于differential_base_lsn包含在差异备份。For a single-based differential backup; changes with LSNs greater than or equal to differential_base_lsn are included in the differential backup.

对于多基准差异备份,值为 NULL,并且必须在文件级别确定 LSN 的基础 (请参阅backupfile (TRANSACT-SQL))。For a multibased differential, the value is NULL, and the base LSN must be determined at the file level (see backupfile (Transact-SQL)).

对于非差异备份类型,该值始终为 NULL。For nondifferential backup types, the value is always NULL.
differential_base_guiddifferential_base_guid uniqueidentifieruniqueidentifier 对于单基准的差异备份,该值为差异基准的唯一标识符。For a single-based differential backup, the value is the unique identifier of the differential base.

对于多基准的差异备份,该值为 NULL,并且必须在文件级别确定差异基准。For multibased differentials, the value is NULL, and the differential base must be determined at the file level.

对于非差异备份类型,该值为 NULL。For nondifferential backup types, the value is NULL.
compressed_backup_sizecompressed_backup_size Numeric(20,0)Numeric(20,0) 磁盘上存储的备份的总字节数。Total Byte count of the backup stored on disk.

若要计算压缩率,请使用backup_sizebackup_sizeTo calculate the compression ratio, use compressed_backup_size and backup_size.

期间msdb升级,此值设置为 NULL。During an msdb upgrade, this value is set to NULL. 表示未压缩的备份。which indicates an uncompressed backup.
key_algorithmkey_algorithm nvarchar(32)nvarchar(32) 用于加密备份的加密算法。The encryption algorithm used to encrypt the backup. NO_Encryption 值指示备份未加密。NO_Encryption value indicated that the backup was not encrypted.
encryptor_thumbprintencryptor_thumbprint varbinary(20)varbinary(20) 可用于在数据库中查找证书或非对称密钥的加密程序的指纹。The thumbprint of the encryptor which can be used to find certificate or the asymmetric key in the database. 在备份未加密的情况下,此值为 NULL。In the case where the backup was not encrypted, this value is NULL.
encryptor_typeencryptor_type nvarchar(32)nvarchar(32) 使用的加密程序的类型:证书或非对称密钥。The type of encryptor used: Certificate or Asymmetric Key. .. 在备份未加密的情况下,此值为 NULL。In the case where the backup was not encrypted, this value is NULL.

备注Remarks

RESTORE VERIFYONLY FROM备份设备WITH LOADHISTORY 使用填充的列backupmediaset介质集标头中的相应值的表。RESTORE VERIFYONLY FROM backup_device WITH LOADHISTORY populates the column of the backupmediaset table with the appropriate values from the media-set header.

若要减少此表中和其他备份和历史记录表中的行数,请执行sp_delete_backuphistory存储过程。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

备份和还原表(Transact SQL) Backup and Restore Tables (Transact-SQL)
backupfile (Transact-SQL) backupfile (Transact-SQL)
backupfilegroup (Transact-SQL) backupfilegroup (Transact-SQL)
backupmediafamily (Transact-SQL) backupmediafamily (Transact-SQL)
backupmediaset (Transact-SQL) backupmediaset (Transact-SQL)
备份和还原期间可能出现的媒体错误 (SQL Server) Possible Media Errors During Backup and Restore (SQL Server)
媒体集、媒体簇和备份集 (SQL Server) Media Sets, Media Families, and Backup Sets (SQL Server)
恢复模式 (SQL Server) Recovery Models (SQL Server)
RESTORE HEADERONLY (Transact-SQL) RESTORE HEADERONLY (Transact-SQL)
备份和还原表(Transact SQL)Backup and Restore Tables (Transact-SQL)