FIX: Monthly Scheduled Backup Skips Alternate Months

  - Microsoft SQL Server, version 4.2

SYMPTOMS

When you schedule a backup event to occur monthly, the backup event may not occur every alternate month. For example, assume a monthly backup event is scheduled on October 26, 1993, to occur on Tuesday of the fourth week every month (Tuesday monthly-week4). The next backup for that event will not occur on November 23, 1993; instead, it will occur on December 28, 1993.

CAUSE

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

WORKAROUND

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 Windows NT:

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  */
/* */

/* */
/* Create temporary table that holds data on what needs to be dumped */
/* */

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 */
)
/* */
/* Check all the databases that are dumped daily, weekly, & biweekly */
/* Note: The dump can only occur if the start time(HH:MM) is > the

/* */

/* 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

/* */
declare @rundate datetime, @weekno smallint     /* Get this week number */

   select @weekno = @weekno + 1
   select @rundate = dateadd(day,-7,@rundate)

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 */

/* */
/* Output the values to the daemon */
/* */

STATUS

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