Minion Backup is made up of SQL Server stored procedures, functions, tables, and jobs.  There is also an optional PowerShell script for mass installation (MinionMassInstall.ps1) included in the download.  The tables store configuration and log data; functions encrypt and decrypt sensitive data; stored procedures perform backup operations; and the jobs execute and monitor those backup operations on a schedule.

This section provides a brief overview of Minion Backup elements at a high level: configuration hierarchy, include/exclude precedence, run time configuration, logging and alerting.

Note: Minion Backup is installed in the master database by default.  You certainly can install Minion in another database (like a DBAdmin database), but when you do, you must also verify that the job points to the appropriate database. 

Configuration Settings Hierarchy

The basic configuration for backup – including most of the BACKUP DATABASE and BACKUP LOG options – is stored in a table: Minion.BackupSettings.  A default row in Minion.BackupSettings (DBName=’MinionDefault’) provides settings for any database that doesn’t have its own specific settings. 

There is a hierarchy of granularity in Minion.BackupSettings, where more specific configuration levels completely override the less specific levels. That is:
  1. The MinionDefault row applies to all databases that do NOT have any database-specific rows.
  2. A MinionDefault row with BackupType=’Full’ (or Log, or Diff) provides settings for that backup type, for all databases that do NOT have any database-specific rows. This overrides the MinionDefault / All row.
  3. A database-specific row with BackupType=’All’ causes all of that database’s backup settings to come from that particular row (not from a MinionDefault row).
  4. A database-specific row with BackupType=’Full’ (or Log, or Diff) causes all of that database’s backup settings for that backup type to come from that particular row (not from a MinionDefault row, nor from the database-specific row where backupType=’All’).

The Configuration Settings Hierarchy Rule

If you provide a database-specific row, be sure that all backup types are represented in the table for that database. For example, if you insert a row for DBName=’DB1’, BackupType=’Full’, then also insert a row for DBName=’DB1’, BackupType=’All’ (or, alternately, two rows for DBName=’DB1’: one for Diff, and one for Log). Once you configure the settings context at the database level, the context stays at the database level, and not the default ‘MinionDefault’ level.

This document refers to the Configuration Hierarchy Settings Rule throughout, in situations where we must insert additional row(s) to provide for all backup types.

Note: “Exclude” is a minor exception to the hierarchy rules. If Exclude=1 for a database where BackupType=’All’, then all backups for that database are excluded.

Other tables hold additional backup configuration settings, and follow a similar hierarchy pattern.

Example 1: Proper Configuration

Let us take a simple example, in which these are the contents of the Minion.BackupSettings table (not all columns are shown here):
ID DBName BackupType Exclude DBPreCode
1 MinionDefault All 0 ‘Exec SP1;’
2 DB1 All 0 ‘Exec SP1;’
3 DB1 Full 0 NULL
 

There are a total of 30 databases on this server. As backups run throughout the week, the settings for individual databases will be selected as follows:
  • Full backups of database DB1 will use only the settings from the row with ID=3.
  • Differential and log backups of database DB1 will use only the settings from the row with ID=2.
  • All other database backups (full, log, and differential) will use the settings from the row with ID=1.

Note that a value left at NULL in one of these fields means that Minion Backup will use the setting that the SQL Server instance itself uses. So in our example, full backups of DB1 will run no precode; while all other backups will run ‘Exec SP1;’ as the database precode.

Example 2: Improper Configuration

Now let’s walk through another simple example, in which these are the contents of the Minion.BackupSettings table (not all columns are shown here):
ID DBName BackupType Exclude DBPreCode
1 MinionDefault All 0 ‘Exec SP1;’
2 DB1 Diff 0 ‘Exec SP1;’
3 DB1 Full 0 NULL
 

There are a total of 30 databases on this server. As backups run throughout the week, the settings for individual databases will be selected as follows:
  • Full backups of database DB1 will use only the settings from the row with ID=3.
  • Differential backups of database DB1 will use only the settings from the row with ID=2.
  • Log backups of database DB1 will fail, because no row exists that covers DB1 / log backups. Again: because we have specified settings for DB1 at the database level, Minion Backup will NOT use the MinionDefault settings for DB1.
  • All other database backups (full, log, and differential) will use the settings from the row with ID=1.
DB1 log backup failures will show up in the log tables (most easily viewable in Minion.BackupLogDetails, which will show a status that begins with “FATAL ERROR”).

Example 3: The "Exclude" Exception

Here we will demonstrate the effect of “Exclude” in rows of BackupType=’All’. In this example, these are the contents of the Minion.BackupSettings table (not all columns are shown here):
ID DBName BackupType Exclude DBPreCode
1 MinionDefault All 0 ‘Exec SP1;’
2 DB1 All 1 ‘Exec SP1;’
3 DB1 Full 0 NULL
 
