SET NOCOUNT ON

DECLARE @dbname VARCHAR(128)

DECLARE @finishdate DATETIME

DECLARE @diskbackup NVARCHAR(260)

DECLARE @verifystatement NVARCHAR(360)

DECLARE @timebegin DATETIME

DECLARE @timeend DATETIME

DECLARE @timeelapsed INT

DECLARE @FileFound INT

 

DECLARE csrDatabases CURSOR FOR

SELECT database_name, MAX(backup_finish_date)

FROM msdb.dbo.backupset

WHERE type = 'D'

GROUP BY database_name

 

OPEN csrDatabases

 

FETCH NEXT FROM csrDatabases INTO @dbname, @finishdate

 

WHILE @@FETCH_STATUS = 0

BEGIN

        SELECT @diskbackup = bmf.physical_device_name

        FROM msdb.dbo.backupmediafamily bmf JOIN msdb.dbo.backupset bs

        ON (bmf.media_set_id = bs.media_set_id)

        WHERE bs.type = 'D'

        AND bs.database_name = @dbname

        AND bs.backup_finish_date = @finishdate

      

        EXEC master.dbo.xp_fileexist @diskbackup, @FileFound OUTPUT

        IF @FileFound=1

        BEGIN

               SET @verifystatement = 'RESTORE VERIFYONLY FROM DISK = '''+@diskbackup+''' WITH LOADHISTORY'

               SELECT @timebegin = GETDATE()

               EXEC sp_executesql @verifystatement

               SELECT @timeend = GETDATE()

               SELECT @timeelapsed = DATEDIFF(s,@timebegin,@timeend);

               

               IF @@ERROR <> 0

                            PRINT 'Backup file '+@diskbackup+' verified in '+CAST(@timeelapsed AS VARCHAR(20))+' seconds.'

        END

 

        FETCH NEXT FROM csrDatabases INTO @dbname, @finishdate

END

      

CLOSE csrDatabases

DEALLOCATE csrDatabases

SET NOCOUNT OFF

 _________________________________________________________________