One of the most important tasks for a database administrator is to backup all the databases for security reasons. As a Database administrator we need to make sure that in case of a disaster we will be able to recover our data from any point of time, that’s one of the reasons why every Database server should have a Backup maintenance plan. A common practice when implementing this kind of plans is to perform a backup into the server and then move the backup files to a storage server or copy the files in a tape. Once the backups have been moved or copied to the tape we need to purge the backup files that we still have in our server. It doesn’t make sense to keep all the backup files into the server when we already have moved those files to a safer place. So depending on the maintenance plan and business requirements we will want to maintain more or less historical backup files in our servers. In this post we will see how to do it easily with PowerShell.

Here is the source code:

   1: Function Delete-Files([string] $Path, [String] $Filefilter, [int] $DaysThreshold)
   2: {
   3:     get-ChildItem -Path $Path -Filter $Filefilter | where {$_.creationTime -lt (Get-Date).AddDays($DaysThreshold*(-1))} | remove-Item
   4: }
   5: Delete-Files -Path "C:tmp" -Filefilter "*.bak" -DaysThreshold 1
   6:

 

What we are doing is to create a function and call that function with 3 parameters:

-Path: The path where the files are located.

-FileFilter: The filter that we want to apply, in this case we are using “*.bak” to remove all full and differential backups, but if we want to remove log backups we could use “*.trn” instead.

-DaysThreshold: We specify how much history we want to keep. In this case we use 1 day, which means that all files older than 1 day from the moment that the code is running.

Now we are going to run it and test it. Let’s say that we have a folder with some backup files, for this demonstration we will use “C:tmp” as our directory.

We notice that there are two groups, the first one contains all those files created on 23th of January 2012 and the second one contains those files created on 24th of January 2012. So when we run our script to delete the files older than 1 day we get the following result:

Untitled2_thumb_395ABC62

In addition, one of the advantages of working with PowerShell is that we can include this PowerShell code in a SQL Server job. Let’s say that we have a job with one step, this step performs the backup. Now with this code we can add a second step deleting all files older than one, two or more days :).

In case you want to use this code in a job step you can run it as we showed it before in the code snippet or you can just run the following command for full and differential backups:

   1: get-ChildItem -Path "C:tmp" -Filter "*.bak" | where {$_.creationTime -lt (Get-Date).AddDays(-1)} | remove-Item

In case you want to delete log backups we only have to change the filter from *.bak” to *.trn” like follows:

   1: get-ChildItem -Path "C:tmp" -Filter "*.trn" | where {$_.creationTime -lt (Get-Date).AddDays(-1)} | remove-Item

Once again, PowerShell shows us its power!!! I hope this post will help you all.

Happy Scripting :)!!