verify_dump

IF EXISTS (SELECT * FROM sysobjects 
           WHERE id = OBJECT_ID('dbo.verify_dump') 
           AND sysstat & 0xf = 4)
	DROP PROCEDURE dbo.verify_dump
GO

CREATE PROCEDURE verify_dump
        @source VARCHAR(30) = ' ',
        @device VARCHAR(80) = ' ',
        @found INT OUTPUT, 
	@debug VARCHAR(5) = 'false'
AS
DECLARE @CMDstatus INT,
        @timeWindowStart VARCHAR(20), 
 	@myName VARCHAR(30)
/*
   Procedure verify_dump uses the remote procedure 
   capability of SQL Server 6.5 to verify that a 
   dump has been recorded in the msdb database.
   There is an attempt to identify possible duplicate
   dumps. The server names are hard coded here for 
   proper operation of the remote stored procedure.  
*/

SET NOCOUNT ON
SELECT @myName = 'admin.verify_dump'

IF (@source = ' ') OR (@device = ' ') 
  RETURN 1

IF @debug = 'true'
  BEGIN
    SELECT '@myName: ', @myName 
    SELECT '@source: ', @source
    SELECT '@device: ', @device
  END  	 	

IF @source = @@SERVERNAME
  BEGIN
    /* already dumped in last 30 or so minutes? */
    SELECT @timeWindowStart = DATEADD(minute, -40, GETDATE())
    SELECT @found = (SELECT COUNT(d.backup_id)
                     FROM msdb..sysbackupdetail d, 
                          msdb..sysbackuphistory h 
                     WHERE d.device_name = @device 
                     AND d.backup_id = h.backup_id 
                     AND h.backup_finish > @timeWindowStart)
    RETURN
  END 
IF (@source = 'hot_site') 
  EXEC hot_site.admin.dbo.verify_dump @source,
                                      @device,
                                      @found OUTPUT, 
                                      @debug
IF (@source = 'prod1') 
  EXEC prod1.admin.dbo.verify_dump @source,
                                   @device,
                                   @found OUTPUT, 
                                   @debug
IF (@source = 'prod2') 
  EXEC prod2.admin.dbo.verify_dump @source,
                                   @device,
                                   @found OUTPUT,
                                   @debug
GO