備份記錄與標頭資訊 (SQL Server)Backup History and Header Information (SQL Server)

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

在伺服器執行個體上的所有 SQL ServerSQL Server 備份與還原作業的完整記錄都會儲存在 msdb 資料庫中。A complete history of all SQL ServerSQL Server backup and restore operations on a server instance is stored in the msdb database. 本主題介紹備份與還原記錄資料表,以及用於存取備份記錄的 Transact-SQLTransact-SQL 陳述式。This topic introduces the backup and restore history tables and also the Transact-SQLTransact-SQL statements that are used to access backup history. 本主題也會討論何時列出資料庫和交易記錄檔最有用,以及媒體標頭資訊與備份標頭資訊這兩者的使用時機。The topic also discusses when listing database and transaction log files is useful and when to use media-header information compared to when to use backup-header information.

重要

若要管理遺失備份和還原記錄最近變更的風險,請經常備份 msdbTo manage the risk of losing recent changes to your backup and restore history, back up msdb frequently. 如需必須備份哪些系統資料庫的相關資訊,請參閱系統資料庫的備份與還原 (SQL Server)For information about which of the system databases you must back up, see Back Up and Restore of System Databases (SQL Server).

本主題內容:In This Topic:

備份與還原記錄資料表Backup and Restore History Tables

此章節介紹儲存 msdb 系統資料庫中的備份及還原中繼資料的歷史記錄資料表。This section introduces the history tables that store backup and restore metadata in the msdb system database.

記錄資料表History table DescriptionDescription
backupfilebackupfile 包含備份的每一個資料檔或記錄檔的資料行。Contains one row for each data or log file that is backed up.
backupfilegroupbackupfilegroup 包含備份組中每一個檔案群組的資料列。Contains a row for each filegroup in a backup set.
backupmediafamilybackupmediafamily 針對每個媒體家族,各包含一個資料列。Contains one row for each media family. 如果媒體家族位於鏡像媒體集中,則在這個媒體集中,這個家族的每個鏡像都會各有一個個別的資料列。If a media family resides in a mirrored media set, the family has a separate row for each mirror in the media set.
backupmediasetbackupmediaset 每個備份組各含一個資料列。Contains one row for each backup media set.
backupsetbackupset 每個備份組各含一個資料列。Contains a row for each backup set.
restorefilerestorefile 針對每個還原的檔案,各包含一個資料列。Contains one row for each restored file. 這包括檔案群組名稱間接還原的檔案。This includes files restored indirectly by filegroup name.
restorefilegrouprestorefilegroup 針對每個還原的檔案群組,各包含一個資料列。Contains one row for each restored filegroup.
restorehistoryrestorehistory 每項還原作業都有一個資料列。Contains one row for each restore operation.

注意

執行還原時,會修改備份記錄資料表和還原記錄資料表。When a restore is performed, backup history tables and restore history tables are modified.

存取備份記錄的 Transact-SQL 陳述式Transact-SQL Statements for Accessing Backup History

還原資訊陳述式會與儲存在某些備份記錄資料表中的資訊對應。The restore information statements correspond with information stored in certain backup history tables.

重要

RESTORE FILELISTONLY、RESTORE HEADERONLY、RESTORE LABELONLY 和 RESTORE VERIFYONLY Transact-SQL 陳述式需要 CREATE DATABASE 權限。The RESTORE FILELISTONLY, RESTORE HEADERONLY, RESTORE LABELONLY, and RESTORE VERIFYONLY Transact-SQL statements require CREATE DATABASE permission. 與舊版相較,這項需求更能完整地保障備份檔案及備份資訊的安全。This requirement secures your backup files and protects your backup information more fully than in previous versions. 如需這個權限的相關資訊,請參閱 GRANT 資料庫權限 (Transact-SQL)For information about this permission, see GRANT Database Permissions (Transact-SQL).

資訊陳述式Information statement 備份記錄資料表Backup history table DescriptionDescription
RESTORE FILELISTONLYRESTORE FILELISTONLY backupfilebackupfile 傳回結果集,其中會有資料庫清單與包含在指定備份組中的記錄。Returns a result set that has a list of the database and log files that are contained in the specified backup set.

如需詳細資訊,請參閱本主題稍後的「列出資料庫與交易記錄檔」。For more information, see "Listing Database and Transaction Log Files," later in this topic.
RESTORE HEADERONLYRESTORE HEADERONLY backupsetbackupset 擷取特定備份裝置上,所有備份組的所有備份前置資料。Retrieves all the backup header information for all backup sets on a particular backup device. 執行 RESTORE HEADERONLY 的結果是結果集。The result from executing RESTORE HEADERONLY is a result set.

