FIX: Monthly Scheduled Backup Skips Alternate Months

• Microsoft SQL Server for OS/2, version 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          : kbtool kbusage SSrvAdmin SSrvStProc kbbug4.20 kbfix4.21 
Version           : 4.2
Platform          : OS/2 
Issue type        : 

Last Reviewed: March 19, 1999