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

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse 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. 如需詳細資訊,請參閱中的檔案BACKUP (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 次要版本號碼。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. 如需有關排序次序和定序的詳細資訊,請參閱 < Collation and Unicode SupportFor 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. 如需字碼頁的詳細資訊,請參閱Collation and Unicode SupportFor 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 的備份,backupset 會是估計的值。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.
flagsflags intint SQL ServerSQL Server,則旗標資料行已被取代,取代下列的位元資料行: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 of the starting recovery fork. 這會對應至FirstRecoveryForkID RESTORE HEADERONLY。This corresponds to FirstRecoveryForkID of RESTORE HEADERONLY.

備份資料,如first_recovery_fork_guid equals last_recovery_fork_guidFor data backups, first_recovery_fork_guid equals last_recovery_fork_guid.
last_recovery_fork_guidlast_recovery_fork_guid uniqueidentifieruniqueidentifier 結尾復原分岔的識別碼。ID of the ending recovery fork. 這會對應至RecoveryForkID RESTORE HEADERONLY。This corresponds to RecoveryForkID of RESTORE HEADERONLY.

備份資料,如first_recovery_fork_guid equals 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 資料庫的唯一識別碼。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 建立時原始資料庫的唯一識別碼。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_sizebackupsetTo 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 backup_device 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)