1

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.

0
1

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.

8
  • I noticed that the table commandlog only logs the commands that are actually issued, so when the script encounters an error during execution, it will write a line for that last database that generated the error, but the ones that would come after that are not logged on that table as a failed backup. – Ronaldo Mar 4 at 16:46
  • everything is logged in the commandlog table. you can identify errors looking at fileds [ErrorNumber] and [ErrorMessage]. notice that if the scirpt encounters an error at the database number 5 (for example) it continues with others databases. – MBuschi Mar 4 at 19:19
  • in my test something a bit different happened. I created two new databases with their log files located on a pen-drive that I removed later to mess those two databases and cause an error during backup. Only the first one was registered on the commandlog table, the second one was not logged, so I assumed it stopped the execution on the first error. – Ronaldo Mar 4 at 22:20
  • 1
    Usually it works as I described. I don't know what happend in your case. I suggest you to enable the step log on files and review what is noted inside. – MBuschi Mar 5 at 7:25
  • MBuschi, I didn't know about that log. Thanks. As you can see on this picture of my step log both problematic databases (OutroBackup and TesteErroBackup) appear on the log, but only the OutroBackup failure was registered on the table commandlog. – Ronaldo Mar 11 at 18:10
0

ola.hallengren doc shows some options for the database parameter and you can specify databases that you want to exclude from that execution.

Also, the system table backupset

Contains a row for each backup set. A backup set contains the backup from a single, successful backup operation.

Knowing that, you could create another job or a different job step in which you would use the following @RetryDatabases variable as the parameter for Hallengren @Databases option and run it manually:

DECLARE @RetryDatabases nvarchar(max);

;WITH exceptdbs (database_name) AS 
(
    SELECT N'ALL_DATABASES' --option for all databases

    UNION

    SELECT DISTINCT database_name
    FROM msdb.dbo.backupset
    WHERE type = 'D'
        AND is_copy_only = 0
        AND backup_start_date >= CONVERT(date, GETDATE())
)
SELECT @RetryDatabases = STRING_AGG(database_name, ', -')
FROM exceptdbs;

SELECT @RetryDatabases;

This solution is using the function STRING_AGG that is available since sql server 2017, but if you are using an older versions there are other ways to group many rows into one line.

In SQL Server 2014 one possible way is to just substitute the SELECT with the STRING_AGG for this:

SELECT @RetryDatabases = STUFF((SELECT ', -' + database_name 
                                FROM exceptdbs FOR XML PATH('')), 1, 3, '');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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