backupset (Transact-SQL)

  • Artículo

Se aplica a:SQL ServerAzure SQL Managed InstanceAnalytics Platform System (PDW)

Contiene una fila por cada conjunto de copia de seguridad. Un conjunto de copia de seguridad contiene la copia de seguridad de una sola operación de copia de seguridad realizada correctamente. Las instrucciones RESTORE, RESTORE FILELISTONLY, RESTORE HEADERONLY y RESTORE VERIFYONLY actúan sobre un solo conjunto de copia de seguridad en el conjunto de medios de los dispositivos de copia de seguridad especificados.

Esta tabla se almacena en la msdb base de datos.

Nombre de la columna Tipo de datos Descripción
backup_set_id int Número de identificación único de conjunto de copia de seguridad que identifica al conjunto de copia de seguridad. Clave principal de identidad.
backup_set_uuid uniqueidentifier Número de identificación único de conjunto de copia de seguridad que identifica al conjunto de copia de seguridad.
media_set_id int Número de identificación único de conjunto de medios que identifica el conjunto de medios que contiene el conjunto de copia de seguridad. References backupmediaset(media_set_id).
first_family_number tinyint Número de familia del medio en el que comienza el conjunto de copia de seguridad. Puede ser NULL.
first_media_number smallint Número de medio del medio en el que comienza el conjunto de copia de seguridad. Puede ser NULL.
last_family_number tinyint Número de familia del medio en el que termina el conjunto de copia de seguridad. Puede ser NULL.
last_media_number smallint Número de medio del medio en el que termina el conjunto de copia de seguridad. Puede ser NULL.
catalog_family_number tinyint Número de familia del medio que contiene el comienzo del directorio del conjunto de copia de seguridad. Puede ser NULL.
catalog_media_number smallint Número de medio del medio que contiene el comienzo del directorio del conjunto de copia de seguridad. Puede ser NULL.
position int Posición del conjunto de copia de seguridad utilizada en la operación de restauración para buscar el conjunto de copia de seguridad y los archivos correspondientes. Puede ser NULL. Para obtener más información, vea FILE en BACKUP (Transact-SQL).
expiration_date datetime Fecha y hora de expiración del conjunto de copia de seguridad. Puede ser NULL.
software_vendor_id int Número de identificación del proveedor de software que escribe el encabezado de medios de copia de seguridad. Puede ser NULL.
name nvarchar(128) Nombre del conjunto de copia de seguridad. Puede ser NULL.
descripción nvarchar(255) Descripción del conjunto de copia de seguridad. Puede ser NULL.
user_name nvarchar(128) Nombre del usuario que realiza la operación de copia de seguridad. Puede ser NULL.
software_major_version tinyint Número de versión principal de Microsoft SQL Server. Puede ser NULL.
software_minor_version tinyint Número de versión secundaria de SQL Server. Puede ser NULL.
software_build_version smallint Número de compilación de SQL Server. Puede ser NULL.
time_zone smallint Diferencia entre la hora local (donde se realiza la operación de copia de seguridad) y la hora universal coordinada (UTC) en intervalos de 15 minutos mediante la información de zona horaria en el momento en que comenzó la operación de copia de seguridad. Los valores pueden variar entre -48 y +48, inclusive. El valor 127 indica que se desconoce la diferencia. Por ejemplo, -20 es la hora estándar del Este (EST) o cinco horas después de UTC. Puede ser NULL.
mtf_minor_version tinyint Número de versión secundaria del formato de cinta de Microsoft. Puede ser NULL.
first_lsn numeric(25,0) Número de secuencia de registro de la primera entrada de registro del conjunto de copia de seguridad o de la más antigua. Puede ser NULL.
last_lsn numeric(25,0) Número de secuencia de registro de la siguiente entrada del registro después del conjunto de copia de seguridad. Puede ser NULL.
checkpoint_lsn numeric(25,0) Número de secuencia de registro de la entrada del registro en la que debe comenzar la operación de rehacer. Puede ser NULL.
database_backup_lsn numeric(25,0) Número de secuencia de registro de la copia de seguridad completa más reciente de la base de datos. Puede ser NULL.

