RESTORE 语句 - HEADERONLY (Transact-SQL)RESTORE Statements - HEADERONLY (Transact-SQL)

适用对象:是SQL Server 是Azure SQL 数据库(仅限托管实例)否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

返回包含 SQL ServerSQL Server 中特定备份设备上所有备份集的所有备份标头信息的结果集。Returns a result set containing all the backup header information for all backup sets on a particular backup device in SQL ServerSQL Server.

备注

有关参数的说明,请参阅 RESTORE 参数 (Transact-SQL)For the descriptions of the arguments, see RESTORE Arguments (Transact-SQL).

“主题链接”图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

  
RESTORE HEADERONLY   
FROM <backup_device>   
[ WITH   
 {  
--Backup Set Options  
   FILE = { backup_set_file_number | @backup_set_file_number }   
 | PASSWORD = { password | @password_variable }   
  
--Media Set Options  
 | MEDIANAME = { media_name | @media_name_variable }   
 | MEDIAPASSWORD = { mediapassword | @mediapassword_variable }  
  
--Error Management Options  
 | { CHECKSUM | NO_CHECKSUM }   
 | { STOP_ON_ERROR | CONTINUE_AFTER_ERROR }  
  
--Tape Options  
 | { REWIND | NOREWIND }   
 | { UNLOAD | NOUNLOAD }    
 } [ ,...n ]  
]  
[;]  
  
<backup_device> ::=  
{   
   { logical_backup_device_name |  
      @logical_backup_device_name_var }  
   | { DISK | TAPE | URL } = { 'physical_backup_device_name' |  
       @physical_backup_device_name_var }   
}  
  

备注

URL 是用于为 Microsoft Azure Blob 存储指定位置和文件名的格式,从 SQL Server 2012 (11.x)SQL Server 2012 (11.x) SP1 CU2 开始受支持。URL is the format used to specify the location and the file name for Microsoft Azure Blob Storage and is supported starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x) SP1 CU2. 虽然 Microsoft Azure 存储是一种服务,不过实现与磁盘和磁带类似,从而可为所有三种设备提供一致且无缝的还原体验。Although Microsoft Azure storage is a service, the implementation is similar to disk and tape to allow for a consistent and seamless restore experience for all the three devices.

参数Arguments

有关 RESTORE HEADERONLY 参数的说明,请参阅 RESTORE 参数 (Transact-SQL)For descriptions of the RESTORE HEADERONLY arguments, see RESTORE Arguments (Transact-SQL).

结果集Result Sets

对于给定设备上的每个备份,服务器均发送一行包含以下各列的标头信息:For each backup on a given device, the server sends a row of header information with the following columns:

备注

RESTORE HEADERONLY 会查看介质上的所有备份集。RESTORE HEADERONLY looks at all backup sets on the media. 因此,使用高容量磁带机时,生成此结果集可能需要一些时间。Therefore, producing this result set when using high-capacity tape drives can take some time. 若要快速查看介质而不获取有关每个备份集的信息,请使用 RESTORE LABELONLY 或指定 FILE = backup_set_file_number 。To get a quick look at the media without getting information about every backup set, use RESTORE LABELONLY or specify FILE = backup_set_file_number.

备注

由于 MicrosoftMicrosoft 磁带格式的固有特点,来自其他软件程序的备份集可以与 MicrosoftMicrosoftSQL ServerSQL Server 备份集在同一介质上占有空间。Due to the nature of MicrosoftMicrosoft Tape Format, it is possible for backup sets from other software programs to occupy space on the same media as MicrosoftMicrosoftSQL ServerSQL Server backup sets. 在由 RESTORE HEADERONLY 返回的结果集中,每一个来自其他软件程序的备份集都占用一行。The result set returned by RESTORE HEADERONLY includes a row for each of these other backup sets.

