SQL Server: How to get total number of backups done in a month?

Question

I need to gather monthly statistics for my boss, on how many FULL backups were done in a month and which number Succeeded and which failed if any.

Is there a simple query that could give me this, based on SQL Server logs?

Thanks

Answer 1

You might use below script to get history of backups in last 1 month:

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, 
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() - 30) 
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date 

Here 30 is a variable, which can be changed with any number up to which you want backup details however this would work only if you are not archiving backupset table in msdb database.

Please refer below links for detailed info on these backup sets and what is backup fails:

https://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/

Query that displays backup information (successful and failed) SQL Server

Hope this helps.