FIX: Monthly Scheduled Backup Skips Alternate Months

Last reviewed: April 29, 1997
Article ID: Q114523

The information in this article applies to:

  - Microsoft SQL Server version 4.2 for OS/2

BUG# 9613 (4.2)

SYMPTOMS

When a backup event is scheduled to occur monthly, the backup event may not occur every alternate month. For example, assume a monthly backup event scheduled on Oct. 26, 93 to occur on Tuesdays of the fourth week of every month (Tuesday monthly-week4). The next backup for that event will not occur on Nov. 23, 93. Instead, it will occur on Dec. 28, 93.

CAUSE

The stored procedure sp_MSbackup_now incorrectly calculates the time elapsed for the monthly backup event.

WORKAROUND

You can drop and recreate the stored procedure sp_MSbackup_now by running the following script using any query tool, such as ISQL or ISQL/w.

On SQL Server for OS/2:

use master go drop proc 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 that holds 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 */
)
/* */
/* Check all the 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 from MSscheduled_backups where Enabled = 1

/* Dump turned on */
      and ((convert(smallint,Day) = @dayofweek) or Frequency = 1)
/* Dump to  day 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
 DDaily, 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 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) - 30) = @weekno
     /* Week of month */
      and datediff(day, Last_dump, @now) >= 28  /* Freq time has elapsed */
      and @now >= convert(datetime, @monthyear + Start_time)
      and datediff(hour, Last_dump, @now) >= convert(smallint,28)*24
  /* Freq time has elapsed */
      and datediff(minute, Last_dump, @now) >=
convert(smallint,28)*24*60    /* Freq time has elapsed */

update #spdumptab set datacntrltype = (select cntrltype from master..sysdevices s where #spdumptab.ddump = s.name) update #spdumptab set logcntrltype = (select 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 from #spdumptab

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2 for OS/2. This problem was corrected in SQL Server version 4.21 for Windows NT. For more information, contact your primary support provider.

Additional query words: SQL Monitor Month
Keywords : kbbug4.20 kbfix4.21 kbtool kbusage SSrvAdmin SSrvStProc
Version : 4.2
Platform : OS/2

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 29, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.