Overview

This article lists queries that can be run to get detailed information from the msdb database.

Note that it is recommended to save the output in CSV format.

The results of these queries will help in identifying whether the backups performed for the DB are external to Druva leading to log chain broken scenario.

Tip: Check if the backups are performed outside the schedule set in Druva and also value for the username field will help as well.

Procedure title

1. SQL query to check the list of backups performed for a database-

select * from msdb.dbo.backupset where database_name = '<DB_name>';

2. Query to fetch backup job info with specific parameters like start/finish date, username, backup type, physical device name-

SELECT
bk.database_name,
bk.backup_start_date,
bk.backup_finish_date,
bk.server_name,
bk.user_name,
bk.type,
bf.physical_device_name
FROM msdb.dbo.backupset AS bk
INNER JOIN msdb.dbo.backupmediafamily AS bf on bk.media_set_id =bf.media_set_id
WHERE database_name = '<DB_NAME>’;

3. Query to check when the last log backup of the database was taken-

SELECT d.name,
MAX(b.backup_finish_date) AS backup_finish_date
FROM  master.sys.sysdatabases d
LEFT OUTER JOIN msdb..backupset b
ON b.database_name = d.name
AND b.type='L'
GROUP BY d.name
ORDER BY backup_finish_date DESC;

4. Query to check last backup of the database with backup type-

SELECT d.name,
d.recovery_model_desc,
b.type, -- type of backup
MAX(b.backup_finish_date) AS backup_finish_date
FROM  master.sys.databases d
LEFT OUTER JOIN msdb..backupset b
ON b.database_name = d.name
GROUP BY d.name, d.recovery_model_desc, b.type
ORDER BY backup_finish_date DESC;

Type Can be:
D = Database OR Full
I = Differential database
L = Log
F = File or filegroup
G =Differential file
P = Partial
Q = Differential partial
Can be NULL.

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

You can also fetch SQL server logs for further troubleshooting-
- Expand SQL Server Logs section
- Click on Current
- Select and Export these in CSV format

Overview

Procedure title

See also