create procedure BD_VerifyExistingBackup_prc

as

SET NOCOUNT ON
DECLARE @FilePath VARCHAR(255)
DECLARE @File_Exists INT
DECLARE @DB_name sysname

DECLARE FileNameCsr CURSOR
READ_ONLY
FOR
SELECT physical_device_name, sd.name
FROM msdb..backupmediafamily bmf
INNER JOIN msdb..backupset bms ON bmf.media_set_id = bms.media_set_id
INNER JOIN master..sysdatabases sd ON bms.database_name = sd.name
AND bms.backup_start_date = (SELECT MAX(backup_start_date) FROM [msdb]..[backupset] b2
WHERE bms.database_name = b2.database_name AND b2.type = ‘D’)
WHERE sd.name NOT IN (‘master’,’tempdb’)

BEGIN TRY
OPEN FileNameCsr

FETCH NEXT FROM FileNameCsr INTO @FilePath, @DB_name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
EXEC master.dbo.xp_fileexist @FilePath, @File_Exists OUT

IF @File_Exists = 0 –0 = not found, 1 = found
PRINT ‘File Not Found: ‘ + @FilePath + ‘ — for database: ‘ + @DB_name
ELSE
PRINT ‘Backup ‘+’found for database ‘+ @DB_name+’. Path: ‘+@FilePath
END

FETCH NEXT FROM FileNameCsr INTO @FilePath, @DB_name
END

END TRY

BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

CLOSE FileNameCsr
DEALLOCATE FileNameCsr
GO

— select * from master..sysdatabases