BUG: sp_MSbackup_now Fails on Scheduled Dumps to Same Device

  - Microsoft SQL Server, version 4.21

SYMPTOMS

When more than one scheduled event exists to dump to the same logical device name, the SQL Monitor backup stored procedure sp_MSbackup_now may fail with Msg 512:

   Subquery returned more than 1 value. This is illegal when
   the subquery follows =, !=, <, <=, >, >=, or when the
   subquery is used as an expression.

WORKAROUND

The following two options can be used to avoid this problem:

1. Create multiple logical dump device names that reference the same physical device name.

2. Execute the following script to replace the existing sp_MSbackup_now procedure:

   use master go if exists (select name from sysobjects where name = 'sp_MSbackup_now') drop procedure sp_MSbackup_now go create procedure sp_MSbackup_now as declare @now datetime, @dayofweek smallint, @hour smallint, @minute smallint, @elapsed int, @monthyear varchar(30) set nocount on

      /* */
      /* Get the current date and time. */
      /* Parse out the relevant parts of the date */
      /* */
      select @now = getdate()
      select @dayofweek = datepart(weekday,@now)
      select @monthyear = substring(convert(varchar(12),getdate()),1,12)
   
      /* */
      /* Create a temporary table to hold data on what needs to be dumped */
      /* */
      create table #spdumptab
      (id           int         Not Null,      /* Unique identifier */
      name          varchar(30)  Not Null, /* Name of database to be dumped */
      owner         varchar(30)  Not Null, /* Name of the database owner */
      ddump          varchar(30)  Null,        /* Database dump device */
      ldump          varchar(30)  Null,        /* Log dump device */
      datacntrltype  smallint     Null,  /* Control type */
      logcntrltype   smallint     Null,  /* Control type */
      status       tinyint      Null,     /* extra dump parameters */
      trys         tinyint      Null,     /* number of attempts to dump */
      emailname    varchar(60)  Null,/* email recipient(s) for notification */
      dumptime     varchar(32)  Not Null,   /* scheduled event time */
      day            tinyint      Not Null,   /* day of week for dump */
      freq           tinyint      Not Null    /* frequency of dump */
      )
   
      /* */
      /* Check all databases that are dumped daily, weekly, and biweekly */
      /* Note: The dump can only occur if the start time(HH:MM) is > the
      last dump <= now */
      /* */
      insert into #spdumptab
      select Event_id, Database_name,
      Database_owner,Database_dump,Log_dump,NULL,NULL,Stat,attempts,
      email_name,@monthyear+Start_time,Day,Frequency
      from MSscheduled_backups
      where Enabled = 1         /* Dump turned on */
      and ((convert(smallint,Day) = @dayofweek) or Frequency = 1)
                         /* Dump today or Daily */
      and Frequency <= 14         /* Freq daily, weekly, or biweekly */
      and datediff(day, Last_dump, @now) >= convert(smallint,Frequency)
                         /* Freq time has elapsed */
      and @now >= convert(datetime, @monthyear + Start_time)
      and datediff(hour, Last_dump, @now) >=convert(smallint,Frequency)*24
            /* Freq time has elapsed */
      and datediff(minute, Last_dump, @now) >=
      convert(smallint,Frequency)*24*60         /* Freq time has elapsed */
   
      /* */
      /* Check all the databases that are dumped monthly */
      /* Note: First we get this week number, then do the same criteria as
      the Daily, weekly, bi-weekly dump.The dump can only occur if the
      start time(HH:MM) is > the last dump <= now */
      /* */
      declare @rundate datetime, @weekno smallint  /* Get this week number */
      select @rundate = @now
      select @weekno = 1
      while datepart(month,dateadd(day,-7,@rundate)) = datepart(month,@now)
      begin
      select @weekno = @weekno + 1
      select @rundate = dateadd(day,-7,@rundate)
      end
   
      insert into #spdumptab
      select Event_id, Database_name,
      Database_owner,Database_dump,Log_dump,NULL,NULL,Stat,attempts,
      email_name, @monthyear+Start_time,Day,Frequency
      from MSscheduled_backups
      where Enabled = 1                           /* Dump turned on */
      and (convert(smallint,Day) = @dayofweek)     /* Dump today */
      and Frequency >= 31                    /* Freq monthly */
      and (convert(smallint,Frequency) - 22) >= @weekno  /* Week of month */
      and datediff(day, Last_dump, @now) >= 22  /* Freq time has elapsed */
      and @now >= convert(datetime, @monthyear + Start_time)
      and datediff(hour, Last_dump, @now) >= 22*24
                     /* Freq time has elapsed */
      and datediff(minute, Last_dump, @now) >= 22*24*60
                         /* Freq time has elapsed */
   
      update #spdumptab set datacntrltype = (select distinct cntrltype
           from master..sysdevices s where #spdumptab.ddump = s.name)
      update #spdumptab set logcntrltype = (select distinct cntrltype
           from master..sysdevices s where #spdumptab.ldump = s.name)
   
      set nocount off
      /* */
      /* Output the values to the daemon */
      /* */
      select id = id, name = name, owner = owner, ddump = ddump,
      ldump = ldump, dcntrl = datacntrltype, lcntrl = logcntrltype,
      stat = status, attempt=trys, email=emailname,
         dumptime=convert(varchar(32),
      convert(datetime,dumptime)),day,freq
      from #spdumptab
      order by (convert(datetime,dumptime))
      go
   
   

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.21.006. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.