There are a total of 30 databases on this server. As backups run throughout the week, the settings for individual databases will be selected as follows:
  • Backups of all types for database DB1 will be excluded, because of the row with ID=2. The log will not display failed backups for DB1; there will simply be no entry in the log for DB1 backups, as they are excluded.
  • Even full backups of database DB1 will be excluded.
  • All other database backups (full, log, and differential) will use the settings from the row with ID=1.

For more information, see the configuration sections in "How To" Topics: Basic Configuration (such as “How to: Configure settings for a single database”), and “Minion.BackupSettings”

Include and Exclude Precedence

Minion Backup allows you to specify lists of databases to include in a backup routine, in several different ways. First of all, databases are always divided into “system” and “user” databases.

Include and Exclude Strings

Within those divisions, the primary means of identifying what databases should be backed up in a given operation is by the use of Include and Exclude strings. As noted in the following section (“Run Time Configuration”), Include and Exclude can be defined as part of either a table configured schedule, or a parameter based schedule.

The important point to understand now, however, is how Include and Exclude work at a basic level. Include and Exclude may each have one of three kinds of values:
  • ‘All’ or NULL (which also means ‘All’)
  • ‘Regex’
  • An explicit, comma-delimited list of database names and LIKE expressions (e.g., @Include=’DB1,DB2%’).

Note: For this initial discussion, we are ignoring the existence of the Exclude bit, while we introduce the Include and Exclude strings. We’ll fold the Exclude bit concept back in at the end of the section.

The following table outlines the interaction of Include and Exclude:
  Exclude=’All’ or IS NULL Exclude=Regex Exclude=[Specific list]
Include=’All’ or IS NULL Run all backups Run all, minus regex exclude Run all, minus explicit exclude
Include=Regex Run only databases that match the configured RegEx expression Run only databases that match the configured RegEx expression Run only databases that match the configured RegEx expression
Include=[Specific list] Run only specific includes
 		 Run only specific includes
 		 Run only specific includes
 
 

Note that regular expressions phrases are defined in a special settings table (Minion.DBMaintRegexLookup).

Let us look at a handful of scenarios, using this table:
  • Include IS NULL, Exclude IS NULL – Run all backups.
  • Include = ‘All’, Exclude = ‘DB%’ – Run all backups except those beginning with “DB”.
  • Include=’Regex’, Exclude=’DB2’ – Run only databases that match the configured RegEx expression. (The Exclude is ignored.)
IMPORTANT: You will note that Exclude is ignored in any case where Include is not ‘All’/NULL.  Whether Include is Regex or is a specific list,  an explicit Include should be the final word. The reason for this rule is that we never want a scenario where a database simply cannot  be backed up.

Exclude bit

In addition to the Include and Exclude strings, Minion Backup also provides an “Exclude” bit in the primary settings table (Minion.BackupSettings) that allows you to exclude backups for a particular database, or a particular database and backup type.

The following table outlines the interaction of the Include string and the Exclude bit:
  Exclude=0 Exclude=1
Include=’All’ or IS NULL Run all backups Run all, minus excluded databases’ backup types
Include=Regex Run only databases that match the configured RegEx expression Run only databases that match the configured RegEx expression
Include=[Specific list] Run only specific includes
 		 Run only specific includes
 
 

Let us look at a handful of scenarios, using this table:
  • Include IS NULL, Exclude bit=0 – Run all backups.
  • Include = ‘All’, Exclude = 1 for DB2 / All – Run all backups except DB2.
  • Include=’Regex’, Exclude=1 for DB2 / All – Run only databases that match the configured RegEx expression. (The Exclude bit is ignored.)

IMPORTANT: You will note that the Exclude bit, like the Exclude string, is ignored in any case where Include is not ‘All’/NULL.  Whether Include is Regex or is a specific list,  an explicit Include should be the final word. The reason for this rule is that we never want a scenario where a database simply cannot  be backed up.

Run Time Configuration

The main Minion Backup stored procedure – Minion.BackupMaster – can be run in one of two ways: with table configuration, or with parameters.

Run Minion.BackupMaster using table configuration: If you run Minion.BackupMaster without parameters, the procedure uses the Minion.BackupSettingsServer table to determine its runtime parameters (including the schedule of backup jobs per backup type, and which databases to Include and Exclude). This is how MB operates by default, to allow for the most flexible backup scheduling with as few jobs as possible.

For more information, see the sections “How To: Change Backup Schedules”, “Minion.BackupSettingsServer”, and “Minion.BackupMaster”.

