When you back up your database with a third-party party backup utility, you’re almost always stuck with their hardcoded path structure and file names. But Minion Backup – a 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 from http://minionware.net/ along with Minion Reindex and Minion CheckDB.