This procedure is used to change the reporting thresholds for backup reporting. It isn’t used automatically anywhere in the system, but it’s syntax is created and presented to the user from the Alert.Backups procedure, and therefore sent out in backup alerts to make it easier to use.
The use case for this procedure is simple; you get a backup alert and you decide that the current threshold of 24hrs isn’t correct so you want to change it to 28hrs (for example).
Parameters:
@ServerName – The server name, not the InstanceID, of the server you want to change the threshold for.
@DBName – The name of the DB you want to change. This parameter has some nuance though. It’s not viable to expect someone to run this procedure several times if there are 100 DBs to change. Therefore, to make mass changes easy, there are two extra parameter values: All, and MinionDefault. ‘All’ means change all the custom values that are currently in the table for this current DB (including the MinionDefault row if it exists). ‘MinionDefault’ means set a global default for the server itself.
@BackupType – This procedure can only run for a single backup type at a time. Valid options are Full|Diff|Log.
@Threshold – The threshold value you want to assign to the backup type for the current server. The metric for this value is in hours for Full and Diff, and in minutes for Log.
MinionDefault
If you create a DB-level override for a server, you should also create a MinionDefault row for that server. While it is true that having no MinionDefault for a server with DB overrides will cause the alert mechanism to go to the global MinionDefault row for its values, the intent is more clear when you have a MinionDefault row for the server. As well you could also see unintended results when you don’t have a MinionDefault row for this server. Let’s look at some examples to illustrate what could happen.
Scenario 1:
You have a DB-level override but no MinionDefault row for that server.
| InstanceID | DBName | FullAlertThresholdInHrs |
| 0 | MinionDefault | 24 |
| 1 | DB1 | 48 |
| 1 | DB2 | 48 |
| 1 | DB3 | 48 |
EXEC Setup.BackupReportThreshold 'MinionDevcon', 'DB1', 'Full', 16;
Result:
| InstanceID | DBName | FullAlertThresholdInHrs |
| 0 | MinionDefault | 24 |
| 1 | DB1 | 16 |
| 1 | DB2 | 48 |
| 1 | DB3 | 48 |
Here you get the expected result. The value for DB1 has been changed.
However, if you call the SP like this, you get an unexpected result.
EXEC Setup.BackupReportThreshold 'MinionDevcon', 'All', 'Full', 16;
Result:
| InstanceID | DBName | FullAlertThresholdInHrs |
| 0 | MinionDefault | 24 |
| 1 | DB1 | 16 |
| 1 | DB2 | 16 |
| 1 | DB3 | 16 |
Here the values for all the DBs for InstanceID 1 were changed, but you have to remember what the intent of the command was. The intent was for all the DBs on InstanceID 1 to have FullAlertThresholdInHrs of 16. But what you got, were the DBs that have exceptions. Every other DB on that server still get their values from the global MinionDefault row. This is why it’s important to have a MinionDefault row for any server that has exceptions.
Scenario 2:
Now let’s look at a scenario where there’s a MinionDefault row for this server.
| InstanceID | DBName | FullAlertThresholdInHrs |
| 0 | MinionDefault | 24 |
| 1 | MinionDefault | 24 |
| 1 | DB1 | 48 |
| 1 | DB2 | 48 |
| 1 | DB3 | 48 |
When we call the SP:
EXEC Setup.BackupReportThreshold 'MinionDevcon', 'All', 'Full', 16;
Result:
| InstanceID | DBName | FullAlertThresholdInHrs |
| 0 | MinionDefault | 24 |
| 1 | MinionDefault | 16 |
| 1 | DB1 | 16 |
| 1 | DB2 | 16 |
| 1 | DB3 | 16 |
This time we got what we actually intended; we changed the value for all the DBs on the server.
Using this procedure to manage your backup alert thresholds will enforce this concept of proper management. You’re not required to use this procedure though. You can manage the dbo.BackupReport table manually, or write your own procedure.
Database Checking
This procedure doesn’t do any DB checking. If you pass it a @DBName that doesn’t exist on that server, it will be inserted like any other DB. This allows you to enter DBs that don’t exist yet in case you’re setting up for a future installation. If you enter a DB that doesn’t exist, it won’t do any harm. It just bloats your dbo.BackupReport table.
Limitations:
Currently, you cannot change global values using this procedure. You can only change instance-level values.