Check the latest full and log backups
msdb queries to show the last full and log backup per database, and the recent backup history.
The following queries read the backup history stored in the msdb database. The first returns, for each user database, the date of the last full backup and the date of the last log backup. The second lists the backups started in the last 11 days, with their type, size and files.
Last full and log backup per database
SELECT @@SERVERNAME AS [Instance],
a.name AS [Database Name],
a.recovery_model_desc AS [Recovery Model],
(
SELECT MAX(b.backup_finish_date)
FROM msdb..backupset b
WHERE b.type = 'D'
AND a.name = b.database_name
) AS [Full Backup],
(
SELECT MAX(b.backup_finish_date)
FROM msdb..backupset b
WHERE b.type = 'L'
AND a.name = b.database_name
) AS [Log Backup]
FROM master.sys.databases a
WHERE a.name NOT IN ('master', 'msdb', 'model', 'tempdb')
ORDER BY 1, 2;
Backups of the last 11 days
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,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size AS backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description,
CAST(first_lsn AS VARCHAR) AS first_lsn,
CAST(last_lsn AS VARCHAR) AS last_lsn,
CAST(checkpoint_lsn AS VARCHAR) AS checkpoint_lsn
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() - 11
-- AND msdb..backupset.type = 'D'
ORDER BY backup_finish_date DESC;