Three cases where Ola Hallengren's Maintenance Solution won't backup a database

We love Ola Hallengren's Maintenance Solution but you should always always double-check either the msdb backup history or the master.dbo.CommandLog table to make sure any important backup was taken. This is especially important if you trigger it manually and are relying on human input to get the parameters right.

Here are three easy to miss cases where the scripts won't backup a database. These absolutely, definitely, aren't bugs, they're idiosyncrasies with the underlying backup command and (sometimes) how the script works. But they're also much easier to miss in the verbose output of the script.

Nothing up my sleeves

First I'll create a database and do a normal Hallengren backup to confirm it's all AOK.

Create Database DontLoseMe;

Declare @Result Int;
Execute @Result = master.dbo.DatabaseBackup
		@Databases = 'DontLoseMe',
		@BackupType = 'FULL',
		@Directory = 'C:\Temp';
Print	'Hallengren result is ' + Cast(@Result As Nvarchar(11));

Date and time: 2015-12-08 22:32:33
Server: W08R2S14C1N1\AOAG
Version: 12.0.2000.8
Edition: Developer Edition (64-bit)
Procedure: [master].[dbo].[DatabaseBackup]
Parameters: @Databases = 'DontLoseMe', @Directory = 'C:\Temp', @BackupType = 'FULL', @Verify = 'N', @CleanupTime = NULL, @CleanupMode = 'AFTER_BACKUP', @Compress = NULL, @CopyOnly = 'N', @ChangeBackupType = 'N', @BackupSoftware = NULL, @CheckSum = 'N', @BlockSize = NULL, @BufferCount = NULL, @MaxTransferSize = NULL, @NumberOfFiles = NULL, @CompressionLevel = NULL, @Description = NULL, @Threads = NULL, @Throttle = NULL, @Encrypt = 'N', @EncryptionAlgorithm = NULL, @ServerCertificate = NULL, @ServerAsymmetricKey = NULL, @EncryptionKey = NULL, @ReadWriteFileGroups = 'N', @OverrideBackupPreference = 'N', @NoRecovery = 'N', @URL = NULL, @Credential = NULL, @MirrorDirectory = NULL, @MirrorCleanupTime = NULL, @MirrorCleanupMode = 'AFTER_BACKUP', @LogToTable = 'N', @Execute = 'Y'
Source: https://ola.hallengren.com
 
Date and time: 2015-12-08 22:32:33
Database: [DontLoseMe]
Status: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Is accessible: Yes
Recovery model: FULL
Differential base LSN: 33000000010000037
Last log backup LSN: 33000000005500065
 
Date and time: 2015-12-08 22:32:33
Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_create_subdir N'C:\Temp\W08R2S14C1N1$AOAG\DontLoseMe\FULL' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1)
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2015-12-08 22:32:33
 
Date and time: 2015-12-08 22:32:33
Command: BACKUP DATABASE [DontLoseMe] TO DISK = N'C:\Temp\W08R2S14C1N1$AOAG\DontLoseMe\FULL\W08R2S14C1N1$AOAG_DontLoseMe_FULL_20151208_223233.bak' WITH NO_CHECKSUM, NO_COMPRESSION
Processed 328 pages for database 'DontLoseMe', file 'DontLoseMe' on file 1.
Processed 2 pages for database 'DontLoseMe', file 'DontLoseMe_log' on file 1.
BACKUP DATABASE successfully processed 330 pages in 0.012 seconds (214.233 MB/sec).
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2015-12-08 22:32:33
 
Date and time: 2015-12-08 22:32:33
Hallengren result is 0

Case 1

I'll just run the backup again. We can trust this will just work without checking right?

Declare @Result Int;
Execute @Result = master.dbo.DatabaseBackup
		@Databases = 'DontLoseMe',
		@BackupType = 'FULL',
		@Directory = 'C:\Temp';
Print	'Hallengren result is ' + Cast(@Result As Nvarchar(11));

No! Of course not! But do you know this from looking at the screen?

You can't because it's not obvious unless you take the time to scroll down and are paying close attention.

The database was skipped because it's in Single_User mode.

But Cody, why would anyone even do this? Everyone knows you can't backup a database in Single_User mode.

But dear reader, the problem is that you may have missed it. And I also missed it the first time. It occurred during a database refresh where my procedure is to:

Take a safety backup of the existing database.
Switch to Single_User mode to quickly kick out existing users.
Restore the database With Replace from another backup.

