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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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
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:
Hope this helps.