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