Microsoft KB Archive/99113

{|
 * width="100%"|

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

 * }

Q99113

-

The information in this article applies to:


 * Microsoft SQL Server for OS/2, version 4.2

-

BUG# 8517 (4.20)

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 /S /i

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

if exists (select * from sysobjects where name = &quot;sp_MSbackup_now&quot;   and sysstat & 7 = 4)

begin drop procedure sp_MSbackup_now end go

print &quot;&quot; print &quot;Creating sp_MSbackup_now&quot; print &quot;&quot; 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 */ /* 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 */ /* */

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 */ /* 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) &gt;= convert(smallint,Frequency)*24 /* Freq time has elapsed */ and datediff(minute, Last_dump, @now) >= convert(smallint,Frequency)*24*60

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)

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

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

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 go

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

dump tran master with truncate_only go

/************* END DUMP THE TRANSACTION LOG ************************/ Additional query words: scheduled backup SQL Administrator Monitor dblib

Keywords : kbprogramming

Issue type :

Technology : kbSQLServSearch kbAudDeveloper kbZNotKeyword3 kbSQLServ420OS2