FIX: Scheduled Backups Stop Working After 4/14/93

  - Microsoft SQL Server for OS/2, version 4.2

SYMPTOMS

SQL Administrator scheduled backups will fail when the date is April 14, 1993, or later. Dump files are not produced and an error is not displayed.

CAUSE

When you add or modify a scheduled backup event using SQL Administrator, SQL Monitor uses the hard-coded date 8/1/92 for the Last_dump field of the MSscheduled_backups table. The sp_MSbackup_now stored procedure is executed once by SQL Monitor to determine which scheduled backups need to occur. If any backup events have this 8/1/92 date as the Last_dump, and the date is 4/14/93 or later, the sp_MSbackup_now procedure fails and no scheduled backups occur.

WORKAROUND

Issue the following query from the MASTER database after any changes or additions have been made to the list of scheduled backup events. It updates the Last_dump column to yesterday, thus allowing the sp_MSbackup_now stored procedure to complete.

   update MSscheduled_backups
   set Last_dump = dateadd (day, -1, getdate())
   where datepart (year, Last_dump) = 1992

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2a. This problem is corrected in SQL Server for OS/2 version 4.2b. You can also correct the problem by running the replacement script below. Simply copy this script to a file, and issue the following command:

   isql /Usa /P<password> /S<server> /i<file>

/*******************************************************************/
/* This stored procedure will get all the databases that need to   */
/* be dumped at the time of inquiry                                */
/*******************************************************************/

if exists (select * from sysobjects where name = "sp_MSbackup_now"

    and sysstat & 7 = 4)

    drop procedure sp_MSbackup_now

print "" print "Creating sp_MSbackup_now" print "" go create procedure sp_MSbackup_now as declare @now datetime, @dayofweek smallint, @hour smallint,

    @minute smallint,

set nocount on

/* */
/* Get the current date and time. */
/* Parse out the relevant parts of the date */
/* */

/* */
/* Create a 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 */
)

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

    Log_dump,NULL,NULL

where Enabled = 1                        /* Dump turned on */
    /* Dump today or Daily */
    and ((convert(smallint,Day) = @dayofweek) or Frequency = 1)
    /* Freq daily, weekly, or biweekly */
    and Frequency <= 14
    /* Freq time has elapsed */
    and datediff(day, Last_dump, @now)
        >= convert(smallint,Frequency)
    and @now >= convert(datetime, @monthyear + Start_time)
    /* Freq time has elapsed */
    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

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

    @weekno smallint     /* Get this week number */

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

insert into #spdumptab select Event_id, Database_name, Database_owner,Database_dump,

    Log_dump,NULL,NULL

where Enabled = 1                       /* Dump turned on */
    /* Dump today */
    and (convert(smallint,Day) = @dayofweek)
    and Frequency >= 31                 /* Freq monthly */
    /* Week of month */
    and (convert(smallint,Frequency) - 30) = @weekno
    /* Freq time has elapsed */
    and datediff(day, Last_dump, @now) >= 30
    and @now >= convert(datetime, @monthyear + Start_time)
    /* Freq time has elapsed */
    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

    = (select cntrltype from master..sysdevices s
    where #spdumptab.ddump = s.name)

    = (select cntrltype from master..sysdevices s
    where #spdumptab.ldump = s.name)

    = (select 5 from MSscheduled_backups s
    where #spdumptab.id = s.Event_id and s.Database_dump like '%+')

    = (select 5 from MSscheduled_backups s
    where #spdumptab.id = s.Event_id and s.Log_dump like '%+')

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

    ldump = ldump, dcntrl = datacntrltype, lcntrl = logcntrltype
    from #spdumptab

/************* DUMP THE TRANSACTION LOG ****************************/
/* Comment this out if you don't want your log dumped.  If you     */
/* rerun this script periodically, you will run out of             */
/* transaction log space.                                          */

/************* END DUMP THE TRANSACTION LOG ************************/