1

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

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.

| improve this answer | |
  • Sort of. I don't get all the backup logs from before Apr 2020, yet when I look at the Error Logs, and filter by BACKUP type, I see everything as far back as Jan 2020. Why? – Fandango68 May 22 at 0:35
  • Edited my answer, Your question was to get backup in a month and hence above query will give your all backups done in last one month. You can change the value of 30(WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 30)) with your desired number and you should get the result. Also note that, in order to this work, backup set shouldn't be archived in your environment. – Learning_DBAdmin May 22 at 16:44
  • 1
    Sorry my response above was based on curiosity (I removed the WHERE line). And that's when I noticed the variance. The original OP stands, and your script does answer it. Thanks – Fandango68 May 23 at 12:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.