Customizing Remove-DbaBackup from dbatools

Howdy!

As a matter fact, it is not new that dbatools is a great PowerShell module for the SQL Server DBA, and from time to time they come up with new functions that for me I just think: Why I never thought about it before?

Today I want to talk about the function Remove-DbaBackup and more specifically my customized version of it. No, I didn’t rewrite Remove-DbaBackup but rather improved to my environment. The function by itself is just great and it addresses the goal of removing the backups nicely, but at the end, the function was not doing something that I wanted.

My challenge

We leverage Ola Hallengren for all of our SQL Backups, and that addresses backups of Production, Development and testing environments. All of those backups are sent to the same file share in our environment. However, each environment offers a different backup retention policy.

Now, imagine that we have a bunch of servers doing backups to the same file share address, but they have different retentions, and we just go ahead and execute Remove-DbaBackup to the root folder setup in the backup jobs.

I think you guessed right, we would end up deleting backups that were not supposed to be deleted.

If you go to my blog post where I talk about Automating your SQL Server Restore Tests with PowerShell, dbatools and PowerBi, you will see that I have a database where I store the information regarding my environment. In this database, I have a table that may contain the following information.

• Server Name
• Environment

Having those two pieces of information, I can easily develop a script that can wisely delete the old backups based on the environment. Having the environment information I know what is the retention period for it, and that is where it comes to my customization.

Creating the Repository

To create the Repository where you can store the server information, you can use this script.

https://github.com/marcosfreccia/sql-server-restore-tests-automation/blob/master/DBARepository.sql

Remove-SqlBackups

A simple execution of the script would be something like.

Remove-SQLBackups -RootBackupShare “F:\Backup” -ProdRentention 10d -DevTestRentention 3d -RepositoryServer ‘localhost’ -RepositoryDB DBARepository

Simply connect to this server and database, retrieve the servers and their roles and delete the backups accordingly. For any production servers that the script finds, remove both (.bak and .trn) older than 10 days, and for any Development or Test environment, remove both (.bak and .trn) older than 3 days.

The cool thing about this script is that also recognizes SQL Server Availability Groups and also applies the correct retention policy. So, if you have a mixed group of servers (standalone and Availability Groups) and you have that in your inventory, the script will handle.

However, one thing in advance that I can tell you is: This script doesn’t work if you have more than one Availability Groups in your server. As I don’t have this situation, I could not test it. If you do, feel free to change the script to adapt to your needs.

Source: https://gist.github.com/marcosfreccia/1f5d8f9e6d1ed5f7bb23e2147dc9cbbe

I’ve been using this script for more than a month and I have not had any problems with it, but of course, if you plan to use it in your environment, test it enough to make sure you get the results that you are expecting.

Let me know if you like this solution! Feedback is always welcome!

Marcos Freccia
Data Platform MVP