列名Column name 数据类型Data type SQL Server 备份集说明Description for SQL Server backup sets
BackupNameBackupName nvarchar(128)nvarchar(128) 备份集名称。Backup set name.
BackupDescriptionBackupDescription nvarchar(255)nvarchar(255) 备份集说明。Backup set description.
BackupTypeBackupType smallintsmallint 备份类型:Backup type:

1 = 数据库 1 = Database

2 = 事务日志 2 = Transaction log

4 = 文件 4 = File

5 = 数据库差异 5 = Differential database

6 = 差异文件 6 = Differential file

7 = 部分 7 = Partial

8 = 差异部分 8 = Differential partial
ExpirationDateExpirationDate datetimedatetime 备份集的过期时间。Expiration date for the backup set.
CompressedCompressed BYTE(1)BYTE(1) 是否使用基于软件的压缩对备份集进行压缩:Whether the backup set is compressed using software-based compression:

0 = 否 0 = No

1 = 是 1 = Yes
位置Position smallintsmallint 备份集在卷中的位置(用于 FILE = 选项)。Position of the backup set in the volume (for use with the FILE = option).
DeviceTypeDeviceType tinyinttinyint 与用于备份操作的设备对应的编号:Number corresponding to the device used for the backup operation.

磁盘:Disk:

2 = 逻辑 2 = Logical

102 = 物理 102 = Physical

磁带:Tape:

5 = 逻辑 5 = Logical

105 = 物理 105 = Physical

虚拟设备:Virtual Device:

7 = 逻辑 7 = Logical

107 = 物理 107 = Physical

逻辑设备名称和设备号在 sys.backup_devices 中;有关详细信息,请参阅 sys.backup_devices (Transact-SQL)Logical device names and device numbers are in sys.backup_devices; for more information, see sys.backup_devices (Transact-SQL).
UserNameUserName nvarchar(128)nvarchar(128) 执行备份操作的用户名。User name that performed the backup operation.
ServerNameServerName nvarchar(128)nvarchar(128) 写入备份集的服务器名称。Name of the server that wrote the backup set.
DatabaseNameDatabaseName nvarchar(128)nvarchar(128) 已备份的数据库名称。Name of the database that was backed up.
DatabaseVersionDatabaseVersion intint 从其中创建备份的数据库的版本。Version of the database from which the backup was created.
DatabaseCreationDateDatabaseCreationDate datetimedatetime 数据库的创建日期和时间。Date and time the database was created.
BackupSizeBackupSize numeric(20,0)numeric(20,0) 备份大小(以字节为单位)。Size of the backup, in bytes.
FirstLSNFirstLSN numeric(25,0)numeric(25,0) 备份集中第一个日志记录的日志序列号。Log sequence number of the first log record in the backup set.
LastLSNLastLSN numeric(25,0)numeric(25,0) 备份集之后的下一条日志记录的日志序列号。Log sequence number of the next log record after the backup set.
CheckpointLSNCheckpointLSN numeric(25,0)numeric(25,0) 创建备份时最后一个检查点的日志序号。Log sequence number of the most recent checkpoint at the time the backup was created.
DatabaseBackupLSNDatabaseBackupLSN numeric(25,0)numeric(25,0) 最近的数据库完整备份的日志序列号。Log sequence number of the most recent full database backup.

