Automatically Take Missed Backups with Ola Hallengren’s Scripts

I blogged a few years ago about my configuration tables for Ola Hallengren’s maintenance solution that allows me to pull the configuration parameters from tables so I don’t have to edit SQL Agent jobs and allows me to query tables to see how all my jobs are configured with Ola’s maintenance solution.  I’ve updated these as I’ve implemented them at my new job.  But I was talking to another DBA about another trick I have implemented that I didn’t originally publish and it’s how I make sure I get a full or differential backup for all databases even when things fail without a bunch of manual intervention.

With Ola’s script if you default to passing in USER_DATABASES for your @Databases parameter you can query the msdb for your time period, in my case a week (168 hours), all the databases that have been backed up and return those with a minus sign concatenated together so you can exclude those from being backed up, and append those to @Databases parameter where it has USER_DATABASES already using the below code with the magic of XML and the STUFF command and pass that back to the DatabaseBackup procedure:

DECLARE @ExcludeDatabases NVARCHAR(MAX) = ', ',
   @Databases NVARCHAR(MAX) = 'USER_DATABASES',
   @BackupType NVARCHAR(MAX) = 'FULL'; --'DIFF'
SELECT @ExcludeDatabases = STUFF((
    SELECT CASE
        WHEN (ag.role = N'PRIMARY' AND ag.ag_status = N'READ_WRITE') OR ag.role IS NULL THEN ', -' + d.name
        ELSE ''
   END
FROM (SELECT d.name
   , d.state
   , d.is_in_standby 
FROM sys.databases d
    INNER JOIN
    (
        SELECT DISTINCT database_name
        FROM msdb.dbo.backupset
        WHERE backup_finish_date >= 
             CASE WHEN @BackupType = 'FULL' THEN DATEADD(HOUR, -168, GETDATE())
                  WHEN @BackupType = 'DIFF' THEN DATEADD(HOUR, -24, GETDATE())
             END
           AND type = 
             CASE WHEN @BackupType = 'FULL' THEN 'D'
                  WHEN @BackupType = 'DIFF' THEN 'I'
             END
           AND is_copy_only = 0
    ) bs ON d.name = bs.database_name) d
    OUTER APPLY
        (
        SELECT role = s.role_desc
            , ag_status = DATABASEPROPERTYEX(c.database_name, N'Updateability')
        FROM sys.dm_hadr_availability_replica_states AS s
            INNER JOIN sys.availability_databases_cluster AS c ON s.group_id = c.group_id AND d.name = c.database_name
        WHERE s.is_local = 1
        ) AS ag
WHERE d.[state] = 0
    AND d.is_in_standby = 0
ORDER BY d.name
FOR XML PATH(''), TYPE).value('text()[1]','NVARCHAR(max)'), 1, LEN(','), '');
SELECT @Databases = @Databases + 
   CASE 
      WHEN LEN(ISNULL(@ExcludeDatabases, '')) > 0 THEN  ',' + @ExcludeDatabases
      ELSE ''
   END
EXEC dbo.DatabaseBackup @Databases = @Databases
   , @BackupType = @BackupType
   , ....;

I’ve included the above code as is so you can implement it in your existing scripts without using the configuration tables.  The above code specifies the @BackupType as FULL so if you want to use this for your differentials just change that at the top to DIFF. Now you can set your DIFF and FULL backups to run every hour and forget it.  If a server restarts during your backups no problem, it will get picked back up in the next hour.  If you are concerned about a performance hit during certain hours adjust your schedule to only backup during the hours, you want it to run. Note also if you have AGs and you failover you will end up with double backups for the databases in an AG again something I’m willing to accept to make sure I have backups everywhere.

I’ve uploaded new code on GitHub that contains the updated tables and jobs without this code and jobs without this code.

But no missed backups because something blew up or the server restarted, or you ran out of disk space and have to manually run backups.  Just fix the situation and it will automatically recover for you.  Yes, this means all your backups aren’t occurring at the same time anymore, but you don’t really need them to accept under rare conditions, we are pros and can work off any set of backups.  Yes, you still might need to troubleshoot why backups are failing.  But if you have a server with dozens or hundreds of databases this keeps you from duplicating backups and manual intervention or missing your SLAs if you don’t notice you didn’t get your backup or consuming extra disk space for large databases.

Of course, if you want you could run this manually when you have missed backups but who really wants to do that, not me?

The post Automatically Take Missed Backups with Ola Hallengren’s Scripts first appeared on Tracy Boggiano's Blog.