backupset (Transact-SQL)

  • Article

S’applique à :SQL ServerAzure SQL Managed InstanceAnalytics Platform System (PDW)

Contient une ligne pour chaque jeu de sauvegarde. Un jeu de sauvegarde contient la sauvegarde issue d’une opération de sauvegarde réussie unique. Les instructions RESTORE, RESTORE FILELISTONLY, RESTORE HEADERONLY et RESTORE VERIFYONLY fonctionnent sur un jeu de sauvegarde unique dans le jeu de médias sur la ou les unités de sauvegarde spécifiées.

Cette table est stockée dans la msdb base de données.

Nom de la colonne Type de données Description
backup_set_id int Numéro d'identification unique du jeu de sauvegardes Identité, clé primaire.
backup_set_uuid uniqueidentifier Numéro d'identification unique du jeu de sauvegardes
media_set_id int Numéro d'identification unique du support de sauvegarde contenant le jeu de sauvegardes. Références backupmediaset(media_set_id).
first_family_number tinyint Numéro de famille du support qui est le premier du jeu de sauvegardes. Sa valeur peut être NULL.
first_media_number smallint Numéro du support qui est le premier du jeu de sauvegardes. Sa valeur peut être NULL.
last_family_number tinyint Numéro de famille du support qui est le dernier du jeu de sauvegardes. Sa valeur peut être NULL.
last_media_number smallint Numéro du support qui est le dernier du jeu de sauvegardes. Sa valeur peut être NULL.
catalog_family_number tinyint Numéro de famille du support contenant le début du répertoire du jeu de sauvegardes. Sa valeur peut être NULL.
catalog_media_number smallint Numéro du support de sauvegardes contenant le début du répertoire du jeu de sauvegardes. Sa valeur peut être NULL.
position int Position du jeu de sauvegardes utilisée lors de la restauration pour localiser les fichiers et le jeu de sauvegardes appropriés. Sa valeur peut être NULL. For more information, see FILE in BACKUP (Transact-SQL).
expiration_date datetime Date et heure d'expiration du jeu de sauvegardes. Sa valeur peut être NULL.
software_vendor_id int Numéro d'identification du fournisseur du logiciel qui a écrit l'en-tête de support de sauvegardes Sa valeur peut être NULL.
name nvarchar(128) Nom du jeu de sauvegardes. Sa valeur peut être NULL.
description nvarchar(255) Description du jeu de sauvegardes. Sa valeur peut être NULL.
user_name nvarchar(128) Nom de l'utilisateur effectuant la sauvegarde Sa valeur peut être NULL.
software_major_version tinyint Numéro de version principale de Microsoft SQL Server. Sa valeur peut être NULL.
software_minor_version tinyint Numéro de version secondaire de SQL Server. Sa valeur peut être NULL.
software_build_version smallint Numéro de build de SQL Server. Sa valeur peut être NULL.
time_zone smallint Différence entre l’heure locale (où l’opération de sauvegarde est effectuée) et le temps universel coordonné (UTC) dans les intervalles de 15 minutes à l’aide des informations de fuseau horaire au moment où l’opération de sauvegarde a démarré. Les valeurs peuvent être comprises entre - 48 et + 48 incluses. La valeur 127 signifie inconnu. Par exemple, -20 correspond à l'heure de l'Est (USA) soit 5 heures après l'heure universelle UTC. Sa valeur peut être NULL.
mtf_minor_version tinyint Numéro de la version secondaire du format de bande Microsoft. Sa valeur peut être NULL.
first_lsn numeric(25,0) Numéro séquentiel dans le journal correspondant au premier enregistrement ou à l'enregistrement le plus ancien du journal dans le jeu de sauvegardes Sa valeur peut être NULL.
last_lsn numeric(25,0) Numéro séquentiel dans le journal correspondant à l'enregistrement du journal suivant après le jeu de sauvegarde. Sa valeur peut être NULL.
checkpoint_lsn numeric(25,0) Numéro séquentiel dans le journal correspondant à l'enregistrement à partir duquel le rétablissement doit démarrer. Sa valeur peut être NULL.
database_backup_lsn numeric(25,0) Numéro séquentiel dans le journal correspondant à la sauvegarde complète la plus récente de la base de données. Sa valeur peut être NULL.

