-- *** 2006 Copyright MSSQLTips.com. All Rights Reserved. ***

declare csrBackups cursor for select [logical_device_name] from msdb..backupmediafamily
open csrBackups
	declare @backupdevice varchar(25), @verifystatement nvarchar(50)
fetch next from csrBackups into @backupdevice
while @@fetch_status = 0
	begin
		insert mssqltips..tblbackupverify (backupdevice) values(@backupdevice)
		set @verifystatement = 'restore verifyonly from ' + @backupdevice
		exec sp_executesql @verifystatement
		if @@error <> 0
		begin
			update mssqltips..tblbackupverify set status = 0 where 
			backupdevice = @backupdevice and datediff(hour, verifydatetime, 
			getdate()) = 0
		end
		else
		begin
			update mssqltips..tblbackupverify set status = 1 where 
			backupdevice = @backupdevice and datediff(hour, verifydatetime, 
			getdate()) = 0
		end
		fetch next from csrBackups into @backupdevice
	end
close csrBackups
deallocate csrBackups

exec xp_sendmail
	@recipients = 'administrator@mycompany.com',
	@subject = 'Backup file verification',
	@message = 'All of the backup files are readable except for the ones below:
			',
	@query='select backupdevice from mssqltips..tblbackupverify where datediff(day,
		verifydatetime, getdate()) = 0 and status = 0


-- *** 2006 Copyright MSSQLTips.com. All Rights Reserved. ***