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

	declare csrBackups cursor for select [name] from sys.sysdevices
	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 msdb..sp_send_dbmail
		@profile_name = 'Edgewood',
		@recipients = 'timothyrcullen@hotmail.com',
		@subject = 'Backupfile Verification Information',
		@body = 'All backup files were verified 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. ***