Checking backup status (success or failure) SQL Query

Transact-SQL
1

Hello,

We have a third party software that runs the backup from all the sql servers. No sql server agent jobs running and so, nothing is there in sysjobs or sysjobshistory tables. It basically pulls the backup and copies them to a storage hardware. That III party has reporting capabilities. However, I wanted to see whether I can query tables from msdb database. If I query backupset table, I do not see anything specific that can show success or failure. Will appreciate your input. Thanks.
VIctor

2

If the backup software is not populating backupset, you'll need to contact the vendor of that software to determine what you want.

1 Like
3

Thanks. I thought so.

4

Try something like:

SELECT M.device_type /* 2 seems to be SQL Backup and 7 is VSS */
	,S.[type] /* D=Full, I=Diff, L=Log */
	,S.backup_start_date, S.backup_finish_date, M.physical_device_name, S.is_copy_only
FROM msdb.dbo.backupset S
    JOIN msdb.dbo.backupmediafamily M
        ON S.media_set_id = M.media_set_id
WHERE S.[type] IN ('D') -- Full Backup (I = Diff, L = Log)
    AND S.database_name = '<Database_Name, sysname, Database_Name>'
ORDER BY S.backup_finish_date DESC;

Backups are useless unless they can be restored and tested. Some VSS backup systems will only restore database files if the whole server is restored. Unless you can restore the databases, and run CHECKDB against them, I would ask your infrastructure team to do the VSS backups with is_copy_only = 1 and then do your own SQL Server backups as well.