DatabaseBackupLSN 是“检查点的起点”,在备份开始时触发 。DatabaseBackupLSN is the "begin of checkpoint" that is triggered when the backup starts. 如果在数据库空闲且未配置复制时进行备份,此 LSN 将与 FirstLSN 一致 。This LSN will coincide with FirstLSN if the backup is taken when the database is idle and no replication is configured.
BackupStartDate BackupStartDate datetimedatetime 备份操作的开始日期和时间。Date and time that the backup operation began.
BackupFinishDateBackupFinishDate datetimedatetime 备份操作的完成日期和时间。Date and time that the backup operation finished.
SortOrderSortOrder smallintsmallint 服务器排列次序。Server sort order. 该列仅对数据库备份有效。This column is valid for database backups only. 提供该列是为了向后兼容。Provided for backward compatibility.
CodePageCodePage smallintsmallint 服务器使用的服务器代码页或字符集。Server code page or character set used by the server.
UnicodeLocaleIdUnicodeLocaleId intint 用于 Unicode 字符数据排序的服务器 Unicode 区域设置 ID 配置选项。Server Unicode locale ID configuration option used for Unicode character data sorting. 提供该列是为了向后兼容。Provided for backward compatibility.
UnicodeComparisonStyleUnicodeComparisonStyle intint 服务器 Unicode 比较风格配置选项,可提供对 Unicode 数据排序的额外控制。Server Unicode comparison style configuration option, which provides additional control over the sorting of Unicode data. 提供该列是为了向后兼容。Provided for backward compatibility.
CompatibilityLevelCompatibilityLevel tinyinttinyint 从其中创建备份的数据库兼容级别设置。Compatibility level setting of the database from which the backup was created.
SoftwareVendorIdSoftwareVendorId intint 软件供应商标识号。Software vendor identification number. 对于 SQL Server,该号码为 4608(用十六进制表示为 0x1200) 。For SQL Server, this number is 4608 (or hexadecimal 0x1200).
SoftwareVersionMajorSoftwareVersionMajor intint 创建备份集的服务器主要版本号。Major version number of the server that created the backup set.
SoftwareVersionMinorSoftwareVersionMinor intint 创建备份集的服务器次要版本号。Minor version number of the server that created the backup set.
SoftwareVersionBuildSoftwareVersionBuild intint 创建备份集的服务器内部版本号。Build number of the server that created the backup set.
MachineNameMachineName nvarchar(128)nvarchar(128) 执行备份操作的计算机名称。Name of the computer that performed the backup operation.
标志Flags intint 设置为 1 时单个标志位的含义 :Individual flags bit meanings if set to 1:

1 = 日志备份包含大容量日志操作 。1 = Log backup contains bulk-logged operations.

2 = 快照备份 。2 = Snapshot backup.

4 = 备份时数据库为只读 。4 = Database was read-only when backed up.

8 = 备份时数据库处于单用户模式 。8 = Database was in single-user mode when backed up.

16 = 备份包含备份校验和 。16 = Backup contains backup checksums.

32 = 备份时数据库损坏,但要求备份操作忽略错误继续执行 。32 = Database was damaged when backed up, but the backup operation was requested to continue despite errors.

64 = 结尾日志备份 。64 = Tail log backup.

128 = 包含不完整元数据的结尾日志备份 。128 = Tail log backup with incomplete metadata.

256 = 包含 NORECOVERY 的结尾日志备份 。256 = Tail log backup with NORECOVERY.

重要提示: 建议使用单个布尔值列(下面列出了以 HasBulkLoggedData 开始且以 IsCopyOnly 结束的列)代替 Flags 。Important: We recommend that instead of Flags you use the individual Boolean columns (listed below starting with HasBulkLoggedData and ending with IsCopyOnly).
BindingIDBindingID uniqueidentifieruniqueidentifier 数据库的绑定 ID。Binding ID for the database. 这与 sys.database_recovery_status database_guid 相对应 。This corresponds to sys.database_recovery_status database_guid. 恢复数据库时,会分配一个新值。When a database is restored, a new value is assigned. 另请参阅 FamilyGUID(见下方) 。Also see FamilyGUID (below).
RecoveryForkIDRecoveryForkID uniqueidentifieruniqueidentifier 结尾恢复分支的 ID。ID for the ending recovery fork. 此列与 backupset 表中的 last_recovery_fork_guid 相对应 。This column corresponds to last_recovery_fork_guid in the backupset table.

