Microsoft KB Archive/107710

= FIX: Monthly Scheduled Backup Skips Alternate Months =

Article ID: 107710

Article Last Modified on 3/26/2000

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition

-



This article was previously published under Q107710



BUG# NT: 9613 (4.20)



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 update #spdumptab set logcntrltype = (select cntrltype from master..sysdevices s where 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
 * 1) spdumptab.ddump = s.name)
 * 1) spdumptab.ldump = s.name)



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: kbbug kbfix kbenv KB107710

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.