When you back up your database with a third-party party backup utility, you’re almost always stuck with their hard coded path structure and file names. But Minion Backup – our free SQL Server backup tool – gives you fully dynamic paths and file names. You can even programmatically change them based on any criteria. We call this our Inline Tokens, and it’s incredibly powerful.
In Minion Backup, the file and path info is stored in the Minion.BackupSettingsPath table, and it looks like this:
These are just a few columns from the table. You’ll notice right away that BackupPath and FileName include SQL Server wildcards. These are built-in parameters you can use to build your own strings.
Let’s look at a couple examples with FileName.
Default Backup File Names
It’s very easy to stripe your backup files with Minion Backup; when you stripe, it’s an excellent idea to number your files in the FileName itself. For example, “1Of3masterFull”, “2Of3masterFull”, and so on.
In Minion.BackupSettingsPath above, the FileName string looks like this:
Each of these wildcards stands for something:
- %Ordinal% – The ordinal number of the current file in the stripe.
- %NumFiles% – The total number of files in the stripe.
- %DBName% – The name of the DB being backed up.
- %BackupType% – Full, Diff, or Log.
So, the files for an AdventureWorks full backup look like this:
Custom Backup File Names
That’s just the default setting, though. Instead, you can choose a different filename format. For example:
%Ordinal%outOf%NumFiles%_%DBName%_%BackupType% would yield:
And, %DBName%_%BackupType%_%Ordinal%outOf%NumFiles% would produce files named:
A Wide Range of Customization
And there are many built-in parameters you can add. Want to datestamp your files? That’s easy.
%DBName%_%BackupType%_%Ordinal%outOf%NumFiles%_%Date%%Time% gives you:
Minion Backup comes with about 25 built-in variables. What’s more, you can add your own very easily, so if you’re not happy with one of the defaults, create your own!
Custom Backup Paths
So, there was a set of examples using filenames, but what about paths? What kind of dynamic paths could you need?
- Perhaps you want to change where a backup happens based on which datacenter you’re in. With Minion Backup, you can detect which server you’re on and change the backup location based on that.
- Or you can easily setup a monthly archive location, so Minion Backup backs up to a different location at the end of the month.
- Or you can simply add the database name to your backup path – or the Availability Group name, or the AG listener name, or a ServerLabel you’ve assigned, or even put it in a special folder with the name of the month and day.
The sky’s the limit.
Minion Backup frees you from the rigid paths and filenames defined by a vendor. Use your own naming conventions, and get exactly what you need. Minion Backup is available for free on MinionWare.net along with Minion Reindex and Minion CheckDB.
Enhance Your DBA Brain
Sign up for the MinionWare newsletter to get:
– The eBook “DBA Rants from a Microsoft Certified Master”
– Our best articles on SQL backup and maintenance
– Links to free MinionWare tools
– News, updates, and tips!
SQL instances shouldn’t do this
So far, no one has found exercise to be beneficial to servers. Purposeless repetitive motion may be good for human muscles, but your SQL Server instance experiences no gain for the pain.
Here’s a good example: taking useless backups.
(“Did she say useless backups? I’ve never heard of such a thing!” Yeah, just wait.)
Backup file names are critical
Traditionally, backup files are named after the database and the backup type, and given a timestamp. So you’ll see something like master_FULL_20170101.bak. If you like to stripe your backups, you might name the files something like 1of5MasterFull20170101.bak, and so on.
But I have run across shops that takes backups without bothering to time stamp the file name: master_FULL.bak. These shops either overwrite each backup file, with each successive backup (using INIT and FORMAT), or add to the backup set (which I find mildly annoying, but to each their own).
The problem with using the same backup file name over and over is if you have a cleanup mechanism that deletes old backup files!
The same-name cleanup issue
Let’s say that in your shop, you have Minion Backup (MB) installed and running with the following options:
- INIT is enabled
- FORMAT is enabled
- Backup file retention (in hours) is 48, so we’ll keep 2 days’ worth of backups
- Backup name is set to %DBName%%BackupType%.bak, which works out to DB1Full.bak for a full backup of DB1.
Note: For more information on the %DBName% and %BackupType% inline tokens, see “About: Inline Tokens” in our help center.
Here is what happens:
- On day 1, MB takes a backup of DB1, to \\NAS1\SQLBackups\DB1Full.BAK.
- On day 2, MB takes a backup of DB1, which overwrites the file \\NAS1\SQLBackups\DB1Full.BAK.
- On day 3, MB takes a full backup of DB1 (which overwrites the same file). And then the delete procedure sees that it has a file from day 1 (>48 hours ago) that needs deleting. And so it deletes \\NAS1\SQLBackups\DB1Full.BAK. Remember, this is the file that MB has been overwriting again and again.
- On day 4, MB takes a backup of DB1, to \\NAS1\SQLBackups\DB1Full.BAK.. Then, it sees that it has a file from day 2 that needs deleting, and so deletes \\NAS1\SQLBackups\DB1Full.BAK.
See? From Day 4 on, we’re creating a backup file just to delete it again!
Fixing the issue if you have MB
One excellent way to figure out if you have this problem is to notice that, hey, you don’t have any backup files. Another indicator in Minion Backup is if you see “Complete: Deleted out-of-process. SPECIFIC ERROR:Cannot find path ‘…’ because it does not exist.” in the Minion.BackupFiles Status column.
But the real smoking gun is if you haven’t time stamped your backup files in Minion.BackupSettingsPath. Here’s how to fix that:
That will give each file its own name, and eliminate the chance of this ever happening.
And it will prevent your server from performing useless exercise. Me? I’m going to hit the gym.
Most of us love technology. And most of us have experienced how blindingly fast technology can provide some degree of cataclysmic failure. Let me explain.
I’ve been getting better and better about planning out my work week, listing out what needs doing each day, and sticking to it. So I knew that today I would be working a little on a few website updates in the morning, and then the rest of the day would be free for coding.
(I pause here for the audience to have a hearty chuckle.)
So here we are. I log onto the WordPress back end for MinionWare.net, change the wording and format of some text elements, and double-check that they look okay.
The other thing on my list is making sure I have a specific plugin installed. Is it? Why, yes it is! That’s grand, I guess my work here is about done.
But hey, look at that. A bunch of the other plugins need updating. I should go ahead and do that real quick…
(I pause here for the audience’s gasps of horror.) But wait, let me reassure you: I did pause and download a fresh WP backup.
And then I updated three WordPress plugins. Why, oh why did I do that? Why, when I know better?
The website came up as gibberish. Every page, every post, came up as complete gibberish. Of course I immediately restored the WP backup…which did absolutely nothing to help. It turns out that these backups are pretty much for content, not for restoring plugins to a specific state.
After a good deal of fighting and rage-coffee, I narrowed everything down to one culprit, killed the plugin with fire, and confirmed that the site was up and looking good.
Why is it so much easier to destroy than to fix?
This is totally a common theme in life. From the big glass bowl my kid shattered in the sink, to the car we (I won’t say who) scraped against a wall, to the appointment we missed. So much of our time is spent cleaning up mistakes, paying to have them fixed, and making up for lost time.
And technology lets you break things so much faster! I can drop a bowl and spend 30 minutes cleaning it up, but I can drop 2 Tb of data without a thought and spend weeks trying to get it back. (I mean literally, that’s what it takes to drop 2Tb…not thinking at all.) I can scrape the paint on my car and just leave the thing scraped…but I can bring down a years-old website with the click of a button.
You see a theme here?
I’m starting to see that a large percentage of an IT professional’s life is (or should be) disaster prevention – teaching yourself to triple check what server you’re connected to, making sure backups are up and running. And another very large percentage is disaster recovery, in one form or another. Yes, of course I mean traditional SQL disaster recovery. But I also mean recovering from the borked website, the forgotten perfmon trace, the third missed meeting this week (where your manager noticed particularly that you weren’t there).
Prevention and recovery
With technology, as with life, automation is a huge part of the solution. But, it’s not the whole solution.
- We can automate database backups.
- We can automate WordPress Backups.
- We must set reminders for meetings.
- We must set reminders to get the car’s oil changed. (Also: to keep off your dang phone while driving.)
- We should create standard procedures for maintenance and downtime.
- We should create standard procedures for managing personal tasks. (I’ve become a huge fan of the Bullet Journal method for this.)
And of course, we can’t prevent everything. So sometimes, we spend the morning staring furiously at wp-admin folders in FileZilla, instead of coding.
Good luck with the chaos.
Let’s take five minutes and get our entire enterprise in order.
Get your repo server, your Minion Enterprise download, and your license key (trial or permanent). Install and config take just five minutes.
Extract the MinionEnterprise2.2Setup.exe and run it on your repository server. Give the installation “localhost” for the instance name.
2. Configure email for alerts
Connect to the repo server and insert your alert email:
INSERT INTO dbo.EmailNotification ( EmailAddress, Comment )
SELECT 'Your@Email.com' AS EmailAddress, 'DBA' AS Comment;
3. Configure servers
Insert (or bulk insert) server to the repo:
INSERT INTO dbo.Servers
( 'Prod01', 'Gold', 1433, 1, 1),
( 'Prod02', 'Gold', 1433, 1, 1),
( 'Prod03', 'Silver', 1433, 1, 1),
( 'QA01', 'Silver', 1433, 1, 1),
( 'Dev01', 'Bronze', 1433, 1, 1),
( 'Dev02', 'Bronze', 1433, 1, 1);
4. You’re done!
Jobs will begin kicking off to collect data within the next hour. Some jobs run hourly, some run daily or weekly or monthly. You’ll start getting tables full of useful data, and email alerts that actually mean something.
If you’re impatient to start getting some of the good stuff right away, kick off the CollectorServerInfo% jobs manually. These populate data in the dbo.Servers table, which other jobs need to run. You’ll start noticing data in the Collector.DBProperties, Collector.ServiceProperties, and Collector.DriveSpace tables first, as these jobs run most frequently.
While ME is taking care of your shop for you, you can get to know it better with the online documentation!
Download our FREE maintenance modules below:
- Minion Backup: Free SQL Server Backup utility with world-class enterprise features and full lifecycle management.
- Minion Reindex: Free SQL Server Reindex utility with unmatched configurability.
- Minion CheckDB: Free SQL Server CHECKDB that solves all of your biggest CHECKDB problems.
Minion CheckDB is available for download as of February 1, 2017!
In celebration, we had Minion CheckDB webinars.
Done: And, we will be giving away 3 licenses of Minion Enterprise to one lucky winner* at each webinar. Must be present to win!
Minion CheckDB completes the MinionWare maintenance and backups suite in style. Each solution is plug-and-play for the busy DBA, and deeply configurable for those shops with in-depth needs.
This new module is MinionWare’s most ambitious free release yet, featuring all of the rich scheduling and logging functionality in previous products, plus remote CheckDB, multithreading, custom snapshots, rotational scheduling, and more.
In this webinar we’ll show you how this FREE tool by MinionWare can meet your needs with almost effortless management. You are going to LOVE it.
If you simply can’t wait, check out the Minion CheckDB tutorials!
*(Giveaway offer is not open to previous ME winners.)