I am pretty sure that you all take care of the backups of all your databases in your production environment. You may also have the reports that will give you hints if you have a backup that is missed for some or other reason. Today I will be sharing you the TSQL script for backup verification which will help you keep track of your backups, in case some database is missed you can mitigate it.
TSQL to get list of backup age
SELECT [Server] = @@servername, [DB_Name] = bs.database_name, [Last_Backup_Date] = MAX(bs.backup_finish_date), [BackupAge(Hours)] = DATEDIFF(hh, MAX(bs.backup_finish_date), GETDATE()) FROM msdb.dbo.backupset BS WHERE bs.type = 'D' GROUP BY bs.database_name HAVING (MAX(bs.backup_finish_date) < DATEADD(hh, - 24, GETDATE())) order by database_name
TSQL to get list of backups, backup location and backup size
select distinct [DB Name] = bs.database_name, [Backup Name] = bmf.physical_device_name, [backup_Size(MB)]= ((bs.backup_size/1024)/1024), [Backup_Type] = CASE bs.type WHEN 'D' THEN 'FULL' WHEN 'L' THEN 'LOG' WHEN 'I' THEN 'Differential' END , [Backup_Finish_Date] =bs.backup_finish_date, [Duration(minute)] = datediff(mi, bs.backup_start_date,bs.backup_finish_date) from msdb..backupmediafamily bmf join msdb..backupset bs on bs.media_set_id=bmf.media_set_id join master..sysdatabases sd on sd.name = bs.database_name where convert(varchar,bs.backup_finish_date,112) = convert (varchar(12),GETDATE(),112) and sd.status NOT IN (1024, 1040) order by bs.database_name,bs.backup_finish_date ASC
You can modify this script according to your requirement, for example if threshold value or backup age is greater than 24 hours it can page you or you can schedule a job that send you the list of all databases and its backup age.
I assume this script will help you with backup status and backup verification.
