Distinguish backup file names or pay the price!

Articles, Minion Backup, Tech / By
Running around a track
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:

  1. On day 1, MB takes a backup of DB1, to \\NAS1\SQLBackups\DB1Full.BAK.
  2. On day 2, MB takes a backup of DB1, which overwrites the file \\NAS1\SQLBackups\DB1Full.BAK.
  3. 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.
  4. 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:

    UPDATE Minion.BackupSettingsPath
    SET FileName='%Ordinal%of%NumFiles%%DBName%%BackupType%%Date%%Hour%%Minute%%Second%';

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.