如需詳細資訊,請參閱本主題稍後的「檢視備份標頭資訊」。For more information, see "Viewing the Backup-Header Information," later in this topic.
RESTORE LABELONLYRESTORE LABELONLY backupmediasetbackupmediaset 傳回結果集,其中會包含指定備份裝置上之備份媒體的相關資訊。Returns a result set that contains information about the backup media on a specified backup device.

如需詳細資訊,請參閱本主題稍後的「檢視媒體標頭資訊」。For more information, see "Viewing the Media-Header Information," later in this topic.

資料庫和交易記錄檔Database and Transaction Log Files

在列出備份中的資料庫與交易記錄檔時所顯示的資訊包括:邏輯名稱、實體名稱、檔案類型 (資料庫或記錄)、檔案群組成員資格、檔案大小 (以位元組為單位)、允許的檔案大小上限以及預先定義的檔案成長大小 (以位元組為單位)。Information that is displayed when the database and transaction log files are listed in a backup includes the logical name, physical name, file type (database or log), filegroup membership, file size (in bytes), the maximum allowed file size, and the predefined file growth size (in bytes). 在下列狀況中,這些資訊有助於在還原資料庫備份前判斷資料庫備份中的檔案名稱:This information is useful, in the following situations, to determine the names of the files in a database backup before you restore the database backup:

  • 損失了一部磁碟機,內含某資料庫的一或多個檔案。You have lost a disk drive that contains one or more of the files for a database.

    您可以列出資料庫備份中的檔案,以判斷哪些檔案受到影響,然後在還原整個資料庫時將那些檔案還原到別的磁碟機,或者只是還原那些檔案,另外再套用備份資料庫後所建的任何交易記錄備份。You can list the files in the database backup to determine which files were affected, and then restore those files onto a different drive when you restore the whole database; or restore just those files and apply any transaction log backups created since the database was backed up.

  • 要將資料庫從一部伺服器還原至別的伺服器,但該伺服器上沒有目錄結構與磁碟機對應。You are restoring a database from one server onto another server, but the directory structure and drive mapping does not exist on the server.

    列出備份中的檔案能讓您判斷哪些檔案受到影響。Listing the files in the backup let you determine which files are affected. 例如,備份包含需要還原到磁碟機 E 的檔案,但是目的地伺服器並沒有磁碟機 E。當您還原這個檔案時,就必須將它重新放置到其他位置,如磁碟機 Z。For example, the backup contains a file that it has to restore to drive E, but the destination server does not have a drive E. The file must be relocated to another location, such as drive Z, when the file is restored.

媒體標頭資訊Media-Header Information

檢視媒體標頭會顯示關於媒體本身的資訊,而非媒體上備份的資訊。Viewing the media header displays information about the media itself, instead of about the backups on the media. 顯示的媒體標頭資訊包括:媒體名稱、描述、建立媒體標頭的軟體名稱以及寫入媒體標頭的日期。Media header information that is displayed includes the media name, description, name of the software that created the media header, and the date the media header was written.

注意

檢視媒體標頭會很快。Viewing the media header is quick.

如需詳細資訊,請參閱本主題後面的 媒體標頭與備份標頭資訊的比較For more information, see Comparison of Media-Header and Backup-Header Information, later in this topic.

備份標頭資訊Backup-Header Information

檢視備份標頭會顯示有關媒體上所有 SQL ServerSQL Server 與非 SQL ServerSQL Server 備份組的資訊。Viewing the backup header displays information about all SQL ServerSQL Server and non- SQL ServerSQL Server backup sets on the media. 顯示的資訊包括使用的備份裝置類型、備份類型 (例如資料庫、交易、檔案或差異資料庫),以及備份開始與停止的日期/時間資訊。Information that is displayed includes the types of backup devices that are used, the types of backup (for example, database, transaction, file, or differential database), and backup start and stop date/time information. 當您必須決定要還原磁帶上的哪個備份組,或包含在媒體上的備份時,這項資訊會非常有用。This information is useful when you have to determine which backup set on the tape to restore, or the backups that are contained on the media.

注意

對於高容量的磁帶而言,檢視備份標頭資訊可能很花時間,因為必須掃描整個媒體,才能顯示有關媒體上每個備份的資訊。Viewing backup header information can take a long time for high-capacity tapes, because the whole media must be scanned to display information about each backup on the media.

