FIX: Monthly Scheduled Backup Skips Alternate Months

• Microsoft SQL Server version 4.2x

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  */ 
/* */ 
select @now = getdate()
select @dayofweek = datepart(weekday,@now)
select @monthyear = substring(convert(varchar(12),getdate()),1,12)
/* */ 
/* Create 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 */ 
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
last dump <= now */ 
/* */ 
insert into #spdumptab
select Event_id, Database_name,
Database_owner,Database_dump,Log_dump,NULL,NULL,Stat
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
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
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, stat = status from #spdumptab 

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.

Additional query words: SQL Monitor Month Windows NT

Keywords          : kbenv SSrvAdmin kbbug4.20 kbfix4.21 
Version           : 4.2
Platform          : WINDOWS 
Issue type        : 

Last Reviewed: March 18, 1999