How is Backup Age calculated for MS SQL DB monitor?

How is Backup Age calculated for MS SQL DB monitor?

Backup Age represents the number of hours that have elapsed since the most recent backup operation was completed. It is calculated as the difference between the current time and the backup completion time.

Quote
Backup Age (hours) = Current Time − Backup Completion Time
By default, Applications Manager collects information about backups performed within the last 7 days. You can modify this period by specifying the required number of days in the Collect SQL Database Backup Details setting under:

Info
Admin → Performance Polling → MS SQL → Metric Name: Backup

NotesNote: Only backups performed within the configured collection period are considered when calculating and displaying the Backup Age metric.


For example, the following query is used to calculate Backup Details (under the Backup/Restore tab) for the last 100 days (specified using the above option) in the MS SQL DB monitor:
Quote
SELECT server_name, BS.database_name, backup_start_date, backup_finish_date, expiration_date, BS.recovery_model, is_damaged,CAST((CAST(DATEDIFF(s,backup_start_date,backup_finish_date) AS int))/3600 AS varchar) + ' hours, ' + CAST((CAST(DATEDIFF(s,backup_start_date,backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '+ CAST((CAST(DATEDIFF(s,backup_start_date,backup_finish_date) AS int))%60 AS varchar)+ ' seconds' AS 'TotalTime', BS.type AS backup_type, backup_size, logical_device_name, physical_device_name, BS.name AS backupset_name,description,DATEDIFF(hh, backup_finish_date,GETDATE()) AS Backup_Age FROM  (SELECT M.database_name,M.type,max(M.backup_finish_date) AS maxtime FROM msdb.dbo.backupset AS M GROUP BY M.database_name,M.type) AS maxvalue ,msdb.dbo.backupset BS INNER JOIN msdb.dbo.backupmediafamily ON BS.media_set_id = msdb.dbo.backupmediafamily.media_set_id INNER JOIN sys.databases db ON db.name=BS.database_name WHERE (CONVERT(datetime, BS.backup_start_date, 102) >= GETDATE() -100) AND backup_finish_date=maxvalue.maxtime AND BS.type=maxvalue.type AND BS.database_name=maxvalue.database_name ORDER BY backup_finish_date desc
Notes
Note:
  1. In addition to the above option, there is an option to delete backup entries from Applications Manager when its database is removed, which will delete the backup entries when its corresponding database is deleted from its SQL Server instance. 
  2. If enabled, it will delete the backup entries when the backup is not taken for more than the number of days mentioned in the Store SQL Database Backup Details option.
  3. By default, it will delete the backup entries when the backup is not taken for more than 7 days.
Info
Refer to our feature page to learn more about SQL Server monitoring in Applications Manager.


          New to M365 Manager Plus?
          New to M365 Manager Plus?
            New to RecoveryManager Plus?
            New to RecoveryManager Plus?
              New to Exchange Reporter Plus?
              New to Exchange Reporter Plus?
                New to SharePoint Manager Plus?
                New to SharePoint Manager Plus?
                  See all integrations
                  New to ADManager Plus?
                  See all integrations
                  New to ADManager Plus?

                    New to ADSelfService Plus?

                    Start your free trial
                    Start your free trial
                      Related Products