如需詳細資訊,請參閱本主題後面的 媒體標頭與備份標頭資訊的比較For more information, see Comparison of Media-Header and Backup-Header Information, later in this topic.

要還原的備份組Which Backup Set to Restore

您可以使用備份標頭中的資訊來識別要還原的備份組。You can use information in the backup header to identify which backup set to restore. Database Engine 會針對備份媒體上的每個備份組編號。The Database Engine numbers each backup set on the backup media. 這樣可讓您利用備份組在媒體上的位置,識別您要還原的備份組。This lets you identify the backup set you want to restore by using its position on the media. 例如,下列媒體包含三個備份組。For example, the following media contains three backup sets.

包含 SQL Server 備份集的備份媒體Backup media containing SQL Server backup sets

若要還原特定的備份組,可指定所要還原的備份組位置編號。To restore a specific backup set, specify the position number of the backup set you want to restore. 例如,若要還原第二個備份組,請指定 2 當做要還原的備份組。For example, to restore the second backup set, specify 2 as the backup set to restore.

媒體標頭與備份標頭資訊的比較Comparison of Media-Header and Backup-Header Information

下圖提供的範例顯示檢視備份標頭與媒體標頭資訊之間的差異。The following illustration provides an example of the differences between viewing backup-header and media-header information. 取得媒體標頭時,只需從磁帶的開頭擷取資訊。Obtaining the media header requires retrieving information from only the start of the tape. 取得備份標頭時,則必須掃描整捲磁帶來查看每個備份組的標頭。Obtaining the backup header requires scanning the whole tape to look at the header of every backup set.

包含三個 SQL Server 備份集的媒體集Media set containing three SQL Server backup sets

注意

使用具有多個媒體家族的媒體集時,會將媒體標頭與備份組寫入所有媒體家族。When you use media sets that have multiple media families, the media header and backup set are written to all media families. 因此,只需要針對這些報表作業提供單一媒體家族。Therefore, you only have to provide a single media family for these reporting operations.

如需有關如何檢視媒體標頭的詳細資訊,請參閱本主題前面的「檢視媒體標頭資訊」。For information about how to view the media-header, see "Viewing the Media-Header Information," earlier in this topic.

如需有關如何檢視備份裝置上所有備份組之備份標頭資訊的詳細資訊,請參閱本主題前面的<檢視備份標頭資訊>。For information about how to view the backup header information for all backup sets on a backup device, see "Viewing the Backup-Header Information," earlier in this topic.

備份驗證Backup Verification

驗證備份不是必要的步驟,但仍是很有用的作法。Although not required, verifying a backup is a useful practice. 驗證備份會檢查備份實際上是否完整無缺,以確定備份中的所有檔案都可以讀取也可以還原,並確定在您需要時可以還原備份。Verifying a backup checks that the backup is intact physically, to ensure that all the files in the backup are readable and can be restored, and that you can restore your backup in the event you need to use it. 您必須了解,驗證備份並不會驗證備份上的資料結構。It is important to understand that verifying a backup does not verify the structure of the data on the backup. 不過,如果是使用 WITH CHECKSUMS 所建立的備份,則使用 WITH CHECKSUMS 來驗證備份可提供適當的指示,指出備份中資料的可靠性。However, if the backup was created using WITH CHECKSUMS, verifying the backup using WITH CHECKSUMS can provide a good indication of the reliability of the data on the backup.

相關工作Related Tasks

從備份與還原記錄資料表中刪除舊的資料列To delete old rows from backup and restore history tables

從備份與還原記錄資料表中刪除特定資料庫的所有資料列To delete all rows for a specific database from backup and restore history tables

檢視備份組中的資料與記錄檔To view the data and log files in a backup set

檢視媒體標頭資訊To view media header information

檢視備份標頭資訊To view backup header information

從備份與還原記錄資料表中刪除舊的資料列To delete old rows from backup and restore history tables

從備份與還原記錄資料表中刪除特定資料庫的所有資料列To delete all rows for a specific database from backup and restore history tables

檢視媒體標頭資訊To view media header information

檢視備份標頭資訊To view backup header information

若要檢視備份組中的檔案To view the files in a backup set

驗證備份To verify a backup

另請參閱See Also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
媒體集、媒體家族與備份組 (SQL Server) Media Sets, Media Families, and Backup Sets (SQL Server)
備份裝置 (SQL Server) Backup Devices (SQL Server)
鏡像備份媒體集 (SQL Server) Mirrored Backup Media Sets (SQL Server)
備份和還原期間可能發生的媒體錯誤 (SQL Server)Possible Media Errors During Backup and Restore (SQL Server)