The MidnightDBA team is announcing the release of a new, free backup solution for SQL Server: Minion Backup arrives on June 1!
Minion Backup by MidnightDBA is a stand-alone database backup module. Once installed, Minion Backup automatically backs up all online databases on the SQL Server instance, and will incorporate databases as they are added or removed.
We created Minion Backup (or MB, for short) to be the most flexible, feature-rich backup solution possible. Our goal for this initial release was to include functionality for as many possible backup scenarios as possible. We’ve included certificate backups, HA and DR awareness, restore script generation, “what if” functionality for deletes, the ability to run a batch for “missing” backups, built in manual runs, rollup and detail data in the backup logs, the ability to deactivate most settings, copy / move / stripe / mirror backup files, etc.
Table based scheduling
While there are about fifty features I’d like to talk about, I’m going to restrain myself (today) and talk about the one feature I’m most excited about (today): table based scheduling.
When Minion Backup is installed, it creates a single backup job that runs the master backup stored procedure every 30 minutes. That master procedure checks the Minion.BackupSettingsServer table to determine what backups should be run for the current day and time.
By default, Minion Backup comes installed with the following scenario:
- Full system backups are scheduled daily at 10:00pm.
- Full user backups are scheduled on Saturdays at 11:00pm.
- Differential backups for user databases are scheduled daily except Saturdays (weekdays and on Sunday) at 11:00pm.
- Log backups for user databases run daily as often as the backup runs (every 30 minutes).
Let’s look at just a few of the columns of this default scenario in Minion.BackupSettingsServer:
| ID |
DBType |
BackupType |
Day |
BeginTime |
EndTime |
MaxForTimeframe |
Include |
Exclude |
| 1 |
System |
Full |
Daily |
22:00:00 |
22:30:00 |
1 |
NULL |
NULL |
| 2 |
User |
Full |
Saturday |
23:00:00 |
23:30:00 |
1 |
NULL |
NULL |
| 3 |
User |
Diff |
Weekday |
23:00:00 |
23:30:00 |
1 |
NULL |
NULL |
| 4 |
User |
Diff |
Sunday |
23:00:00 |
23:30:00 |
1 |
NULL |
NULL |
| 5 |
User |
Log |
Daily |
00:00:00 |
23:59:00 |
48 |
NULL |
NULL |
I’m not going to fully document this table here – I’ll be happy to send you a draft of the product documentation if you can’t wait for the release date – but you get an initial impression of how flexible this scenario can be, especially in conjunction with other settings tables. I will note that “Include” and “Exclude” allow comma delimited lists of databases (and/or LIKE operators) to include in, or exclude from, the particular backup scenario; a value of NULL means that all databases are included.
This is how MB operates by default, to allow for the most flexible backup scheduling with as few jobs as possible.
Table based scheduling presents multiple advantages:
- A single backup job – Multiple backup jobs are, to put it simply, a pain. They’re a pain to update and slow to manage, as compared with using update and insert statements on a table.
- Fast, repeatable configuration – Keeping your backup schedules in a table saves loads of time, because you can enable and disable schedules, change frequency and time range, etc. all with an update statements. This also makes standardization easier: write one script to alter your backup schedules, and run it across all Minion Backup instances (instead of changing dozens or hundreds of jobs).
- Mass updates across instances – With a simple Powershell script, you can take that same script and run it across hundreds of SQL Server instances at once, standardizing your entire enterprise with ease.
- Transparent scheduling – Multiple backup jobs tend to obscure the backup scenario, because each piece of the configuration is displayed in separate windows. Table based scheduling allows you to see all aspects of the backup schedule in one place, easily and clearly.
- Boundless flexibility – Table based scheduling provides a stunning degree of flexibility that would be very troublesome to implement with multiple jobs. With a single backup job, you can schedule all of the following:
- System full backups three days a week.
- User full backups on weekend days and Wednesday.
- DB1 log backups between 7am and 5pm on weekdays.
- All other user log backups between 1am and 11pm on all days.
- Differential backups for DB2 at 2am and 2pm.
- Read only backups on the first of every month.
…and each of these can also use dynamic backup tuning, which can also be slated for different file sizes, applicable at different times and days of the week and year.
…and each of these can also stripe across multiple files, to multiple locations, and/or copy to secondary locations, and/or mirror to a secondary location.
Like I said, there are a zillion and a half more things I’d like to talk about, but we’ll keep it right here for now. Reply below, email, or ping @MidnightDBA on Twitter with questions or comments. And keep an eye out on June 1!
Check out Minion Enterprise, our new enterprise management solution for centralized SQL Server management and alerting!
P.S. Anticipating a few FAQs (and I’ll add to this as things come up):
- Yes, you can change how often the backup job runs. If, for example, you only want log backups to run hourly, set your job to run hourly.
- Yes, absolutely, you still have the option to use the more traditional “multi job” backup scheduling. You’d just disable the single job mentioned above, and configure the new jobs with individual schedules and a parameterized master query. Easy.
- The Include and Exclude fields aren’t the only way to include and exclude databases, but we’re not going to get into that just now.
We solved this question.
