IMPORTANT: We highly recommend backing up to UNC paths, instead of to locally defined drives. Especially in the context of the Data Waiter feature, UNC paths allow a smoother transition between replicas or to a warm failover server. For more information, see “About: Synchronizing settings and log data with the Data Waiter”.
Several “How To” sections provide instructions for copy, move, and mirror scenarios that use the Minion.BackupSettingsPath table:
- How to: Set up mirror backups
- How to: Copy files after backup (single and multiple locations)
- How to: Move files to a location after backup
- How to: Copy and move backup files
- How to: Back up to multiple files in a single location
- How to: Back up to multiple locations
Also see the discussion below, after the columns description.
| Name | Type | Description |
| ID | Int | Primary key row identifier. |
| DBName | sysname | Database name. |
| isMirror | bit | Is a backup mirror location. |
| BackupType | Varchar | Backup type. Valid inputs: ALL Full Diff Log ServerCert DatabaseCert Move Copy Note that ALL encompasses full, differential, and log backups. |
| BackupLocType | varchar | Backup location type. Example values: Local NAS URL Note: URL and NUL are the most important of these, and are used by the Minion Backup process. The remaining input(s) are user defined, as they’re just information for you. However, once combined with Minion Enterprise, these are all important for reporting. |
| BackupDrive | Varchar | Backup drive. This is only the drive letter of the backup destination. Alternately, this value can be NUL if BackupLocType is NUL. IMPORTANT: If this is drive, this must end with colon-slash (for example, ‘M:\’). If this is URL, use the base path (for example, ‘\\server2\’) |
| BackupPath | varchar | Backup path. This is only the path (for example, ‘SQLBackups\’) of the backup destination. Alternately, this value can be NUL if BackupLocType is NUL. |
| FileName | varchar | The name of the file, without the extension. |
| FileExtension | varchar | The file extension, with the period. For example: “.bak”. Both NULL AND ‘MinionDefault’ will cause MB to use the default extension as appropriate: for backup files, ‘.bak’ or ‘.trn’, and for certificate backups, ‘.cer’ and ‘.pvk’. This field accepts Inline Tokens. Examples: NULL MinionDefault .bak %BackupTypeExtension% |
| ServerLabel | Varchar | A user-customized label for the server name. It can be the name of the server, server\instance, or a label for a server. This is used for the backup file path. This comes in handy especially in Availability groups; if on day 1 we are on AG node 1, and on day 2 we are on AG node 2, we don’t want the backups to save to different physical locations based on that name change. We instead provide a label for all databases on the instance – whether or not they’re in an AG – so backups will all be in a central place (and so that cleaning up old backups is not an onerous chore). As this is just a label meant to group backup files, you could conceivably use it any which way you like; for example, one label for AG databases, and another for non-AG, etc. Cannot contain a dynamic part. |
| RetHrs | int | Number of hours to retain the backup files. |
| FileActionMethod | Used to specify the program to use to perform the COPY/MOVE actions. Note: NULL and COPY are the same. And while the setting is called COPY, it uses PowerShell COPY or MOVE commands as needed. Valid inputs: NULL (same as COPY) COPY MOVE XCOPY ROBOCOPY ESEUTIL Note that ESEUTIL requires additional setup. For more on this topic, see “How to Topics: Backup Mirrors and File Actions” and “About: Copy and move backup files”. |
|
| FileActionMethodFlags | Used to supply flags for the method specified in FileActionMethod. The flags will be appended to the end of the command; this is the perfect way to provide specific functionality like preserving security, attributes, etc. For more on this topic, see “How to Topics: Backup Mirrors and File Actions” and “About: Copy and move backup files”. |
|
| PathOrder | Int | If a backup goes to multiple drives, or is copied to multiple drives, then PathOrder is used to determine the order in which the different drives are used. IMPORTANT: Like all ranking fields in Minion, PathOrder is a weighted measure. Higher numbers have a greater “weight” - they have a higher priority - and will be used earlier than lower numbers. |
| IsActive | bit | The current row is valid (active), and should be used in the Minion Backup process. |
| AzureCredential | Varchar | The name of the credential used to back up to a Microsoft Azure Blob. When you take a backup to a Microsoft Azure Blob (with TO URL=’…’), you must set up a credential under security so you can access that blob. You have to pass that into the backup statement (WITH CREDNTIAL=’…’). See https://msdn.microsoft.com/en-us/jj720558 |
| Comment | Varchar | For your reference only. You can label each row with a short description and/or purpose. |
Discussion:
The Minion.BackupSettingsPath table comes with one default row: DBName=’MinionDefault’ and isMirror=0. If all of your backups are going to same location, you only need to update this row with your backup location.
You can also insert additional rows to configure the backup file target for an individual database, to override the default backup settings for that database.
You can also insert a row with BackupType=’MOVE’, to move a backup file after the backup operations are complete; and/or one or more rows with BackupType=’COPY’ to copy a backup file. Both MOVE and COPY operations are performed at a time designated by the FileActionTime field in the Minion.BackupSettings table. For example, if FileActionTime is set to ‘AfterBackup’, then a MOVE or COPY specified here in Minion.BackupSettingsPath will happen immediately after that backup (instead of at the end of the entire backup operation).
To backup a server certificate or database certificate, you must insert a row with BackupType = ‘ServerCert’. Server certificate backups don’t make use of the DBName field, so you can set it to ‘MinionDefault’, to signify that it applies universally. To backup a database certificate, you must insert an individual row for each –– either DBName = ‘MinionDefault’ and BackupType = ‘DatabaseCert’, or BackupType=’DatabaseCert’ for a specific database.
Minion Backup will not back up certificates without an explicit BackupType=’ServerCert’ / ‘DatabaseCert’ row(s). You can have multiple certificate backup path rows for the same database (or for the server) going to multiple locations, all with isActive = 1. This is because certificates are so important to the restoration of a database, that Minion Backup allows you to back up the certificates to multiple locations. If you have five rows for DB2 database certificate backups, and all are set to isActive = 1, then all five of them are valid and will be executed. For more information, see the “How to: Configure certificate backups” section.