June 4, 2003 at 8:48 am
Hi!
I have a job that runs a procedure that backs up my databases. If the backup of particular database is successfull it updates the LastBackupDate field for this database in a user table.
If backup of some databases fails, then the LastBackupDate for these databases remains old.
This job is sheduled to run each night.
I want to modify the procedure to make the job start again in 30 minutes if LastBackupDate field for any database is not today. I do not want to use any additional triggers, jobs, etc... I just want to insert some code into the procedure that makes this.
This is what I found:
1. use sp_add_jobschedule to create a one time run shedule to run the job. (need to clear such shedules after that with sp_delete_jobschedule)
2. create another job (a temp copy of original job) with sp_add_job. Set delete_level
to 3 (the job is executed only once, regardless of any schedules defined for the job. Furthermore, if a job deletes itself, all history for the job is also deleted.)
3. not shure about this: to update the next_run_date in table sysjobschedules,
Will the original shedule work after this???
Any other ideas?
June 4, 2003 at 9:19 am
Within the actual job, if you open up the step and select the Advanced tab, there is facility for the step to retry. You can set the number of retry attempts and the retry interval (in minutes).
This only works if the step actually fails.
Jeremy
June 4, 2003 at 9:59 pm
Yes u r right u can do so buy updateing the next_run_date and next_run_time fikled, but be cautious as these filds in the tables are int, now dont ask y SQL has made the next_run_date and next_run_time as int :)) It sounds funny when i also saw for the first time.
June 5, 2003 at 12:16 am
2Jeremy Kemp:
The procedure I call from the job does a number of backups in a cycle:
set @backup_command = 'BACKUP DATABASE ' + @db_name + ' TO DISK=''' + @full_backup_path + @db_name + '\' + @db_name + '.BAK''' + ' with INIT'
exec(@backup_command)
-- if backup is successfull, it updates the LastBackup field in a user table
IF @@ERROR = 0
UPDATE MANAGE.dbo.BackupDatabases
SET LastBackup=GetDate()
WHERE dbname=@db_name
Will the step fail, if for some DBs backup fails?
I also thought about placing
While ...
if <condition:LastBackup not updated>
WAITFOR DELAY '00:30:00'
In this case I do not need to restart job, I just dont finish the procedure and is able to make different intervals, like: 5, 10, 20, 30, 60 minutes for each attempt.
2jaybmehta: Did you try this? Kind of risk, but seems interesting..
June 5, 2003 at 2:37 pm
What about setting up the backup job to run every hour starting at midnight and running to whatever time.
Then have the job check your BackupDatabases table for which databases need to be backed up. see code below:
DECLARE @ICTint,
@iMaxCTint,
@vcDBNamevarchar(60),
@vcBackupCommandvarchar(500)
SET @ICT = 1
CREATE TABLE #TmpTable (
iLineId int IDENTITY(1,1) PRIMARY KEY,
vcDBName varchar(60))
INSERT INTO #TmpTable (vcDBName)
SELECT DBName
FROM Manage.dbo.BackupDatabases
WHERE LastBackup < DATEADD(hour, -23, GETDATE())
SELECT @iMaxCT = MAX(iLineID)
FROM #TmpTable
IF @iMaxCT = 0 OR @iMaxCT IS NULL
RETURN
WHILE @ICT <= @iMaxCT
BEGIN
SELECT @vcDBName = vcDBName
FROM #TmpTable
WHERE iLineId = @ICT
SET @vcBackupCommand = <create backup statement here>
EXEC (@vcBackupCommand)
IF @@ERROR = 0
<update lastbackup field for the database>
CONTINUE
END
DROP TABLE #TmpTable
June 6, 2003 at 5:33 am
This is what first came to mind, I was trying to find more...
June 6, 2003 at 8:42 am
What I have found that works the best is to keep it as simple as possible. The next person that does our job may not understand what we have developed and would have trouble maintaining it. For this reason, I find that the best solution is generally the simple, straight forward solution. Sometimes this is not the best or the most elegant solution but it works and is the most maintainable.
June 6, 2003 at 9:06 am
I believe that "WAITFOR DELAY '00:30:00'" in cycle (if any backup troubles) is very simple too, but is it reliyble?
June 6, 2003 at 10:58 am
couple of my backups have had troubles because of our network being to busy. I put a 15 minute delay on the re-try and to re-try 5 times. I have not had any problems with them since then.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply