FIX: Scheduled Backups Stop Working After 4/14/93ID: Q99113
|
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.
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.
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
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 */
/*******************************************************************/
use master
go
if exists (select * from sysobjects where name = "sp_MSbackup_now"
and sysstat & 7 = 4)
begin
drop procedure sp_MSbackup_now
end
go
print ""
print "Creating sp_MSbackup_now"
print ""
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)
>= 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 : kbprg SSrvAdmin SSrvDB_Lib kbbug4.20a
Version : OS/2:4.2
Platform : OS/2
Issue type : Last Reviewed: July 12, 1999