But I had somehow mixed up the order of the first and second commands and if you do enough backups then you will eventually do the same. By comparison, if you were doing this with native backup commands, it would be immediately obvious that the backup didn't occur:

This is my point. You never know, so always check the result very carefully, and rather than eyeballing it the easiest and most reliable way to check the backup history tables.

Case 2

Okay let's redesign our weekly backup structure.

We'll set up weekly full backups.
Daily differential backups.
Hourly log backups.

But we want to make sure that when we have databases added during the week we don't get errors that a differential backup cannot be taken, we want a full backup taken in those cases.

Luckily there's the @ChangeBackupType flag documented in Ola's instructions.

So we'll use that. Here's our daily differential backup which should cover all of the system databases (for example). We can trust this will just work without checking right? I mean what could possibly go wrong?

Declare @Result Int;
Execute @Result = master.dbo.DatabaseBackup
		@Databases = 'SYSTEM_DATABASES',
		@BackupType = 'DIFF',
		@ChangeBackupType = 'Y',
		@Directory = 'C:\Temp';
Print	'Hallengren result is ' + Cast(@Result As Nvarchar(11));

No! Of course not! But before you read on, I challenge you to tell me what was missed. And if you're really brave, you can take a look through the verbose output and THEN tell me what was wrong (which is almost as hard).

Date and time: 2015-12-08 22:41:38
Server: W08R2S14C1N1\AOAG
Version: 12.0.2000.8
Edition: Developer Edition (64-bit)
Procedure: [master].[dbo].[DatabaseBackup]
Parameters: @Databases = 'SYSTEM_DATABASES', @Directory = 'C:\Temp', @BackupType = 'DIFF', @Verify = 'N', @CleanupTime = NULL, @CleanupMode = 'AFTER_BACKUP', @Compress = NULL, @CopyOnly = 'N', @ChangeBackupType = 'Y', @BackupSoftware = NULL, @CheckSum = 'N', @BlockSize = NULL, @BufferCount = NULL, @MaxTransferSize = NULL, @NumberOfFiles = NULL, @CompressionLevel = NULL, @Description = NULL, @Threads = NULL, @Throttle = NULL, @Encrypt = 'N', @EncryptionAlgorithm = NULL, @ServerCertificate = NULL, @ServerAsymmetricKey = NULL, @EncryptionKey = NULL, @ReadWriteFileGroups = 'N', @OverrideBackupPreference = 'N', @NoRecovery = 'N', @URL = NULL, @Credential = NULL, @MirrorDirectory = NULL, @MirrorCleanupTime = NULL, @MirrorCleanupMode = 'AFTER_BACKUP', @LogToTable = 'N', @Execute = 'Y'
Source: https://ola.hallengren.com
 
Date and time: 2015-12-08 22:41:38
Database: [master]
Status: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Is accessible: Yes
Recovery model: SIMPLE
Differential base LSN: N/A
Differential base is snapshot: N/A
Last log backup LSN: N/A
 
Date and time: 2015-12-08 22:41:38
Database: [model]
Status: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Is accessible: Yes
Recovery model: FULL
Differential base LSN: 32000000038700037
Differential base is snapshot: No
Last log backup LSN: 32000000038700037
 
Date and time: 2015-12-08 22:41:38
Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_create_subdir N'C:\Temp\W08R2S14C1N1$AOAG\model\DIFF' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1)
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2015-12-08 22:41:38
 
Date and time: 2015-12-08 22:41:38
Command: BACKUP DATABASE [model] TO DISK = N'C:\Temp\W08R2S14C1N1$AOAG\model\DIFF\W08R2S14C1N1$AOAG_model_DIFF_20151208_224138.bak' WITH NO_CHECKSUM, NO_COMPRESSION, DIFFERENTIAL
Processed 40 pages for database 'model', file 'modeldev' on file 1.
Processed 1 pages for database 'model', file 'modellog' on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 41 pages in 0.007 seconds (44.852 MB/sec).
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2015-12-08 22:41:38
 
Date and time: 2015-12-08 22:41:38
Database: [msdb]
Status: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Is accessible: Yes
Recovery model: SIMPLE
Differential base LSN: 80000000001600160
Differential base is snapshot: No
Last log backup LSN: N/A
 
Date and time: 2015-12-08 22:41:38
Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_create_subdir N'C:\Temp\W08R2S14C1N1$AOAG\msdb\DIFF' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1)
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2015-12-08 22:41:38
 