database_backup_lsn constitue le « début du point de contrôle » déclenché lors du démarrage de la sauvegarde. Ce LSN coïncide avec first_lsn si la sauvegarde est effectuée lorsque la base de données est inactive et qu'aucune réplication n'a été configurée.
database_creation_date datetime Date et heure de création de la base de données. Sa valeur peut être NULL.
backup_start_date datetime Date et heure de début de la sauvegarde Sa valeur peut être NULL.
backup_finish_date datetime Date et heure de fin de la sauvegarde Sa valeur peut être NULL.
type char(1) Type de sauvegarde. Valeurs possibles :

D = Base de données
I = Base de données différentielle
L = Journal
F = Fichier ou groupe de fichiers
G =Fichier différentiel
P = Partiel
Q = Partielle différentielle

Sa valeur peut être NULL.
sort_order smallint Ordre de tri utilisé par le serveur effectuant la sauvegarde. Sa valeur peut être NULL. Pour plus d’informations sur les ordres de tri, consultez Prise en charge d’Unicode et du classement.
code_page smallint Page de codes du serveur effectuant la sauvegarde. Sa valeur peut être NULL. Pour plus d’informations sur les pages de codes, consultez Prise en charge d’Unicode et du classement.
compatibility_level tinyint Paramètres définissant le niveau de compatibilité de la base de données. Valeurs possibles :

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)

Sa valeur peut être NULL.

Pour plus d’informations sur les niveaux de compatibilité, consultez Niveau de compatibilité ALTER DATABASE (Transact-SQL).
database_version int Numéro de version de la base de données. Sa valeur peut être NULL.
backup_size numeric(20,0) Taille du jeu de sauvegardes en octets. Sa valeur peut être NULL. Pour les sauvegardes VSS, backup_size est une valeur estimée.
database_name nvarchar(128) Nom de la base de données impliquée dans la sauvegarde. Sa valeur peut être NULL.
server_name nvarchar(128) Nom du serveur exécutant l’opération de sauvegarde de SQL Server. Sa valeur peut être NULL.
machine_name nvarchar(128) Nom de l'ordinateur sur lequel SQL Serverest exécuté. Sa valeur peut être NULL.
flags int Dans SQL Server, la colonne indicateurs a été déconseillée et est remplacée par les colonnes de bits suivantes :

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

Sa valeur peut être NULL.

Dans des jeux de sauvegarde à partir de versions antérieures de SQL Server, bits d'indicateur :
1 = La sauvegarde contient des données consignées de façon minimale.
2 = WITH SNAPSHOT a été utilisé.
4 = La base de données était accessible en lecture seule au moment de la sauvegarde.
8 = La base de données était en mode mono-utilisateur au moment de la sauvegarde.
unicode_locale int Paramètres régionaux Unicode. Sa valeur peut être NULL.
unicode_compare_style int Style de comparaison Unicode. Sa valeur peut être NULL.
collation_name nvarchar(128) Nom du classement. Sa valeur peut être NULL.
Is_password_protected bit Jeu de sauvegardes

protégé par mot de passe :

0 = Non protégé

1 = Protégé
recovery_model nvarchar(60) Mode de récupération de la base de données :

FULL

BULK-LOGGED

SIMPLE
has_bulk_logged_data bit 1 = La sauvegarde contient des données journalisées en bloc.
is_snapshot bit 1 = La sauvegarde a été réalisée à l'aide de l'option SNAPSHOT.
is_readonly bit 1 = La base de données était accessible en lecture seule au moment de la sauvegarde.
is_single_user bit 1 = La base de données était en mode mono-utilisateur lors de la sauvegarde.
has_backup_checksums bit 1 = La sauvegarde contient des sommes de contrôle de sauvegarde.
is_damaged bit 1 = Des dommages ont été détectés pour la base de données lors de la création de cette sauvegarde. L'opération de sauvegarde a dû continuer malgré les erreurs.
begins_log_chain bit 1 = Il s'agit de la première d'une chaîne continue de sauvegardes journalisées. Une séquence de journaux démarre par la première sauvegarde journalisée effectuée après la création de la base de données, ou lorsqu'elle passe du mode de récupération simple à complète ou utilisant les journaux de transactions.
has_incomplete_metadata bit 1 = Sauvegarde de la fin du journal avec des métadonnées incomplètes. Pour plus d’informations, consultez Sauvegardes de la fin du journal (SQL Server).
is_force_offline bit 1 = La base de données est passée en mode hors connexion à l'aide de l'option NORECOVERY lors de la sauvegarde.
is_copy_only bit 1 = Sauvegarde de copie unique. Pour plus d’informations, consultez Sauvegardes de copie uniquement (SQL Server).
first_recovery_fork_guid uniqueidentifier ID du branchement de récupération de début. Cela correspond à FirstRecoveryForkID de RESTORE HEADERONLY.

