minion backupLet’s talk about changing the retention hours for your backup files.  We’ll see the proper way to do it, as well as discuss the rationale behind the way Minion Backup works in this regard.

First of all, there are two settings that sound very similar, but do completely different things, which can be a source of confusion (and misconfiguration): HistRetDays and RetHrs.

The HistRetDays in the dbo.BackupSettings table controls how long the data sits in the log tables, not how long the files sit on the disk.

The RetHrs in the Minion.BackupSettingsPath table controls how long the backups files remain on disk.

Change the retention time for backup files

There are two steps to fully changing the RetHrs.  First, make the change in the dbo.BackupSettingsPath table.  This will give all future backups the new retention setting you want.  Next, you need to change the RetHrs column in the Collector.BackupFiles table.  This is the setting that is used when the delete routine runs.

UPDATE Minion.BackupSettingsPath
SET RetHrs = 48; -- We want to retain backup files for 48 hours.

UPDATE Minion.BackupFiles
SET RetHrs = 48
WHERE IsDeleted = 0; -- Existing backup files should be deleted after 48 hours.

Why not specify RetHrs for the whole database?

You might be wondering why the RetHrs setting is in the Minion.BackupSettingsPath table instead of in the Minion.BackupSettings table with the rest of the database backup configuration.  If you think about it, this is a wonderful design.  When done this way, every backup type and every instance can have its own retention period.  You can choose to keep files on one instance for 24 hours, files on another instance for 48 hours, and so on. It’s very flexible.

Minion.BackupSettings contains the essential backup settings for a database and backup type.

Minion.BackupSettingsPath holds backup path destinations, and backup file copy and move settings. This is specific to a database, backup type, and destination path.

You can think of BackupSettings as config for database backups; and BackupSettingsPath as config for backup files.

RetHrs isn’t a database level setting.  We don’t determine retention hours for the backup; you’re setting retention for the files themselves.  This is why RetHrs is specified by backup path, and not by database.

Let’s take an example.  We’re taking full backups once a week, differential backups daily, and log backups every 30 minutes.  We probably don’t want to keep all the differential backups, but just the last one or two.  If Minion Backup specified RetHrs in Minion.BackupSettings, there are scenarios where you’d be stuck with the same retention for all of your backup files.  Because RetHrs is a part of the Minion.BackupSettingsPath table, also allows you to have separate retention for certificate backups, mirrors, copies, and so on.

Future vs. Past

Why do we need to change the Minion.BackupFiles table setting?  It seems like the delete routine should use the setting from Minion.BackupSettingsPath, right?  No, and here’s why.

The RetHrs column in Minion.BackupSettingsPath tells the system how long you want to keep the files the next time you take a backup.  When the backup runs, that setting is inserted into the Minion.BackupFiles table for each of the new backup files.  Later, when the delete routine comes along, it reads RetHrs from Minion.BackupFiles for each file, and deletes based off of that.

Those files that were marked to be deleted at (for example) 24 hours when they were written. Then we decided to keep backup files for 48 hours.  Should that change propagate to existing files? Or do we want the new setting to take effect going forward?

If you want the new RetHrs setting to apply to existing (past) backup files, update Minion.BackupFiles to tell the system these files are to be kept longer.

UPDATE Minion.BackupFiles
SET RetHrs = 48
WHERE IsDeleted = 0;

Here’s another option: You can mark certain files as archived (using IsArchive = 1) in Minion.BackupFiles, and they won’t be deleted at all.  Or, you can just give your month-end backups a RetHrs of a year (8760 hours), if you need to keep them for a longer, set amount of time.

When will the file be deleted?

There’s one last minor thing to consider: the ToBeDeleted column in Minion.BackupFilesToBeDeleted holds the date and time when the files are scheduled to be deleted.  This is just a friendly column we put in there to keep you from having to do the math yourself; the delete routine doesn’t look at this column at all.  It just allows you to see at a glance when your files are expected to be deleted.

The backup process writes this column when it records the backup file to the table, but it’s not updated if the retention hours changes for that row. If you want to keep ToBeDeleted accurate, your update statement will have to include both it and RetHrs.

UPDATE Minion.BackupFiles
SET RetHrs = 48
 , ToBeDeleted = DATEADD(HOUR, 48, ExecutionDateTime)
WHERE IsDeleted = 0;

This query only changes the files that haven’t been deleted yet. You can certainly alter your WHERE clause as needed, to limit it to a specific DBName, or BackupType, or even date range.  The possibilities are almost endless.

(By the way, you can see when the files were actually deleted with the DeleteDateTime column in the Minion.BackupFiles table.)

Remember: the ToBeDeleted column is just for your use.  Minion Backup doesn’t use or care about this value.  However, if you neglect this and in two months you decide to check and see if your files are being deleted on time, you may forget that you changed the RetHrs, and the ToBeDeleted date is no longer valid.  It’s a good idea to go ahead and change this value, because you don’t know what type of investigation you’re going to do in the future.