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

[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 'I' THEN 'Differential'
[Backup_Finish_Date] =bs.backup_finish_date,
[Duration(minute)] = datediff(mi, bs.backup_start_date,bs.backup_finish_date)
msdb..backupmediafamily bmf
msdb..backupset bs
master..sysdatabases sd
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

TSQL Script for Backup Verification






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.

Leave a Reply

Your email address will not be published.

Hemantgiri Goswami

Hemantgiri Goswami

Hemantgiri is a seasoned SQL Server Consultant with demonstrated history for close to 21 years. He is a published author specializing in High Availability and Disaster Recovery area. He was awarded Most Valuable Professional by Microsoft 4 times. He is a regular speaker at events in Surat. Hemantgiri is founder and leader of the SQLPASS Chapter for Surat.