SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Remove-DbaBackup with dbatools

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.

2018-04-10 17_29_56-cmd - powershell

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.

2018-04-10 17_27_38-Copies

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.

2018-04-10 19_37_32-cmd - powershell

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.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...