对于数据备份,RecoveryForkID 等于 FirstRecoveryForkID 。For data backups, RecoveryForkID equals FirstRecoveryForkID.
排序规则Collation nvarchar(128)nvarchar(128) 数据库使用的排序规则。Collation used by the database.
FamilyGUIDFamilyGUID uniqueidentifieruniqueidentifier 创建时的原始数据库 ID。ID of the original database when created. 恢复数据库时,此值保持不变。This value stays the same when the database is restored.
HasBulkLoggedDataHasBulkLoggedData bitbit 1 = 包含大容量日志操作的日志备份 。1 = Log backup containing bulk-logged operations.
IsSnapshotIsSnapshot bitbit 1 = 快照备份 。1 = Snapshot backup.
IsReadOnlyIsReadOnly bitbit 1 = 备份时数据库为只读 。1 = Database was read-only when backed up.
IsSingleUserIsSingleUser bitbit 1 = 备份时数据库处于单用户模式 。1 = Database was single-user when backed up.
HasBackupChecksumsHasBackupChecksums bitbit 1 = 备份包含备份校验和 。1 = Backup contains backup checksums.
IsDamagedIsDamaged bitbit 1 = 备份时数据库损坏,但要求备份操作忽略错误继续执行 。1 = Database was damaged when backed up, but the backup operation was requested to continue despite errors.
BeginsLogChainBeginsLogChain 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.
HasIncompleteMetaDataHasIncompleteMetaData bitbit 1 = 包含不完整元数据的结尾日志备份 。1 = A tail-log backup with incomplete meta-data.

有关包含不完整元数据的结尾日志备份的信息,请参阅结尾日志备份 (SQL Server)For information about tail-log backups with incomplete backup metadata, see Tail-Log Backups (SQL Server).
IsForceOfflineIsForceOffline bitbit 1 = 采用 NORECOVERY 的备份;数据库采用脱机备份 。1 = Backup taken with NORECOVERY; the database was taken offline by backup.
IsCopyOnlyIsCopyOnly bitbit 1 = 仅复制备份 。1 = A copy-only backup.

仅复制备份不会影响数据库的总体备份和恢复进程。A copy-only backup does not impact the overall backup and restore procedures for the database. 有关详细信息,请参阅仅复制备份 (SQL Server)For more information, see Copy-Only Backups (SQL Server).
FirstRecoveryForkIDFirstRecoveryForkID uniqueidentifieruniqueidentifier 起始恢复分支的 ID。ID for the starting recovery fork. 此列与 backupset 表中的 first_recovery_fork_guid 相对应 。This column corresponds to first_recovery_fork_guid in the backupset table.

对于数据备份,FirstRecoveryForkID 等于 RecoveryForkID 。For data backups, FirstRecoveryForkID equals RecoveryForkID.
ForkPointLSNForkPointLSN numeric(25,0) NULLnumeric(25,0) NULL 如果 FirstRecoveryForkID 不等于 RecoveryForkID,此值为分支点的日志序列号 。If FirstRecoveryForkID is not equal to RecoveryForkID, this is the log sequence number of the fork point. 否则,此值为 NULL。Otherwise, this value is NULL.
RecoveryModelRecoveryModel nvarchar(60)nvarchar(60) 数据库的恢复模式,可以是下列值之一:Recovery model for the Database, one of:

FULLFULL

BULK-LOGGEDBULK-LOGGED

SIMPLESIMPLE
DifferentialBaseLSNDifferentialBaseLSN numeric(25,0) NULLnumeric(25,0) NULL 对于单基准的差异备份,该值等于差异基准的 FirstLSN;大于或等于 DifferentialBaseLSN 的 LSN 的更改会包括在差异中 。For a single-based differential backup, the value equals the FirstLSN of the differential base; changes with LSNs greater than or equal to DifferentialBaseLSN are included in the differential.

对于多基准的差异备份,该值为 NULL,基准 LSN 必须在文件级别确定。For a multi-based differential, the value is NULL, and the base LSN must be determined at the file level. 有关详细信息,请参阅 RESTORE FILELISTONLY (Transact-SQL)For more information, see RESTORE FILELISTONLY (Transact-SQL).

对于非差异备份类型,该值始终为 NULL。For non-differential backup types, the value is always NULL.

