I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.
One important item for any system administrator to manage is the removal of old files that aren’t useful. I know most of us hate to delete data, but there are log files, backups, and more that will clog up a drive over time if they’re not managed. I’ve had SQL Servers stop because old copies backups filled the disk and I’ve had IIS servers start throwing errors because 2 years worth of logs were on stored on the C drive.
Maintenance plans had a way to remove files and we have xp_delete_file, but there are limitations to ensure that only backup files are deleted. I think those are silly, but it wasn’t my decision to include restrictions, and I don’t get a vote on future changes.
In any case, dbatools has a cmdlet that can help: Remove-DbaBackup. I was interested to see if this worked on it’s own or had restrictions, but it seems to work wonderfully for me.
Required Parameters
Most cmdlets will allow quite a few parameters to be optional. In this case, however, there are some requirements. First, you need a path for the backup files. That makes sense and no big deal.
However, you also need a retention period. You can’t skip this, as if you do, you get a prompt.
The retention periods aren’t obvious, but not that hard to remember. There’s a numeric counter and a one character time period item. They are:
- h for hours
- d for days
- w for weeks
- m for months
That’s it and not a big deal, though for testing I need to play with my system clock a bit.
In any case, after this parameter, you need a backup extension. This is the file extension, without the period. You can put in anything, which is cool.
There are some other params, but not required.
For testing, I copied some backups and then changed some extensions. As you can see, my test folder has SQL backups with various extensions I’ve encountered as well as a few text documents.
If I run Remove-DbaBackup with some options, I’ll see what will happen with the –WhatIf parameter. I see plenty of files being marked for deletion as long as I have the right extension.
This is handy, and it makes perfect sense when you read it. This is exactly the type of maintenance job that you want to set up on a server to remove old files. I don’t know that I’d use this for general cleaning of files that I might need soon for a backup, since I always want to be sure that I have a good backup before I remove old files, but for managing very old files, this is helpful.
And, a little scripting logic would show you how to find the date of the most recent full backup and then remove files older than that. Or maybe older than the last two fulls.
Voice of the DBA 