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

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 comment