database_backup_lsn es el “inicio del punto de comprobación” que se desencadena cuando comienza la copia de seguridad. Este LSN coincide con first_lsn si la copia de seguridad se realiza cuando la base de datos está inactiva y no está configurada la replicación.
database_creation_date datetime Fecha y hora en que se creó originalmente la base de datos. Puede ser NULL.
backup_start_date datetime Fecha y hora en que comenzó la operación de copia de seguridad. Puede ser NULL.
backup_finish_date datetime Fecha y hora en que terminó la operación de copia de seguridad. Puede ser NULL.
type char(1) Tipo de copia de seguridad. Puede ser:

D = Base de datos
I = Base de datos diferencial
L = Registro
F = Archivo o grupo de archivos
G = Archivo diferencial
P = Parcial
Q = Parcial diferencial

Puede ser NULL.
sort_order smallint Criterio de ordenación del servidor que realiza la operación de copia de seguridad. Puede ser NULL. Para obtener más información sobre los criterios de ordenación y las intercalaciones, consulte Compatibilidad con la intercalación y Unicode.
code_page smallint Página de códigos del servidor que realiza la operación de copia de seguridad. Puede ser NULL. Para obtener más información sobre las páginas de códigos, consulte Compatibilidad con la intercalación y Unicode.
compatibility_level tinyint Configuración del nivel de compatibilidad para la base de datos. Puede ser:

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)

Puede ser NULL.

Para obtener más información sobre los niveles de compatibilidad, vea Nivel de compatibilidad de ALTER DATABASE (Transact-SQL).
database_version int Número de versión de la base de datos. Puede ser NULL.
backup_size numeric(20,0) Tamaño del conjunto de copia de seguridad, en bytes. Puede ser NULL. En el caso de las copias de seguridad de VSS, backup_size es un valor estimado.
database_name nvarchar(128) Nombre de la base de datos que forma parte de la operación de copia de seguridad. Puede ser NULL.
server_name nvarchar(128) Nombre del servidor en el que se ejecuta la operación de copia de seguridad de SQL Server. Puede ser NULL.
machine_name nvarchar(128) Nombre del equipo en el que se ejecuta SQL Server. Puede ser NULL.
flags int En SQL Server, la columna flags ya no se usa y se ha sustituido por las siguientes columnas de tipo bit:

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

Puede ser NULL.

En los conjuntos de copia de seguridad de versiones anteriores de SQL Server, bits de marca:
1 = La copia de seguridad contiene datos registrados al mínimo.
2 = Se utilizó WITH SNAPSHOT.
4 = La base de datos era de solo lectura en el momento de la copia de seguridad.
8 = La base de datos estaba en modo de usuario único en el momento de la copia de seguridad.
unicode_locale int Configuración regional Unicode. Puede ser NULL.
unicode_compare_style int Estilo de comparación en Unicode. Puede ser NULL.
collation_name nvarchar(128) Nombre de intercalación. Puede ser NULL.
Is_password_protected bit Indica si el conjunto de copia de seguridad

está protegido mediante contraseña:

0 = Sin protección

1 = Protegido
recovery_model nvarchar(60) Modelo de recuperación de la base de datos:

FULL

BULK-LOGGED

SIMPLE
has_bulk_logged_data bit 1 = La copia de seguridad contiene datos de registros de operaciones masivas.
is_snapshot bit 1 = La copia de seguridad se realizó con la opción SNAPSHOT.
is_readonly bit 1 = La base de datos era de solo lectura en el momento de la copia de seguridad.
is_single_user bit 1 = La base de datos estaba en el modo de usuario único en el momento de la copia de seguridad.
has_backup_checksums bit 1 = La copia de seguridad contiene sumas de comprobación de copia de seguridad.
is_damaged bit 1 = Se detectaron daños en la base de datos al crear esta copia de seguridad. Se solicitó que la copia de seguridad continuara a pesar de los errores.
begins_log_chain bit 1 = Es el primer elemento de una cadena continua de copias de seguridad de registros. Una cadena de registro empieza por la primera copia de seguridad de registros realizada después de crear la base de datos o cuando se cambia del modelo de recuperación simple al modelo de recuperación completa o al modelo de recuperación optimizado para cargas masivas de registros.
has_incomplete_metadata bit 1 = Copia de seguridad de registros después del error con metadatos incompletos. Para obtener más información, vea Copias del final del registro (SQL Server).
is_force_offline bit 1 = La base de datos se dejó sin conexión mediante la opción NORECOVERY cuando se realizó la copia de seguridad.
is_copy_only bit 1 = Copia de seguridad de solo copia. Para obtener más información, consulte Copias de seguridad de solo copia (SQL Server).
first_recovery_fork_guid uniqueidentifier Id. de la bifurcación de recuperación inicial. Corresponde al valor de FirstRecoveryForkID de RESTORE HEADERONLY.

Para las copias de seguridad de datos, first_recovery_fork_guid es igual a last_recovery_fork_guid.
last_recovery_fork_guid uniqueidentifier Id. de la bifurcación de recuperación final. Corresponde al valor de RecoveryForkID de RESTORE HEADERONLY.

Para las copias de seguridad de datos, first_recovery_fork_guid es igual a last_recovery_fork_guid.
fork_point_lsn numeric(25,0) Si first_recovery_fork_guid no es igual que last_recovery_fork_guid, este es el número de secuencia de registro del punto de bifurcación. En caso contrario, el valor es NULL.
database_guid uniqueidentifier Id. único de la base de datos. Corresponde al valor de BindingID de RESTORE HEADERONLY. Cuando se restaura la base de datos, se asigna un valor nuevo.
family_guid uniqueidentifier Id. único de la base de datos original cuando se creó. Este valor permanece invariable cuando se restaura la base de datos, incluso con un nombre diferente.
differential_base_lsn numeric(25,0) LSN de base para copias de seguridad diferenciales. En el caso de las copias de seguridad diferenciales con una única copia de seguridad base, los cambios cuyo LSN sea mayor o igual que differential_base_lsn se incluyen en la copia de seguridad diferencial.

Para una copia de seguridad diferencial con varias copias de seguridad base, el valor es NULL y el LSN de la copia de seguridad base debe determinarse en el nivel de archivo (vea backupfile (Transact-SQL)).

Para los tipos de copia de seguridad no diferenciales, el valor siempre es NULL.
differential_base_guid uniqueidentifier Para una copia de seguridad diferencial con una única copia de seguridad base, el valor es el identificador único de la base diferencial.

Para las copias de seguridad diferenciales con varias copias de seguridad base, el valor es NULL y la base diferencial debe determinarse en el nivel de archivo.

Para los tipos de copia de seguridad no diferenciales, el valor es NULL.
compressed_backup_size Numeric(20,0) Recuento total de bytes de la copia de seguridad almacenada en disco.

Para calcular la razón de compresión, utilice compressed_backup_size y backup_size.

Durante una msdb actualización, este valor se establece en NULL. lo que indica que la copia de seguridad no está comprimida.
key_algorithm nvarchar(32) Algoritmo de cifrado usado para cifrar la copia de seguridad. El valor NO_Encryption indica que la copia de seguridad no se cifró.
encryptor_thumbprint varbinary(20) La huella digital del sistema de cifrado que se puede utilizar para encontrar el certificado o la clave asimétrica en la base de datos. Si la copia de seguridad no se cifró, este valor es NULL.
encryptor_type nvarchar(32) Tipo de sistema de cifrado usado: certificado o clave asimétrica. Si la copia de seguridad no se cifró, este valor es NULL.
last_valid_restore_time datetime Marca de tiempo del último registro de transacciones incluido en la copia de seguridad del registro de transacciones, para registros que llevan una marca de tiempo. Ayuda a construir planes de restauración al ayudarle a encontrar la última copia de seguridad del registro que se va a restaurar cuando se especifica la STOPAT cláusula en la RESTORE LOG instrucción . Esa copia de seguridad de registros tiene el last_valid_restore_time estrictamente mayor que el tiempo especificado en la STOPAT cláusula . Introducido en SQL Server 2022 (16.x).
compression_algorithm nvarchar(32) Algoritmo de compresión que se usó al crear la copia de seguridad de SQL Server. Introducido en SQL Server 2022 (16.x). El valor predeterminado es MS_XPRESS. Para obtener más información, consulte BACKUP COMPRESSION y aceleración integrada y descarga.

Comentarios

  • RESTORE VERIFYONLY FROM <backup_device> WITH LOADHISTORY rellena la columna de la backupmediaset tabla con los valores adecuados del encabezado del conjunto de medios.
  • Para reducir el número de filas de esta tabla y de otras tablas de copia de seguridad e historial, ejecute el procedimiento almacenado sp_delete_backuphistory.
  • Para SQL Instancia administrada, consulte transparencia de copia de seguridad y supervisión de copias de seguridad.

Ejemplos

Consulta del historial de copias de seguridad

La consulta siguiente devuelve información de copia de seguridad correcta de los últimos dos meses.

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;

Pasos siguientes