Date and time: 2015-12-08 22:41:38
Command: BACKUP DATABASE [msdb] TO DISK = N'C:\Temp\W08R2S14C1N1$AOAG\msdb\DIFF\W08R2S14C1N1$AOAG_msdb_DIFF_20151208_224138.bak' WITH NO_CHECKSUM, NO_COMPRESSION, DIFFERENTIAL
Processed 168 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 4 pages for database 'msdb', file 'MSDBLog' on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 172 pages in 0.008 seconds (167.724 MB/sec).
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2015-12-08 22:41:38
 
Date and time: 2015-12-08 22:41:38
Hallengren result is 0

It skipped the master database because you can't take differential backups of master. If you didn't know this then you wouldn't be alone, and it's not mentioned on Microsoft's Differential Backups page but it is mentioned on Microsoft's Create Database page.

But what about @ChangeBackupType? It should handle this!

Hallengren's script would normally handle this but has a special exclusion which simply skips master in this circumstance. Whether it should or not either way is understandable, but my only point is that you have two choices:

Be the kind of DBA that checks backup history. Find that master databases aren't being backed up. Add a special job step to do them every day.
Or come Friday, have to restore a master database, and find your last valid backup was from the weekend prior.

Case 3

This is now stretching credulity but I swear these things actually happen.

I've gone back to the DontLoseMe database I created above. I'll switch it back to Multi_User mode and I guarantee there are no tricks and that this worked as intended. I haven't manually touched anything else. I'm really going to take a backup this time. We can trust this will just work without checking right?

Alter Database DontLoseMe Set Multi_User With Rollback Immediate; 

Declare @Result Int;
Execute @Result = master.dbo.DatabaseBackup
		@Databases = 'DontLoseMe',
		@BackupType = 'FULL',
		@Directory = 'C:\Temp';
Print	'Hallengren result is ' + Cast(@Result As Nvarchar(11));

No! Of course not.

Date and time: 2015-12-08 23:01:14
Server: W08R2S14C1N2\AOAG
Version: 12.0.2000.8
Edition: Developer Edition (64-bit)
Procedure: [master].[dbo].[DatabaseBackup]
Parameters: @Databases = 'DontLoseMe', @Directory = 'C:\Temp', @BackupType = 'FULL', @Verify = 'N', @CleanupTime = NULL, @CleanupMode = 'AFTER_BACKUP', @Compress = NULL, @CopyOnly = 'N', @ChangeBackupType = 'N', @BackupSoftware = NULL, @CheckSum = 'N', @BlockSize = NULL, @BufferCount = NULL, @MaxTransferSize = NULL, @NumberOfFiles = NULL, @CompressionLevel = NULL, @Description = NULL, @Threads = NULL, @Throttle = NULL, @Encrypt = 'N', @EncryptionAlgorithm = NULL, @ServerCertificate = NULL, @ServerAsymmetricKey = NULL, @EncryptionKey = NULL, @ReadWriteFileGroups = 'N', @OverrideBackupPreference = 'N', @NoRecovery = 'N', @URL = NULL, @Credential = NULL, @MirrorDirectory = NULL, @MirrorCleanupTime = NULL, @MirrorCleanupMode = 'AFTER_BACKUP', @LogToTable = 'N', @Execute = 'Y'
Source: https://ola.hallengren.com
 
Date and time: 2015-12-08 23:01:14
Database: [DontLoseMe]
Status: ONLINE
Standby: No
Updateability: READ_ONLY
User access: MULTI_USER
Is accessible: Yes
Recovery model: FULL
Availability group: AOAG_Group
Availability group role: SECONDARY
Availability group backup preference: SECONDARY_ONLY
Is preferred backup replica: Yes
Differential base LSN: 33000000015500037
Last log backup LSN: 33000000009200001
 
Date and time: 2015-12-08 23:01:14
Hallengren result is 0

The database was automatically added to an Availability Group in the background by an agent job and now I'm on the secondary. Maybe we can just try to override the backup preference like I showed you above. We can trust this will just work without checking right?

Declare @Result Int;
Execute @Result = master.dbo.DatabaseBackup
		@Databases = 'DontLoseMe',
		@BackupType = 'FULL',
		@OverrideBackupPreference = 'Y',
		@Directory = 'C:\Temp';
Print	'Hallengren result is ' + Cast(@Result As Nvarchar(11));

