Sql-server – Ola Hallengren scripts: after a job failure, how to skip the databases that worked and backup only the remaining ones

backupola-hallengrensql serversql server 2014

In our organization we have more than 150 databases in one instance and full backup should run for every day.

Our backup script should be in such a way that if backup job fails while taking 100th database and if I rerun the job again, it should start from the failed database(100th) and exclude the backups which are already taken and the script should take the backup of the remaining databases which are not backuped up for the day. I think the current backup script won't serve this purpose.

Best Answer

Usually when one or more backups fails, you have to do some kind of manual check. Check the log, fix the problem and the rerun the backup.

Focusing on the last step, I suggest you to collect the database list of failed backups from the commandlog table populated by ola scripts. Then you should use it as @databases parameter for the ola script.

In an daily backup jobs, you can use the ALL_DATABASES standard ola parameter as step 1 and the job ends there on success. You can have a step2 as recovery step with the dynamically generated database list. You run it only to recover missing backups.