One of SQL Safe's frequently used feature is the ability to automatically delete backups older than a certain time period. This feature, however, is often misunderstood in how it works under the covers. I'd like to take a closer look at this feature in hopes that it'll allow you to make better decisions when configuring your backup policy in SQL Safe.
The option that I'll be talking about can be found on the Location tab of the SQL Safe Backup Policy Wizard, as shown in the screenshot below.
Why is SQL Safe forcing me to use an auto-generated file name?
Upon enabling the Remove files older than X option, the SQL Safe Backup Policy Wizard automatically uses an auto-generated filename. Auto-generated files names use the following naming convention:
This is done so that the SQL Safe Backup Service (which has no connectivity to the SQL Safe repository database) can determine which backup files to delete when the policy is running. The SQL Safe Backup Service reviews the various parts of the filename to determine whether it's a candidate for deletion.
Let me give you a simple scenario to demonstrate what happens under the covers. In SQLsafe, I've configured the following in my SQL Safe Backup Policy.
- Instance: MySQLServer
- Databases: MyDB1
- Instance: MySQLServer
- \\BackupStorage\SQLBackups\<filename auto-generated>
- Remove files older than 7 days
- Weekly on Sunday at 12:00:00 AM
When the SQL Safe Backup Service is getting ready to delete files (which happens at the end of a successful backup operation), it gets a list of files in the location directory (e.g. \\BackupStorage\SQLBackups\ from my example above) that matches the SQL instance, database name, and type of backup that was just completed. Using my example above, the files that will be among the list of candidates for deletion will all begin with the following name:
Once the list of candidates has been determined, the SQL Safe Backup Service then checks the timestamp portion of the filename to determine. From the list of candidates, any files that have a timestamp that exceeds the time threshold will then be deleted. Let's assume that my backup directory currently has the following files:
- MySQLServer_MyDB1_Full_201701080200 (1 of 1).safe
- MySQLServer_MyDB2_Full_201701010000 (1 of 1).safe
When my policy runs on January 15, 2017 at 12:00 AM, it will first select the following files as candidates for deletion:
Notice that the files containing "(1 of 1)" in the filename have been excluded as it does not match the expected naming convention. Next, it further refines the list by reviewing the timestamps of the filenames leaving us with the following candidates:
Notice that MySQLServer_MyDB1_Full_201701080000.safe is not among the candidates anymore. That's because the file is exactly a 7 days old and not older than 7 days. This delineation is very important to understand as it can affect how many backups are kept by SQL Safe. I'll talk more about in the next section. Once SQLsafe has gotten this far, it then proceeds to delete the backup files.
TLDR, SQL Safe auto-generate filenames as it provides a means to identify which backup files are candidates for deletion.
Why wasn't my backup file(s) deleted?
There may be a number of different reasons why SQL Safe would fail to delete a fail per the defined schedule. I've listed the most common scenarios that can be encountered.
Backup was not successful
By design, SQL Safe does not delete any backup files unless the backup operation was successful. This is to ensure that users have an available backup set. We wouldn't want to leave you in a scenario where you needed to restore but all of your backups have been programmatically deleted!
Time threshold was not exceeded
Let's start with the scenario from the section above. To summarize, a backup operation kicks off on January 15, 2017 at 12:00 AM but it doesn't delete the MySQLServer_MyDB1_Full_201701080000.safe because it's not over 7 days old. Well, when you look at the filename, you may think January 8, 2017 is exactly a week before January 15, 2017. It is, but SQL Safe is looking for files OLDER than a certain time period. Given that the time period is exactly 7 days, SQL Safe's logic determines that the requirement has not been met and does not delete the file.
Scenarios such as this aren't encountered very often from my experience, however, it is possible. To avoid such scenarios, I configure my policies so that it deletes files older than X number of minutes. For example, 7 days is exactly 10,080 minutes. Rather than using the exact conversion, I'll use a slightly lower value such as 10,020 minutes (6 days and 23 hours).
File In Use
There's been a number of times where I've seen SQL Safe fail to delete a backup due to the fact that the file was in use. Identifying the process that was using the file isn't always easy, especially in scenarios where the duration of "use" was very short. I've found that tools such as Windows Sysinternals Process Monitor to be very useful when troubleshooting such scenarios. In most cases, it turns out to be some anti-virus software scanning the backup files, thus preventing SQL Safe from deleting the file successfully. Scenarios such as this can be easily avoided by adding the backup directory to the exclusion list within the anti-virus software.
Is there an alternative way to delete backup files?
The SQL Safe command line interface (CLI) does have an action that allows you to delete files. The action is named, predictably, DELETE. The basic syntax for the command is the following:
SQLsafeCmd DELETE <filename> [options]
For example, if I wanted to perform the same deletion schedule from the example above, I would use the following command:
SQLsafeCmd DELETE MySQLServer_MyDB1_Full_*.safe -age 7days
One very important detail regarding the -age option is that it does not check the timestamp in the filename when determining the age of the file. Instead, the -age option uses the Date Modified property when measuring the age of the file.
If you want to schedule an execution of this command, you could create a scheduled task through Windows Scheduler or create a SQL Server Agent job to run the command. This approach gives you more flexibility in regards to how your backup files are named. It does, however, add complexity in managing your backups. Now, you'll have to manage when your backup schedule and the deletion schedule.
Some of you may ask yourself, "why would I use this if I could simply use the DEL command in the command prompt?". The answer is fairly simply. When you perform the DELETE command through the SQLsafe CLI, the SQL Safe Backup Service will send a list of backup files that were deleted to the SQL Safe Management Service. Upon retrieving that list of backup files, the SQL Safe Management Service will then update the SQL Safe repository database indicating that those backup files have been deleted, keeping things in sync.
I hope the information that I provided allows for a better understanding of how SQL Safe deletes old backup files. With this knowledge, I hope it'll allow you to configure your backup policies to better manage the storage of your backup files.
If you would like to learn more about SQL Safe, please check the product page here!