Pour les sauvegardes de données, first_recovery_fork_guid équivaut à last_recovery_fork_guid.
last_recovery_fork_guid uniqueidentifier ID du branchement de récupération de fin. Cela correspond à RecoveryForkID de RESTORE HEADERONLY.

Pour les sauvegardes de données, first_recovery_fork_guid équivaut à last_recovery_fork_guid.
fork_point_lsn numeric(25,0) Si first_recovery_fork_guid n'équivaut pas à last_recovery_fork_guid, il s'agit du numéro séquentiel dans le journal du point de duplication (fork). Dans le cas contraire, la valeur est NULL.
database_guid uniqueidentifier ID unique de la base de données. Cela correspond à BindingID de RESTORE HEADERONLY. Lors de la restauration de la base de données, une nouvelle valeur est attribuée.
family_guid uniqueidentifier ID unique de la base de données d'origine lors de sa création. Cette valeur demeure identique lors de la restauration de la base de données, même sous un nom différent.
differential_base_lsn numeric(25,0) Numéro de séquence d'enregistrement de base pour les sauvegardes différentielles. Pour une sauvegarde différentielle unique, les modifications avec des LSN supérieurs ou égaux à differential_base_lsn sont incluses dans la sauvegarde différentielle.

Pour une sauvegarde différentielle multibase, la valeur est NULL, tandis que le LSN de base doit être déterminé au niveau du fichier (consultez backupfile (Transact-SQL)).

Pour les types de sauvegarde non différentiels, la valeur est toujours NULL.
differential_base_guid uniqueidentifier Pour une sauvegarde différentielle unique, cette valeur constitue l'identificateur unique de la base différentielle.

Pour les sauvegardes différentielles multiples, cette valeur est NULL, tandis que la base différentielle doit être déterminée au niveau du fichier.

Pour les types de sauvegarde non différentiels, la valeur est NULL.
compressed_backup_size Numeric(20,0) Nombre total d'octets de la sauvegarde stocké sur le disque.

Pour calculer le taux de compression, utilisez compressed_backup_size et backup_size.

Pendant une msdb mise à niveau, cette valeur est définie sur NULL. ce qui indique une sauvegarde non compressée.
key_algorithm nvarchar(32) Algorithme de chiffrement utilisé pour chiffrer la sauvegarde. La valeur NO_Encryption indique que la sauvegarde n'est pas chiffrée.
encryptor_thumbprint varbinary(20) Empreinte numérique du chiffreur pouvant être utilisé pour rechercher un certificat ou la clé asymétrique dans la base de données. Si la sauvegarde n'est pas chiffrée, cette valeur est NULL.
encryptor_type nvarchar(32) Type de chiffreur utilisé : certificat ou clé asymétrique. Si la sauvegarde n'est pas chiffrée, cette valeur est NULL.
last_valid_restore_time datetime Horodatage du dernier enregistrement du journal des transactions inclus dans la sauvegarde du journal des transactions, pour les enregistrements de journal qui comportent un horodatage. Aide à construire des plans de restauration en vous aidant à trouver la dernière sauvegarde du journal à restaurer lorsque la STOPAT clause est spécifiée dans l’instruction RESTORE LOG . Cette sauvegarde de journal a la last_valid_restore_time strictement supérieure à la durée spécifiée dans la STOPAT clause. Introduite dans SQL Server 2022 (16.x).
compression_algorithm nvarchar(32) Algorithme de compression utilisé lors de la création de la sauvegarde SQL Server. Introduite dans SQL Server 2022 (16.x). La valeur par défaut est MS_XPRESS. Pour plus d’informations, consultez COMPRESSION DE SAUVEGARDE et accélération intégrée et déchargement.

Notes

  • RESTORE VERIFYONLY FROM <backup_device> WITH LOADHISTORY remplit la colonne de la backupmediaset table avec les valeurs appropriées de l’en-tête media-set.
  • Pour réduire le nombre de lignes dans cette table et dans d'autres tables de sauvegarde et d'historique, exécutez la procédure stockée sp_delete_backuphistory.
  • Pour SQL Managed Instance, consultez la transparence des sauvegardes et comment surveiller les sauvegardes.

Exemples

Interroger l’historique de sauvegarde

La requête suivante retourne les informations de sauvegarde réussies des deux derniers mois.

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;

Étapes suivantes