backupset (Transact-SQL)

適用対象: はいSQL Server (サポートされているすべてのバージョン) はいAzure SQL Managed Instance はいParallel Data Warehouse

バックアップ セットごとに 1 行のデータを格納します。 バックアップ セット には、正常に終了した 1 つのバックアップ操作のバックアップが含まれます。 RESTORE、RESTORE FILELISTONLY、RESTORE HEADERONLY、および RESTORE VERIFYONLY ステートメントは、単一または複数の指定バックアップ デバイス上のメディア セット内にある単一のバックアップ セットに対して機能します。

このテーブルは msdb データベースに格納 されます。

列名 データ型 説明
backup_set_id int バックアップ セットを識別する一意のバックアップ セット識別番号。 ID、主キー。
backup_set_uuid uniqueidentifier バックアップ セットを識別する一意のバックアップ セット識別番号。
media_set_id int バックアップ セットを含むメディア セットを識別する一意のメディア セット識別番号。 backupmediaset(media_set_id) を参照します
first_family_number tinyint バックアップ セットが開始されるメディアのファミリ番号。 NULL にすることができます。
first_media_number smallint バックアップ セットが開始されるメディアのメディア番号。 NULL にすることができます。
last_family_number tinyint バックアップ セットの最終メディアのファミリ番号。 NULL にすることができます。
last_media_number smallint バックアップ セットの最終メディアのメディア番号。 NULL にすることができます。
catalog_family_number tinyint バックアップ セット ディレクトリの先頭を含むメディアのファミリ番号。 NULL にすることができます。
catalog_media_number smallint バックアップ セット ディレクトリの先頭を含むメディアのメディア番号。 NULL にすることができます。
position int 復元操作で使用されるバックアップ セットの位置。適切なバックアップ セットとファイルを検索します。 NULL にすることができます。 詳細については、「FILE in BACKUP (Transact-SQL)」を参照してください
expiration_date datetime バックアップ セットの期限が切れる日付と時刻。 NULL にすることができます。
software_vendor_id int バックアップ メディア ヘッダーを記述するソフトウェア ベンダーの識別番号。 NULL にすることができます。
name nvarchar(128) バックアップ セットの名前。 NULL にすることができます。
description nvarchar (255) バックアップ セットの説明。 NULL にすることができます。
user_name nvarchar(128) バックアップ操作を実行するユーザー名。 NULL にすることができます。
software_major_version tinyint MicrosoftSQL Serverメジャー バージョン番号。 NULL にすることができます。
software_minor_version tinyint SQL Server のマイナー バージョン番号。 NULL にすることができます。
software_build_version smallint SQL Server のビルド番号。 NULL にすることができます。
time_zone smallint バックアップ操作が開始された時点のタイム ゾーン情報を使用して、ローカル時刻 (バックアップ操作が実行されている場所) と 15 分間隔で 協定世界時 (UTC) の差。 -48 ~ +48 の値を指定できます。 値 127 は、不明な状態を表します。 たとえば、-20 は東部標準時刻 (EST) を表し、これは UTC の 5 時間前にあたります。 NULL にすることができます。
mtf_minor_version tinyint Microsoft Tape Format のマイナー バージョン番号。 NULL にすることができます。
first_lsn numeric(25,0) バックアップ セット内の最初または最も古いログ レコードのログ シーケンス番号。 NULL にすることができます。
last_lsn numeric(25,0) バックアップ セットの次のログ レコードのログ シーケンス番号。 NULL にすることができます。
checkpoint_lsn numeric(25,0) 再実行を開始する必要があるログ レコードのログ シーケンス番号。 NULL にすることができます。
database_backup_lsn numeric(25,0) データベース全体の最新バックアップのログ シーケンス番号。 NULL にすることができます。

database_backup_lsn は、バックアップの開始時にトリガーされる "チェックポイントの開始" です。 この LSN は、データベース がアイドル状態first_lsn レプリケーションが構成されていないときにバックアップが作成された場合に、この LSN と一致します。
database_creation_date datetime データベースが最初に作成された日付と時刻。 NULL にすることができます。
backup_start_date datetime バックアップ操作が開始された日時。 NULL にすることができます。
backup_finish_date datetime バックアップ操作が完了した日時。 NULL にすることができます。
type char(1) バックアップの種類。 次の値をとります。

D = データベース

I = 差分データベース

L = ログ

F = ファイルまたはファイル グループ

G =差分ファイル

P = Partial

Q = 差分部分

NULL にすることができます。
sort_order smallint バックアップ操作を実行するサーバーの並べ替え順。 NULL にすることができます。 並べ替え順序と照合順序の詳細については、「照合順序と Unicode の サポート」を参照してください
code_page smallint バックアップ操作を実行しているサーバーのコード ページ。 NULL にすることができます。 コード ページの詳細については、「照合順序と Unicode の サポート」を参照してください
compatibility_level tinyint データベースの互換性レベルの設定。 次の値をとります。

90 = SQL Server 2005 (9.x)

100 = SQL Server 2008

110 = SQL Server 2012 (11.x)

120 = SQL Server 2014 (12.x)

NULL にすることができます。

互換性レベルの詳細については、「ALTER DATABASE 互換性レベル (Transact-SQL)」を参照してください。
database_version int データベースのバージョン番号。 NULL にすることができます。
backup_size numeric(20,0) バックアップ セットのサイズ (バイト単位)。 NULL にすることができます。 VSS バックアップの場合、backup_sizeは推定値です。
database_name nvarchar(128) バックアップ操作に関係するデータベース名。 NULL にすることができます。
server_name nvarchar(128) SQL Server バックアップ操作を実行しているサーバー名。 NULL にすることができます。
machine_name nvarchar(128) SQL Serverを実行しているコンピューターの名前。 NULL にすることができます。
flags int では SQL Server 、flags 列 は非推奨とされ、次のビット列に置き換えられる予定です。

has_bulk_logged_data
is_snapshot
is_readonly
is_single_user
has_backup_checksums
is_damaged
begins_log_chain
has_incomplete_metadata
is_force_offline
is_copy_only

NULL にすることができます。

以前のバージョンの のバックアップ セットでは、 SQL Server ビットにフラグを設定します。
1 = バックアップには最小限のログ データが含まれている。
2 = WITH SNAPSHOT が使用されています。
4 = バックアップ時にデータベースが読み取り専用でした。
8 = データベースはバックアップ時にシングル ユーザー モードでした。
unicode_locale int Unicode ロケール。 NULL にすることができます。
unicode_compare_style int Unicode 比較スタイル。 NULL にすることができます。
collation_name nvarchar(128) 照合順序名。 NULL にすることができます。
Is_password_protected bit バックアップ セットです

password protected:

0 = 保護されていない

1 = Protected
recovery_model nvarchar(60) データベースの復旧モデル。

FULL

BULK-LOGGED

SIMPLE
has_bulk_logged_data bit 1 = バックアップに一括ログ データが含まれている。
is_snapshot bit 1 = SNAPSHOT オプションを使用してバックアップが作成された。
is_readonly bit 1 = バックアップ時にデータベースが読み取り専用でした。
is_single_user bit 1 = データベースはバックアップ時にシングル ユーザーでした。
has_backup_checksums bit 1 = バックアップ チェックサムを含むバックアップ。
is_damaged bit 1 = バックアップの作成時、データベースの損傷が検出された。 エラーに関係なくバックアップ操作の続行が要求されました。
begins_log_chain bit 1 = 連鎖的なログ バックアップの先頭。 ログ チェーンは、データベースの作成後、または単純復旧モデルから完全復旧モデルまたは一括ログ復旧モデルに切り替えたときに、最初に作成されたログ バックアップから始まります。
has_incomplete_metadata bit 1 = 不完全なメタデータでのログ末尾のバックアップ。 詳細については、「ログ末尾のバックアップ (SQL Server)」を参照してください。
is_force_offline bit 1 = バックアップ時、データベースが NORECOVERY オプションによってオフラインにされた。
is_copy_only bit 1 = コピーのみのバックアップ。 詳細については、「コピーのみのバックアップ (SQL Server)」を参照してください。
first_recovery_fork_guid uniqueidentifier 開始復旧フォークの ID。 これは、RESTORE HEADERONLY の FirstRecoveryForkID に対応します。

データ バックアップの場合、は first_recovery_fork_guid同last_recovery_fork_guid。
last_recovery_fork_guid uniqueidentifier 終了復旧フォークの ID。 これは、RESTORE HEADERONLY の RecoveryForkID に対応します。

データ バックアップの場合、は first_recovery_fork_guid同last_recovery_fork_guid。
fork_point_lsn numeric(25,0) first_recovery_fork_guid と等 しくないlast_recovery_fork_guid、 これはフォーク ポイントのログ シーケンス番号です。 それ以外の場合は NULL になります。
database_guid uniqueidentifier データベースの一意の ID。 これは RESTORE HEADERONLY の BindingID に対応します。 データベースを復元すると、新しい値が割り当てられます。
family_guid uniqueidentifier 作成時の元のデータベースの一意の ID。 この値は、データベースが復元された場合でも、別の名前でも同じままです。
differential_base_lsn numeric(25,0) 差分バックアップのベース LSN。 単一ベースの差分バックアップの場合は、LSN が差分バックアップに含まれるdifferential_base_lsn以上の変更。

マルチベースの差分の場合、値は NULL であり、基本 LSN はファイル レベルで決定する必要があります(「backupfile (Transact-SQL)」を参照してください)。

非参照バックアップの種類の場合、値は常に NULL です。
differential_base_guid uniqueidentifier シングル ベースの差分バックアップの場合は、差分ベースの一意識別子。

マルチ ベースの差分バックアップの場合は NULL。差分のベースはファイル レベルで決定される必要があります。

非参照バックアップの種類の場合、値は NULL です。
compressed_backup_size Numeric(20,0) ディスクに格納されたバックアップの総バイト数。

圧縮率を計算するには、 と compressed_backup_sizebackup_size。

msdb のアップグレード中 に、この値は NULL に設定されます。 これは圧縮されていないバックアップを示します。
key_algorithm nvarchar(32) バックアップの暗号化に使用される暗号化アルゴリズム。 NO_Encryption値は、バックアップが暗号化されていない状態を示しました。
encryptor_thumbprint varbinary(20) データベースに保存されている証明書や非対称キーを検索するために使用される暗号化機能の拇印。 バックアップが暗号化されていない場合、この値は NULL になります。
encryptor_type nvarchar(32) 使用される暗号化の種類:証明書キーまたは非対称キー。 . バックアップが暗号化されていない場合、この値は NULL になります。

解説

  • LOADHISTORY を使用した backup_device からの RESTORE verifyonly は、 backupmediaset テーブルの列に、メディアセットヘッダーからの適切な値を設定します。
  • このテーブルおよびその他のバックアップテーブルと履歴テーブルの行の数を減らすには、 sp_delete_backuphistory ストアドプロシージャを実行します。
  • SQL Managed Instance の場合、backupset テーブルには、ユーザーが開始したコピーのみのバックアップのバックアップ履歴が表示されます。 Backupset テーブルには、サービスによって実行される自動バックアップのバックアップ履歴は表示されません。

バックアップ履歴のクエリ

次のクエリでは、過去2か月に成功したバックアップ情報が返されます。

SELECT bs.database_name,
    backuptype = CASE
            WHEN bs.type = 'D'
            AND bs.is_copy_only = 0 THEN 'Full Database'
            WHEN bs.type = 'D'
            AND bs.is_copy_only = 1 THEN 'Full Copy-Only Database'
            WHEN bs.type = 'I' THEN 'Differential database backup'
            WHEN bs.type = 'L' THEN 'Transaction Log'
            WHEN bs.type = 'F' THEN 'File or filegroup'
            WHEN bs.type = 'G' THEN 'Differential file'
            WHEN bs.type = 'P' THEN 'Partial'
            WHEN bs.type = 'Q' THEN 'Differential partial'
        END + ' Backup',
    CASE bf.device_type
            WHEN 2 THEN 'Disk'
            WHEN 5 THEN 'Tape'
            WHEN 7 THEN 'Virtual device'
            WHEN 9 THEN 'Azure Storage'
            WHEN 105 THEN 'A permanent backup device'
            ELSE 'Other Device'
        END AS DeviceType,
    bms.software_name AS backup_software,
    bs.recovery_model,
    bs.compatibility_level,
    BackupStartDate = bs.Backup_Start_Date,
    BackupFinishDate = bs.Backup_Finish_Date,
    LatestBackupLocation = bf.physical_device_name,
    backup_size_mb = CONVERT(decimal(10, 2), bs.backup_size/1024./1024.),
    compressed_backup_size_mb = CONVERT(decimal(10, 2), bs.compressed_backup_size/1024./1024.),
    database_backup_lsn, -- For tlog and differential backups, this is the checkpoint_lsn of the FULL backup it is based on.
    checkpoint_lsn,
    begins_log_chain,
    bms.is_password_protected
FROM msdb.dbo.backupset bs
LEFT OUTER JOIN msdb.dbo.backupmediafamily bf ON bs.[media_set_id] = bf.[media_set_id]
INNER JOIN msdb.dbo.backupmediaset bms ON bs.[media_set_id] = bms.[media_set_id]
WHERE bs.backup_start_date > DATEADD(MONTH, -2, sysdatetime()) --only look at last two months
ORDER BY bs.database_name ASC, bs.Backup_Start_Date DESC;

参照

テーブルのバックアップと復元 (SQL transact-sql)
backupfile (Transact-SQL)
backupfilegroup (Transact-SQL)
backupmediafamily (Transact-SQL)
backupmediaset (Transact-SQL)
バックアップ中および復元中に発生する可能性があるメディア エラー (SQL Server)
メディア セット、メディア ファミリ、およびバックアップ セット (SQL Server)
復旧モデル (SQL Server)
RESTORE HEADERONLY (Transact-SQL)
テーブルのバックアップと復元 (SQL transact-sql)