有关详细信息,请参阅 差异备份 (SQL Server)For more information, see Differential Backups (SQL Server).
DifferentialBaseGUIDDifferentialBaseGUID uniqueidentifieruniqueidentifier 对于单基准的差异备份,该值为差异基准的唯一标识符。For a single-based differential backup, the value is the unique identifier of the differential base.

对于多基准差异,此值为 NULL,且必须确定每个文件的差异基准。For multi-based differentials, the value is NULL, and the differential base must be determined per file.

对于非差异备份类型,此值为 NULL。For non-differential backup types, the value is NULL.
BackupTypeDescriptionBackupTypeDescription nvarchar(60)nvarchar(60) 字符串形式的备份类型有以下几种:Backup type as string, one of:

DATABASEDATABASE

TRANSACTION LOGTRANSACTION LOG

FILE OR FILEGROUPFILE OR FILEGROUP

DATABASE DIFFERENTIALDATABASE DIFFERENTIAL

FILE DIFFERENTIAL PARTIALFILE DIFFERENTIAL PARTIAL

PARTIAL DIFFERENTIALPARTIAL DIFFERENTIAL
BackupSetGUIDBackupSetGUID uniqueidentifier NULLuniqueidentifier NULL 备份集的唯一标识号,可以根据此标识号在介质上标识备份集。Unique identification number of the backup set, by which it is identified on the media.
CompressedBackupSizeCompressedBackupSize bigintbigint 备份集的字节计数。Byte count of the backup set. 对于未压缩的备份,该值与 BackupSize 相同 。For uncompressed backups, this value is the same as BackupSize.

若要计算压缩率,请使用 CompressedBackupSize 和 BackupSize 。To calculate the compression ratio, use CompressedBackupSize and BackupSize.

在 msdb 升级期间,将此值设置为与 BackupSize 列的值相匹配 。During an msdb upgrade, this value is set to match the value of the BackupSize column.
containmentcontainment tinyint 非 NULLtinyint not NULL 适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

指示数据库的包含状态。Indicates the containment status of the database.

0 = 数据库包含状态为 OFF0 = database containment is off

1 = 数据库处于部分包含状态1 = database is in partial containment
KeyAlgorithmKeyAlgorithm nvarchar(32)nvarchar(32) 适用范围:SQL ServerSQL ServerSQL Server 2014 (12.x)SQL Server 2014 (12.x) (CU1) 到当前版本) 。Applies to: SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x) (CU1) through current version.

用于加密备份的加密算法。The encryption algorithm used to encrypt the backup. NO_Encryption 指示备份未加密。NO_Encryption indicates that the backup was not encrypted. 当无法确定正确的值时,此值应为 NULL。When the correct value cannot be determined the value should be NULL.
EncryptorThumbprintEncryptorThumbprint varbinary(20)varbinary(20) 适用范围:SQL ServerSQL ServerSQL Server 2014 (12.x)SQL Server 2014 (12.x) (CU1) 到当前版本) 。Applies to: SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x) (CU1) through current version.

可用于在数据库中查找证书或非对称密钥的加密程序的指纹。The thumbprint of the encryptor which can be used to find certificate or the asymmetric key in the database. 当备份未加密时,此值为 NULL。When the backup was not encrypted, this value is NULL.
EncryptorTypeEncryptorType nvarchar(32)nvarchar(32) 适用范围:SQL ServerSQL ServerSQL Server 2014 (12.x)SQL Server 2014 (12.x) (CU1) 到当前版本) 。Applies to: SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x) (CU1) through current version.

使用的加密程序的类型:证书或非对称密钥。The type of encryptor used: Certificate or Asymmetric Key. 当备份未加密时,此值为 NULL。When the backup was not encrypted, this value is NULL.

备注

