TSQL Scripts for backup verification

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.

Hemantgiri Goswami

Throughout my extensive 24-year tenure in the IT industry, I have honed my expertise in SQL Server and cloud technologies. My qualifications include certifications in ITIL, Azure, and Google Cloud, and my professional journey boasts a consistent record of delivering top-notch, dependable, and efficient solutions across diverse clients and domains. In recognition of my dedication and impact, I am honored to have received the Microsoft MVP award for SQL Server on six occasions. Additionally, I actively contribute to various online forums and blogs, acting as a moderator and facilitator of meaningful discussions. My ultimate mission revolves around empowering organizations to enhance the reliability and efficiency of their SQL Server implementations while fostering a culture of continuous learning and growth within the SQL Server community.