Build Dynamic Backup Locations with Minion Backup

Minion Backup / By

Minion BackupWhen 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.

The Basics

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:

%Ordinal%Of%NumFiles%%DBName%%BackupType%

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:

1Of5AdventureWorksFull.bak
2Of5AdventureWorksFull.bak
3Of5AdventureWorksFull.bak
4Of5AdventureWorksFull.bak
5Of5AdventureWorksFull.bak

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:

1outOf5_AdventureWorks_Full.bak
2outOf5_AdventureWorks_Full.bak
3outOf5_AdventureWorks_Full.bak
4outOf5_AdventureWorks_Full.bak
5outOf5_AdventureWorks_Full.bak

And, %DBName%_%BackupType%_%Ordinal%outOf%NumFiles% would produce files named:

AdventureWorks_Full_1outOf5.bak
AdventureWorks_Full_2outOf5.bak
AdventureWorks_Full_3outOf5.bak
AdventureWorks_Full_4outOf5.bak
AdventureWorks_Full_5outOf5.bak

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:

AdventureWorks_Full_1outOf5_201703301100.bak
AdventureWorks_Full_2outOf5_201703301100.bak
AdventureWorks_Full_3outOf5_201703301100.bak
AdventureWorks_Full_4outOf5_201703301100.bak
AdventureWorks_Full_5outOf5_201703301100.bak

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!