如果为备份集指定了密码,则 RESTORE HEADERONL 只显示其密码与命令中指定的 PASSWORD 选项相匹配的备份集的完整信息。If passwords are defined for the backup sets, RESTORE HEADERONLY shows complete information for only the backup set whose password matches the specified PASSWORD option of the command. RESTORE HEADERONLY 还显示没有密码保护的备份集的完整信息。RESTORE HEADERONLY also shows complete information for unprotected backup sets. 将介质上其他受密码保护的备份集的 BackupName 列设置为“Password Protected ”,剩下的所有列为 NULL 。The BackupName column for the other password-protected backup sets on the media is set to 'Password Protected', and all other columns are NULL.

一般备注General Remarks

客户端可以使用 RESTORE HEADERONLY 检索特殊备份设备上的所有备份的所有备份标头信息。A client can use RESTORE HEADERONLY to retrieve all the backup header information for all backups on a particular backup device. 对于备份设备上的每个备份,服务器都会将标头信息作为一行发送。For each backup on the backup device, the server sends the header information as a row.

SecuritySecurity

在备份时,可以根据需要为介质集、备份集或这两者指定密码。A backup operation may optionally specify passwords for a media set, a backup set, or both. 如果已经在介质集或备份集上定义了密码,则必须在 RESTORE 语句中指定正确的密码。When a password has been defined on a media set or backup set, you must specify the correct password or passwords in the RESTORE statement. 这些密码可防止未经授权的用户使用 MicrosoftMicrosoft SQL ServerSQL Server 工具执行还原操作和向介质追加备份集。These passwords prevent unauthorized restore operations and unauthorized appends of backup sets to media using MicrosoftMicrosoft SQL ServerSQL Server tools. 但是,密码不会阻止使用 BACKUP 语句的 FORMAT 选项覆盖介质。However, a password does not prevent overwrite of media using the BACKUP statement's FORMAT option.

重要

此密码提供的安全性较低。The protection provided by this password is weak. 它旨在防止经过授权的用户或未经授权的用户使用 SQL ServerSQL Server 工具执行不正确的还原操作。It is intended to prevent an incorrect restore using SQL ServerSQL Server tools by authorized or unauthorized users. 但是不能防止通过其他方式或通过替换密码来读取备份数据。It does not prevent the reading of the backup data by other means or the replacement of the password. 此功能处于维护模式并且可能会在 Microsoft SQL Server 将来的版本中被删除。This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。Avoid using this feature in new development work, and plan to modify applications that currently use this feature. 保护备份的最佳做法是将备份磁带存储在安全位置,或者备份到由适当的访问控制列表 (ACL) 保护的磁盘文件。The best practice for protecting backups is to store backup tapes in a secure location or back up to disk files that are protected by adequate access control lists (ACLs). ACL 应设置在创建备份的根目录下。The ACLs should be set on the directory root under which backups are created.

权限Permissions

获取有关备份集或备份设备的信息需要具有 CREATE DATABASE 权限。Obtaining information about a backup set or backup device requires CREATE DATABASE permission. 有关详细信息,请参阅 GRANT 数据库权限 (Transact-SQL)For more information, see GRANT Database Permissions (Transact-SQL).

示例Examples

以下示例返回磁盘文件 C:\AdventureWorks-FullBackup.bak 标头中的信息。The following example returns the information in the header for the disk file C:\AdventureWorks-FullBackup.bak.

RESTORE HEADERONLY   
FROM DISK = N'C:\AdventureWorks-FullBackup.bak'   
WITH NOUNLOAD;  
GO  

另请参阅See Also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
backupset (Transact-SQL) backupset (Transact-SQL)
RESTORE REWINDONLY (Transact-SQL) RESTORE REWINDONLY (Transact-SQL)
RESTORE VERIFYONLY (Transact-SQL) RESTORE VERIFYONLY (Transact-SQL)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
备份历史记录和标头信息 (SQL Server) Backup History and Header Information (SQL Server)
在备份或还原期间启用或禁用备份校验和 (SQL Server) Enable or Disable Backup Checksums During Backup or Restore (SQL Server)
媒体集、媒体簇和备份集 (SQL Server) Media Sets, Media Families, and Backup Sets (SQL Server)
恢复模式 (SQL Server)Recovery Models (SQL Server)