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
– 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!