Microsoft KB Archive/119266

= BUG: sp_MSbackup_now Fails on Scheduled Dumps to Same Device =

Article ID: 119266

Article Last Modified on 2/7/2002

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition

-



This article was previously published under Q119266



BUG# NT: 860 (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.

When scheduled backups fail in this scenario, the History Log Report run from SQL Administrator does not report any errors or information about the status of the dump.



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

 Create multiple logical dump device names that reference the same physical device name.  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: KB119266

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.