Run Minion.BackupMaster with parameters: The procedure takes a number of parameters that are specific to the current maintenance run.  For example:
  • Use @DBType to specify ‘System’ or ‘User’ databases.
  • Use @BackupType to specify Full, Log, or Diff backups.
  • Use @StmtOnly to generate backup statements, instead of running them. 
  • Use @Include to back up a specific list of databases, or databases that match a LIKE expression.  Alternately, set @Include=’All’ or @Include=NULL to back up all databases.
  • Use @Exclude to exclude a specific list of databases from backup.
  • Use @ReadOnly to (1) include ReadOnly databases, (2) exclude ReadOnly databases, or (3) only include ReadOnly databases.
For more information, see the section “How To: Change Backup Schedules” and “Minion.BackupMaster”.

Logging

As a Minion Backup routine runs, it keeps logs of all activity. The two primary log tables are:
  • Minion.BackupLog – a log of activity at the batch level.
  • Minion.BackupLogDetails – a log of activity at the database level.

The Status column for the current backup run is updated continually in each of these tables while the batch is running.  This way, status information (Live Insight) is available to you while backup is still running, and historical data is available after the fact for help in planning future operations, reporting, troubleshooting, and more.

Minion Backup logs additional information in a number of other tables, including:
  • Minion.BackupDebug – Log of high level debug data.
  • Minion.BackupDebugLogDetails – Log of detailed debug data.
  • Minion.BackupFileListOnly – log of RESTORE FILELISTONLY output for each backup taken
  • Minion.BackupFiles – a log of all backup files (whether they originate from a database backup, a certificate backup, a copy, or a move). Note that a backup that is striped to 10 files will have 10 rows in this table.
  • Minion.SyncCmds – a log of commands used to synchronize settings and log tables to configured synchronization servers. This table is both a log table and a work table: the synchronization process uses Minion.SyncCmds to push the synchronization commands to target servers, and it is also a log of those commands (complete and incomplete).
  • Minion.SyncErrorCmds – a log of synchronization commands that have failed, to be retried again later.

Minion Backup maintains all log tables are automatically. The retention period for all log tables is set in the HistoryRetDays field in Minion.BackupSettings.

Alerting

Minion Backup doesn’t include an alerting mechanism, though you can write one easily using the log tables.  You should also check out the Community Zone for the latest community-developed alerting solutions.

As of Minion Backup 1.3, jobs that fail will not show a “succeeded” status if one or more backups fail; the error and failed backup will also be recorded in the log. 

Here is one example of an alerting mechanism. Ideally, you could create a stored procedure, and simply call that procedure in step 2 of your backup job(s). 
---- Declare variables (could be SP parameters)
DECLARE @profile_name sysname = 'Server DBMail' ,
    @recipients VARCHAR(MAX) = 'SQLsupport@Company.com';
 
---- Declare and set internal variables
DECLARE @Query NVARCHAR(MAX) ,
    @Subject NVARCHAR(255);
 
SET @Query = 'SELECT  ID ,
        ExecutionDateTime ,
        ServerLabel ,
        @@SERVERNAME AS Servername ,
        STATUS ,
        PctComplete ,
        DBName
FROM    master.Minion.BackupLogDetails
WHERE   ExecutionDateTime = ( SELECT    MAX(ExecutionDateTime)
                              FROM      master.Minion.BackupLogDetails
                            )
        AND STATUS NOT IN (''All Complete'', ''Complete'');';
 
SELECT  @Subject = @@Servername + ' ALERT: Log backup failed';
 
---- Execute query to pull the rowcount
EXEC sp_executesql @Query;
 
---- If query returned rows, email to recipients
IF @@ROWCOUNT > 0
                EXEC msdb.dbo.sp_send_dbmail
                                @profile_name = @profile_name,
                                @recipients = @recipients,
                                @query = @Query ,
                                @subject = @Subject,
                                @attach_query_result_as_file = 0 ;
 
Important notes:
  • This is just one example of how you could code a backup alert for Minion Backup. Review and modify this code for your own use, if you like, or grow your own.
  • We do not recommend basing alerts off of Status=’Complete’, because a successful backup run will not always be marked “Complete”. It will be “All Complete” if the backup batch was run by Minion.BackupMaster, and “Complete” if run by Minion.BackupDB.
 
 
Minion Enterprise Hint
Minion Backup doesn’t include an alerting mechanism, though you can write one easily using the log tables. Minion Enterprise provides central backup reporting and alerting. The ME alert for all databases includes the reasons why any backups fail, across the entire enterprise. Further, you can set customized alerting thresholds at various levels (server, database, and backup type).  For example, you might set the alert thresholds for some servers to alert on missing backups after a day; for a handful of databases, to alert at half a day; for log backups, alert on 5 hours; and for development servers, not at all. The choice is yours.

See www.MinionWare.net for more information, or
email us today at Support@MidnightDBA.com for a demo!