4

Is there a way to do an automatic backup re-try utilizing Ola Hallengren's back up scripts for just one database back up if there is a failure? Looking for a way to complete the back up without re-running the whole script.

1

2 Answers 2

3

Unfortunately the script itself does not have a built in path to retry a single DB for a backup if it failed. You can view the entire script options here on Ola's Site.

What I would recommend (albeit it may be a little painful) is to have a separate job step for each DB and specify the DB you want to backup. Then in each step you can add the retry options.

You could probably start by creating one or two, and then hit the script button in the SQL Agent. From there you can copy and paste the sections of SQL that create each subsequent job step and make a few alterations rather than manually modifying the job steps through the GUI.

From there I would begin evaluating whether Ola's scripts are meeting my organizations needs and I may start to look to other backup options like DBATools or Minionware or custom backup scripts. You could also alter the Ola scripts but this will be significantly more difficult. There are also paid tools that may be worth evaluating.

3

You can query the dbo.CommandLog table for a successful backup (since you are using Ola's backup solution) or even msdb history to double check that the backup is indeed successful or not.

Based on that, if the backup failed, then you can retry the backup.

There was an issue already filed (it was for backup to Azure URL- but MS released a hot fix for handling intermittent failures - so the issue is closed), but the problem with handling server side errors is that using error variable - it will give you the last error only.

Also, refer to Three cases where Ola Hallengren's Maintenance Solution won't backup a database

Now if you use dbatools (which I highly recommend)

  • Still continue using Ola's solution. You should use Invoke-DbaQuery and in query use -Query 'EXECUTE dbo.DatabaseBackup ..' along with -EnableException SomeExceptionErrorMessage switch. Then you can just use the variable SomeExceptionErrorMessage to check for errors or warnings and implement a retry logic.
  • Switch to use dbatools native - Backup-DbaDatabase with -EnableException SomeExceptionErrorMessage switch.

Alternatively, you can just add an extra step in your sql agent job to check for the commandLog or msdb for successful backup and based on that you can call your backup job again.

2
  • If you are querying the commandlog or MSDB to look for a failed backup to kick the job off again, wouldn't that force a retry of backing up every DB that was originally specified vs a single DB? I'm curious about if DBATools has a similar approach, but I don't know enough about the commands and its integration with Ola to know for sure. Commented Jan 23, 2019 at 15:53
  • 1
    If using Ola's script - yes it would. Else you can use dynamic sql to construct the backup command for the failed db backups. DBATools if you go with backup-dbadatabase and based on the exception, you can do a retry. This is what I am doing in my current env. managing 4K+ dbs. Also, the good thing about using Powershell based dbatools is that you can do multithreading which I blogged about.
    – Kin Shah
    Commented Jan 23, 2019 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.