Si alguna vez te has preguntado cuándo se realizó el último backup de una base de datos en SQL Server o simplemente saber todo su historial de backups, aquí te dejo un dato:

Toda la información de backups y restores realizados en una instancia SQL se guarda en la base de datos ‘msdb‘

Por ello, para consultar la data de los backups usaremos el siguiente script:

SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date, 
DATEDIFF(MI,msdb.dbo.backupset.backup_start_date,msdb.dbo.backupset.backup_finish_date)/60.0 AS [Duracion_estimada],
   msdb.dbo.backupset.expiration_date, 
   CASE msdb..backupset.type
       WHEN 'D' THEN 'Full'  
       WHEN 'L' THEN 'Log'
       WHEN 'I' THEN 'Differential'
       WHEN 'F' THEN 'File or Filegroup'    END AS backup_type,
   ((msdb.dbo.backupset.backup_size/1024.00)/1024.00) as backup_size_MB,  
   msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) --backups de la última semana 
AND msdb..backupset.type NOT LIKE 'L'
AND DATABASE_NAME IN ('dbname')
--AND year(msdb.dbo.backupset.backup_start_date) = año 
--AND month(msdb.dbo.backupset.backup_start_date) = mes

Por supuesto, puedes modificar esta query para mostrar sólo el detalle que deseas, por ejemplo:

• msdb.dbo.backupset.type: Es el tipo de backup que deseas mostrar. En el ejemplo está mostrando todos backups menos los de Transaction Log (NOT LIKE ‘L’). Los posibles valores de este campo son: D = backup full, L = backup de transaction log, I = backup diferencial, F = backup de filegroup.
• msdb.dbo.backupset.database_name: Es el nombre de la base datos a la que se tomó backup. Puedes usar este campo para que no muestre las bases de datos de sistema o para que sólo filtre por una base en específico.
• msdb.dbo.backupset.backup_start_date: Fecha en que se tomó el backup. En el ejemplo he comentado estos campos pero puedes filtrarlo por mes y año por si quieres mostrar sólo los backups tomados en determinadas fechas.

Importante: La data de la msdb no se purga automáticamente, por lo que es necesario llevar a cabo programar una tarea de mantenimiento para purgarla de data desactualizada que puede estar ocupando espacio innecesario en el disco.

Si tienes alguna duda, comentario o sugerencia, puedes mandarme un email a bitácoradeundba@gmail.com.