June 28, 2019 at 8:30 pm
Dear Members
I have more than 50 servers with sql server installed
each of them runs a backup job every day at night
Exists any backup tool I can check daily if all the servers have backed up successfully
Thanks
Almir
June 29, 2019 at 9:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
July 1, 2019 at 4:34 pm
You should assume all backups work and provide an alert of some sort if backups fail. You can do this with SQL Agent, or you can use something like SQL Monitor to watch your systems.
Disclosure: I work for Redgate Software.
July 1, 2019 at 11:09 pm
Dear Members I have more than 50 servers with sql server installed each of them runs a backup job every day at night Exists any backup tool I can check daily if all the servers have backed up successfully Thanks Almir
Why wouldn't the backup jobs on the various systems not be emailing failures to a "DBA Alert" of some sort?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 2, 2019 at 2:27 pm
You can use the script to run on each instance. It will show the last back-up (full, diff, log) taken of each database. You can use sp_send_dbmail to send the results of this script to your mailbox. Or setup something different to notify you of errors. There are also some (free) monitoring solutions to keep track of multiple instances...
;WITH CTE_Last_backup_list AS
(
SELECT
Last_backup.database_name
, Last_backup.type
, Last_backup.last_backup_date
, media_set_id
FROM msdb.dbo.backupset
INNER JOIN
(SELECT
backupset.database_name
, type
, max(backupset.backup_finish_date) as last_backup_date
FROM msdb..backupset
group by
backupset.database_name
, type
) Last_backup
ON backupset.database_name = Last_backup.database_name
AND backupset.type = Last_backup.type
AND backupset.backup_finish_date = Last_backup.last_backup_date
)
SELECT db.name as Database_name
, db.recovery_model_desc as Recovery_Model
, bs_full.last_backup_date as last_FULL_backup
, media_full.physical_device_name as FULL_backup_file
, bs_diff.last_backup_date as last_DIFF_backup
, media_diff.physical_device_name as DIFF_backup_file
, case when most_recent.backup_date > dateadd(hour, -25, getdate())
then 'recent'
else 'outdated'
end as 'FULL/DIFF status'
, bs_log.last_backup_date as last_LOG_backup
, media_log.physical_device_name as LOG_backup_file
, case when db.recovery_model <> 3
then case when drs.last_log_backup_lsn is null or bs_log.last_backup_date is null
then 'BROKEN'
else 'Ok'
end
else 'n/a'
end as LOG_chain
, case when bs_log.last_backup_date IS NULL
then NULL
else
case when bs_log.last_backup_date > dateadd(hour, -2, getdate())
then 'recent'
else 'outdated'
end
end as 'LOG status'
FROM sys.databases db
INNER JOIN [sys].[database_recovery_status] drs
ON db.database_id = drs.database_id
LEFT OUTER JOIN CTE_Last_backup_list as bs_full
ON db.name = bs_full.database_name
AND bs_full.type = 'D'
LEFT OUTER JOIN msdb.dbo.backupmediafamily media_full
ON bs_full.media_set_id = media_full.media_set_id
LEFT OUTER JOIN CTE_Last_backup_list as bs_diff
ON db.name = bs_diff.database_name
AND bs_diff.type = 'I'
LEFT OUTER JOIN msdb.dbo.backupmediafamily media_diff
ON bs_diff.media_set_id = media_diff.media_set_id
LEFT OUTER JOIN CTE_Last_backup_list as bs_log
ON db.name = bs_log.database_name
AND bs_log.type = 'L'
LEFT OUTER JOIN msdb.dbo.backupmediafamily media_log
ON bs_log.media_set_id = media_log.media_set_id
CROSS APPLY (select max(full_diff.last_date) as backup_date
from
(select bs_full.last_backup_date as last_date
union all
select bs_diff.last_backup_date
) full_diff
) most_recent
WHERE db.name <> 'tempdb'
ORDER BY
db.recovery_model_desc
, db.name
--, most_recent.backup_date desc
--, last_FULL_backup desc
--, last_DIFF_backup desc
--, last_LOG_backup desc
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply