Minion.BackupSettings is installed with default settings already in place, via the system-wide default row (identified by DBName = “MinionDefault” and BackupType = “All”). If you do not need to fine tune your backups at all, no action is required, and all backups will use this default configuration.
Important: Do not delete the MinionDefault row, or alter the DBName or BackupType columns for this row!
To override these default settings for a specific database, insert a new row for the individual database with the desired settings. Note that any database with its own entry in Minion.BackupSettings retrieves ALL its configuration data from that row. For example, if you enter a row for [YourDatabase] and leave the ShrinkLogOnLogBackup column at NULL, Minion Backup does NOT retrieve that value from the “MinionDefault” row; in this case, ShrinkLogOnLogBackup for YourDatabase would default to off (“no”).
| Name | Type | Description |
| ID | int | Primary key row identifier. |
| DBName | sysname | Database name. |
| Port | Int | Port number for the instance. If this is NULL, we assume the port number is 1433. Minion Backup includes the port number because certain operations that are shelled out to sqlcmd require it. |
| BackupType | Varchar | Backup type. Valid inputs: All Full Diff Log Note that “All” encompasses full, differential, and log backups. |
| Exclude | Bit | Exclude database from backups. For more on this topic, see “How To: Exclude databases from backups”. |
| GroupOrder | Int | The backup order within a group. Used solely for determining the order in which databases should be backed up. By default, all databases have a value of 0, which means they’ll be processed in the order they’re queried from sysobjects. Higher numbers have a greater “weight” (they have a higher priority), and will be backed up earlier than lower numbers. We recommend leaving some space between assigned back up order numbers (e.g., 10, 20, 30) so there is room to move or insert rows in the ordering. For more information, see “How To: Backup databases in a specific order”. |
| GroupDBOrder | int | Group to which this database belongs. Used solely for determining the order in which databases should be backed up. By default, all databases have a value of 0, which means they’ll be processed in the order they’re queried from sysobjects. Higher numbers have a greater “weight” (they have a higher priority), and will be backed up earlier than lower numbers. The range of GroupDBOrder weight numbers is 0-255. For more information, see “How To: Backup databases in a specific order”. |
| Mirror | Bit | Back up to a secondary mirror location. Note: This option is only available in SQL Server Enterprise edition. |
| DelFileBefore | bit | Delete the backup file before taking the new backup. |
| DelFileBeforeAgree | bit | Signifies that you know deleting the backup file first is a bad idea (because it leaves you without a backup, should your current backup fail), but that you agree anyway. |
| LogLoc | varchar | Determines whether log data is only stored on the local (client) server, or on both the local server and the central Minion (repository) server. Valid inputs: Local Repo |
| HistRetDays | smallint | Number of days to retain a history of backups (in Minion Backup log tables). Minion Backup does not modify or delete backup information from the MSDB database. Note: This setting is also optionally configurable at the backup level, and also at the BackupType level. So, you can keep log history for different amounts of time for log backups than you do for full backups. |
| MinionTriggerPath | varchar | UNC path where the Minion logging trigger file is located. Not applicable for a standalone Minion Backup instance. |
| DBPreCode | Nvarchar | Code to run for a database, before the backup operation begins for that database. For more on this topic, see “How To: Run code before or after backups”. |
| DBPostCode | nvarchar | Code to run for a database, after the backup operation completes for that database. For more on this topic, see “How To: Run code before or after backups”. |
| PushToMinion | Bit | Save these values to the central Minion server, if it exists. Modifies values for this particular database on the central Minion server. A value of NULL indicates that this feature is off. Functionality not yet supported. |
| DynamicTuning | bit | Enables dynamic tuning. For more on dynamic tuning, see “How to: Set up dynamic backup tuning thresholds”. |
| Verify | Varchar | Specifies when the RESTORE VERIFYONLY operation is to happen. Warning: Just as with the FileActionTime column, this setting must be used with caution. Verifying backups can take a long time, and you could hold up subsequent backups while running the verify. We recommend using AfterBatch. (Note that the FileAction operation is processed before the Verify operation.) Valid inputs: NULL (meaning do not run verify) AfterBackup AfterBatch See http://msdn.microsoft.com/en-us/library/ms188902.aspx |
| PreferredServer | Varchar | The server on which you would like to perform backups in an Availability Group. A NULL in this field defaults to the current AG primary (if in an AG scenario). This field is ignored for databases not in an AG scenario. Valid inputs: NULL AGPreferred <specific server or server\instance name> For more on this topic, see “How to: Set up backups on Availability Groups”. |
| ShrinkLogOnLogBackup | Bit | Turn on log shrink after log backups. For more on this topic, see “How to: Shrink log files after backup”. |
| ShrinkLogThresholdInMB | int | How big (in MB) the log file is before Minion Backup will shrink it. For example, if a log file is 1% full, but the file is only 1 GB, we probably don’t want to shrink it. Note that you could force a shrink after every log backup by setting this to 0, but we don’t advise it. For more on this topic, see “How to: Shrink log files after backup”. |
| ShrinkLogSizeInMB | int | The size (in MB) the log file shrink should target. In other words, how big you would like the log file to be after a file shrink. This setting applies for EACH log file, not for all log files totaled. If you specify 1024 as the size here, and you have three log files for your database, Minion Backup will attempt to shrink each of the three log files down to 1024MB (so you’ll end up with at least 3072MB of logs). For more on this topic, see “How to: Shrink log files after backup”. |
| MinSizeForDiffInGB | bigint | The minimum size of a database (in GB) in order to perform differentials; databases under this size will not get differential backups. A value of NULL or 0 means that there is no restriction on whether to take differential backups. |
| DiffReplaceAction | varchar | If a database does not meet the MinSizeForDiffInGB limit, perform another action instead of a differential backup (e.g., perform a log backup instead). While Minion Backup allows you to perform a full backup in lieu of a differential, understand that this could increase the expected time of the backup jobs. A NULL value means the same as “Skip”. Valid inputs: Full Log Skip NULL |
| LogProgress | bit | Track the progress of backup operations for this database. Status is tracked in the Minion.BackupLog table. |
| FileAction | varchar | Move or copy the backup file. A value of NULL means this setting has no move or copy operations. If COPY or MOVE is specified, at least one corresponding COPY entry (or a single corresponding MOVE entry, as appropriate) is required in the Minion.BackupSettingsPath table, to determine the path to copy or move to. IMPORTANT: If there is no corresponding COPY or MOVE entry, this setting will generate no error; there will just be no copy. Valid inputs: NULL COPY MOVE CopyMove For more on this topic, see “About: Copy and move backup files”. |
| FileActionTime | Varchar | The time at which to perform the COPY or MOVE FileAction. Valid inputs: AfterBackup AfterBatch For more on this topic, see “About: Copy and move backup files”. |
| Encrypt | bit | Encrypt the backup. |
| Name | varchar | The name of the backup set. See http://msdn.microsoft.com/en-us/library/ms186865.aspx |
| ExpireDateInHrs | int | Number of hours until the backup set for this backup can be overwritten. If both ExpireDateInHrs and RetainDays are both used, RetainDays takes precedence. See http://msdn.microsoft.com/en-us/library/ms186865.aspx |
| RetainDays | smallint | The number of days that must elapse before this backup media set can be overwritten. See http://msdn.microsoft.com/en-us/library/ms186865.aspx |
| Descr | varchar | Description of the backup set. Note: this must be no more than 255 characters. See http://msdn.microsoft.com/en-us/library/ms186865.aspx |
| Checksum | bit | Verify each page for checksum and torn page (if enabled and available) and generate a checksum for the entire backup. See http://msdn.microsoft.com/en-us/library/ms186865.aspx |
| Init | bit | Overwrite the existing backup set. See http://msdn.microsoft.com/en-us/library/ms186865.aspx |
| Format | bit | Overwrite the existing media header. Note that Format=1 is equivalent to Format=1 AND Init=1; therefore, FORMAT=1 will override the Init setting. See http://msdn.microsoft.com/en-us/library/ms186865.aspx |
| CopyOnly | bit | Perform a copy-only backup. Copy only backups do not affect the normal sequence of backups. See http://msdn.microsoft.com/en-us/library/ms186865.aspx |
| Skip | bit | Skip the check of the backup set’s expiration before overwriting. See http://msdn.microsoft.com/en-us/library/ms186865.aspx |
| BackupErrorMgmt | varchar | Rollup of the two BACKUP flags – STOP_ON_ERROR and CONTINUE_AFTER_ERROR. See http://msdn.microsoft.com/en-us/library/ms186865.aspx |
| MediaName | varchar | The backup set’s media name. See http://msdn.microsoft.com/en-us/library/ms186865.aspx |
| MediaDescription | varchar | Description of the media set. Note: this must be no more than 255 characters. See http://msdn.microsoft.com/en-us/library/ms186865.aspx |
| IsActive | bit | The current row is valid (active), and should be used in the Minion Backup process. |
| Comment | Varchar | For your reference only. You can label each row with a short description and/or purpose. |
Discussion:
The Minion.BackupSettings table comes with a row with “MinionDefault” as the DBName value, and “All” as the BackupType. This row defines the system-wide defaults.
Important: Any row inserted for an individual database overrides only ALL of the values, whether or not they are specified. Refer to the following for an example:
| ID | DBName | BackupType | Exclude | DBPreCode |
| 1 | MinionDefault | All | 0 | EXEC specialCode; |
| 2 | YourDatabase | Full | 0 | NULL |
The first row, “MinionDefault”, is the set of default values to use for all the databases in the SQL Server instance. These values will be used for backup for all databases that do not have an additional row in this table.
The second row, [YourDatabase], specifies some values for YourDatabase. This row completely overrides the “DefaultMinion” values for Full backups on YourDatabase.
When full backups are performed for YourDatabase, only the values from the YourDatabase/Full row will be used. So, even though the system-wide default (as specified in the MinionDefault row) for DBPreCode is ‘EXEC specialCode;’, Full backups on YourDatabase will NOT use that default value. Because DBPreCode is NULL for YourDatabase/Full, Full backups will perform no pre code for YourDatabase.
For more information, see the “Configuration Settings Hierarchy” section in “Architecture Overview”.
Example: Set custom configuration for Full backups on database ‘YourDatabase’.
INSERT INTO [Minion].[BackupSettings]
( [DBName] ,
[BackupType] ,
[Exclude] ,
[LogLoc] ,
[HistRetDays] ,
[ShrinkLogOnLogBackup] ,
[ShrinkLogThresholdInMB] ,
[ShrinkLogSizeInMB] ,
[Name] ,
[ExpireDateInHrs] ,
[RetainDays] ,
[Descr] ,
[Checksum] ,
[Init] ,
[Format] ,
[MediaName] ,
[MediaDescription]
)
SELECT 'YourDatabase' AS [DBName] ,
'All' AS [BackupType] ,
0 AS [Exclude] ,
'Local' AS [LogLoc] ,
60 AS [HistRetDays] ,
1 AS [ShrinkLogOnLogBackup] ,
1 AS [ShrinkLogThresholdInMB] ,
1024 AS [ShrinkLogSizeInMB] ,
'Backup name' AS [Name] ,
5 AS [ExpireDateInHrs] ,
2 AS [RetainDays] ,
'backup desc' AS [Descr] ,
1 AS [Checksum] ,
1 AS [Init] ,
1 AS [Format] ,
'MediaName' AS [MediaName] ,
'MediaDesc' AS [MediaDescription];