BUG: sp_MSbackup_now Fails on Scheduled Dumps to Same Device

• Microsoft SQL Server version 4.2x

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.

Additional query words: Windows NT

Keywords          : kbtool SSrvAdmin SSrvStProc kbbug4.21 
Version           : 4.21.006
Platform          : WINDOWS 
Issue type        : 

Last Reviewed: March 20, 1999