No! Of course not!

As part of the design of Availability Groups you can only do CopyOnly backups on a secondary. It has nothing to do with the OverrideBackupPreference.

But most of the time when you're doing this manually you might want to specify both and cover all of your bases. But you'd never, ever, make a mistake and actually forget one or the other in whatever underlying mix of circumstances they were required in, would you?

Declare @Result Int;
Execute @Result = master.dbo.DatabaseBackup
		@Databases = 'DontLoseMe',
		@BackupType = 'FULL',
		@CopyOnly = 'Y',
		@OverrideBackupPreference = 'Y',
		@Directory = 'C:\Temp';
Print	'Hallengren result is ' + Cast(@Result As Nvarchar(11));

Date and time: 2015-12-08 23:04:05
Server: W08R2S14C1N2\AOAG
Version: 12.0.2000.8
Edition: Developer Edition (64-bit)
Procedure: [master].[dbo].[DatabaseBackup]
Parameters: @Databases = 'DontLoseMe', @Directory = 'C:\Temp', @BackupType = 'FULL', @Verify = 'N', @CleanupTime = NULL, @CleanupMode = 'AFTER_BACKUP', @Compress = NULL, @CopyOnly = 'Y', @ChangeBackupType = 'N', @BackupSoftware = NULL, @CheckSum = 'N', @BlockSize = NULL, @BufferCount = NULL, @MaxTransferSize = NULL, @NumberOfFiles = NULL, @CompressionLevel = NULL, @Description = NULL, @Threads = NULL, @Throttle = NULL, @Encrypt = 'N', @EncryptionAlgorithm = NULL, @ServerCertificate = NULL, @ServerAsymmetricKey = NULL, @EncryptionKey = NULL, @ReadWriteFileGroups = 'N', @OverrideBackupPreference = 'Y', @NoRecovery = 'N', @URL = NULL, @Credential = NULL, @MirrorDirectory = NULL, @MirrorCleanupTime = NULL, @MirrorCleanupMode = 'AFTER_BACKUP', @LogToTable = 'N', @Execute = 'Y'
Source: https://ola.hallengren.com
 
Date and time: 2015-12-08 23:04:05
Database: [DontLoseMe]
Status: ONLINE
Standby: No
Updateability: READ_ONLY
User access: MULTI_USER
Is accessible: Yes
Recovery model: FULL
Availability group: AOAG_Group
Availability group role: SECONDARY
Availability group backup preference: NONE
Is preferred backup replica: No
Differential base LSN: 33000000018300037
Last log backup LSN: 33000000009200001
 
Date and time: 2015-12-08 23:04:05
Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_create_subdir N'C:\Temp\W08R2S14C1$AOAG_Group\DontLoseMe\FULL_COPY_ONLY' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1)
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2015-12-08 23:04:05
 
Date and time: 2015-12-08 23:04:05
Command: BACKUP DATABASE [DontLoseMe] TO DISK = N'C:\Temp\W08R2S14C1$AOAG_Group\DontLoseMe\FULL_COPY_ONLY\W08R2S14C1$AOAG_Group_DontLoseMe_FULL_COPY_ONLY_20151208_230405.bak' WITH NO_CHECKSUM, NO_COMPRESSION, COPY_ONLY
Processed 328 pages for database 'DontLoseMe', file 'DontLoseMe' on file 1.
Processed 2 pages for database 'DontLoseMe', file 'DontLoseMe_log' on file 1.
BACKUP DATABASE successfully processed 330 pages in 0.016 seconds (160.949 MB/sec).
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2015-12-08 23:04:05
 
Date and time: 2015-12-08 23:04:05
Hallengren result is 0

Takeaway

You may have noticed I was capturing a result code from the backup script execute statement and displaying it was 0 each time. I'm not sure if this ever actually returns an error code but either way you shouldn't trust it to indicate that the backup you want ever occurred.

Anyway no matter how unlikely these cases may seem, if you have enough servers and do enough backups, it's extremely easy for mistakes to be made. It's always far better and far easier that:

When you run the scripts manually, have a one-liner on the end to check the backup history table to make sure your target database(s) were really backed up instead of relying on verbose output from the script.
And have daily checks on backup history across your servers to make sure databases are backed up everywhere (remembering this history can be split across multiple servers with Mirrors and Availability Groups).

Nothing up my sleeves

Case 1

Case 2

Case 3

Takeaway

Share on: