backupset (Transact-SQL)

Άρθρο
02/29/2024

Applies to: SQL Server Azure SQL Managed Instance Analytics Platform System (PDW)

Contains a row for each backup set. A backup set contains the backup from a single, successful backup operation. 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.

This table is stored in the msdb database.

Column name	Data type	Description
backup_set_id	int	Unique backup set identification number that identifies the backup set. Identity, primary key.
backup_set_uuid	uniqueidentifier	Unique backup set identification number that identifies the backup set.
media_set_id	int	Unique media set identification number that identifies the media set containing the backup set. References backupmediaset(media_set_id).
first_family_number	tinyint	Family number of the media where the backup set starts. Can be NULL.
first_media_number	smallint	Media number of the media where the backup set starts. Can be NULL.
last_family_number	tinyint	Family number of the media where the backup set ends. Can be NULL.
last_media_number	smallint	Media number of the media where the backup set ends. Can be NULL.
catalog_family_number	tinyint	Family number of the media containing the start of the backup set directory. Can be NULL.
catalog_media_number	smallint	Media number of the media containing the start of the backup set directory. Can be NULL.
position	int	Backup set position used in the restore operation to locate the appropriate backup set and files. Can be NULL. For more information, see FILE in BACKUP (Transact-SQL).
expiration_date	datetime	Date and time the backup set expires. Can be NULL.
software_vendor_id	int	Identification number of the software vendor writing the backup media header. Can be NULL.
name	nvarchar(128)	Name of the backup set. Can be NULL.
description	nvarchar(255)	Description of the backup set. Can be NULL.
user_name	nvarchar(128)	Name of the user performing the backup operation. Can be NULL.
software_major_version	tinyint	Microsoft SQL Server major version number. Can be NULL.
software_minor_version	tinyint	SQL Server minor version number. Can be NULL.
software_build_version	smallint	SQL Server build number. Can be NULL.
time_zone	smallint	Difference between local time (where the backup operation is taking place) and Coordinated Universal Time (UTC) in 15-minute intervals using the time zone information at the time the backup operation started. Values can be -48 through +48, inclusive. A value of 127 indicates unknown. For example, -20 is Eastern Standard Time (EST) or five hours after UTC. Can be NULL.
mtf_minor_version	tinyint	Microsoft Tape Format minor version number. Can be NULL.
first_lsn	numeric(25,0)	Log sequence number of the first or oldest log record in the backup set. Can be NULL.
last_lsn	numeric(25,0)	Log sequence number of the next log record after the backup set. Can be NULL.
checkpoint_lsn	numeric(25,0)	Log sequence number of the log record where redo must start. Can be NULL.
database_backup_lsn	numeric(25,0)	Log sequence number of the most recent full database backup. Can be NULL. database_backup_lsn is the "begin of checkpoint" that is triggered when the backup starts. This LSN will coincide with first_lsn if the backup is taken when the database is idle and no replication is configured.
database_creation_date	datetime	Date and time the database was originally created. Can be NULL.
backup_start_date	datetime	Date and time the backup operation started. Can be NULL.
backup_finish_date	datetime	Date and time the backup operation finished. Can be NULL.
type	char(1)	Backup type. Can be: D = Database I = Differential database L = Log F = File or filegroup G =Differential file P = Partial Q = Differential partial Can be NULL.
sort_order	smallint	Sort order of the server performing the backup operation. Can be NULL. For more information about sort orders and collations, see Collation and Unicode Support.
code_page	smallint	Code page of the server performing the backup operation. Can be NULL. For more information about code pages, see Collation and Unicode Support.
compatibility_level	tinyint	Compatibility level setting for the database. Can be: 90 = SQL Server 2005 (9.x) 100 = SQL Server 2008 (10.0.x) 110 = SQL Server 2012 (11.x) 120 = SQL Server 2014 (12.x) 130 = SQL Server 2016 (13.x) 140 = SQL Server 2017 (14.x) 150 = SQL Server 2019 (15.x) 160 = SQL Server 2022 (16.x) Can be NULL. For more information about compatibility levels, see ALTER DATABASE Compatibility Level (Transact-SQL).
database_version	int	Database version number. Can be NULL.
backup_size	numeric(20,0)	Size of the backup set, in bytes. Can be NULL. For VSS backups, backup_size is an estimated value.
database_name	nvarchar(128)	Name of the database involved in the backup operation. Can be NULL.
server_name	nvarchar(128)	Name of the server running the SQL Server backup operation. Can be NULL.
machine_name	nvarchar(128)	Name of the computer running SQL Server. Can be NULL.
flags	int	In SQL Server, the flags column has been deprecated and is being replaced with the following bit columns: 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 Can be NULL. In backup sets from earlier versions of SQL Server, flag bits: 1 = Backup contains minimally logged data. 2 = WITH SNAPSHOT was used. 4 = Database was read-only at the time of backup. 8 = Database was in single-user mode at the time of backup.
unicode_locale	int	Unicode locale. Can be NULL.
unicode_compare_style	int	Unicode compare style. Can be NULL.
collation_name	nvarchar(128)	Collation name. Can be NULL.
Is_password_protected	bit	Is the backup set password protected: 0 = Not protected 1 = Protected
recovery_model	nvarchar(60)	Recovery model for the database: FULL BULK-LOGGED SIMPLE
has_bulk_logged_data	bit	1 = Backup contains bulk-logged data.
is_snapshot	bit	1 = Backup was taken using the SNAPSHOT option.
is_readonly	bit	1 = Database was read-only at the time of backup.
is_single_user	bit	1 = Database was single-user at the time of backup.
has_backup_checksums	bit	1 = Backup contains backup checksums.
is_damaged	bit	1 = Damage to the database was detected when this backup was created. The backup operation was requested to continue despite errors.
begins_log_chain	bit	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_metadata	bit	1 = A tail log backup with incomplete metadata. For more information, see Tail-Log Backups (SQL Server).
is_force_offline	bit	1 = Database was taken offline using the NORECOVERY option when the backup was taken.
is_copy_only	bit	1 = A copy-only backup. For more information, see Copy-Only Backups (SQL Server).
first_recovery_fork_guid	uniqueidentifier	ID of the starting recovery fork. This corresponds to FirstRecoveryForkID of RESTORE HEADERONLY. For data backups, first_recovery_fork_guid equals last_recovery_fork_guid.
last_recovery_fork_guid	uniqueidentifier	ID of the ending recovery fork. This corresponds to RecoveryForkID of RESTORE HEADERONLY. For data backups, first_recovery_fork_guid equals last_recovery_fork_guid.
fork_point_lsn	numeric(25,0)	If first_recovery_fork_guid is not equal to last_recovery_fork_guid, this is the log sequence number of the fork point. Otherwise, the value is NULL.
database_guid	uniqueidentifier	Unique ID for the database. This corresponds to BindingID of RESTORE HEADERONLY. When the database is restored, a new value is assigned.
family_guid	uniqueidentifier	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_lsn	numeric(25,0)	Base LSN for differential backups. For a single-based differential backup; changes with LSNs greater than or equal to differential_base_lsn are included in the differential backup. For a multibased differential, the value is NULL, and the base LSN must be determined at the file level (see backupfile (Transact-SQL)). For nondifferential backup types, the value is always NULL.
differential_base_guid	uniqueidentifier	For a single-based differential backup, the value is the unique identifier of the differential base. For multibased differentials, the value is NULL, and the differential base must be determined at the file level. For nondifferential backup types, the value is NULL.
compressed_backup_size	Numeric(20,0)	Total Byte count of the backup stored on disk. To calculate the compression ratio, use compressed_backup_size and backup_size. During an `msdb` upgrade, this value is set to NULL. which indicates an uncompressed backup.
key_algorithm	nvarchar(32)	The encryption algorithm used to encrypt the backup. NO_Encryption value indicated that the backup was not encrypted.
encryptor_thumbprint	varbinary(20)	The thumbprint of the encryptor which can be used to find certificate or the asymmetric key in the database. In the case where the backup was not encrypted, this value is NULL.
encryptor_type	nvarchar(32)	The type of encryptor used: Certificate or Asymmetric Key. In the case where the backup was not encrypted, this value is NULL.
last_valid_restore_time	datetime	The timestamp of the last transaction log record included in the transaction log backup, for log records that carry a timestamp. Assists in constructing restore plans by helping you find the last log backup to restore when the `STOPAT` clause is specified in the `RESTORE LOG` statement. That log backup has the last_valid_restore_time strictly greater than the time specified in the `STOPAT` clause. Introduced in SQL Server 2022 (16.x).
compression_algorithm	nvarchar(32)	The compression algorithm that was used when creating the SQL Server backup. Introduced in SQL Server 2022 (16.x). Default is `MS_XPRESS`. For more information, see BACKUP COMPRESSION and Integrated acceleration and offloading.

Remarks

RESTORE VERIFYONLY FROM <backup_device> WITH LOADHISTORY populates the column of the backupmediaset table with the appropriate values from the media-set header.
To reduce the number of rows in this table and in other backup and history tables, execute the sp_delete_backuphistory stored procedure.
For SQL Managed Instance, see backup transparency and how to monitor backups.

Examples

Query backup history

The following query returns successful backup information from the past two months.

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 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;

Κοινή χρήση μέσω

backupset (Transact-SQL)

Remarks

Examples

Query backup history

Next steps

Σχόλια

Πρόσθετοι πόροι