Removing Old SQL Server backup files with PowerShell

PowerShellA common question that comes up during my PowerShell Restore presentation is on how to delete your old SQL Server backups after a certain period of time. This is especially important for those DBAs who are looking after SQL Express installs who don’t have the luxury of SQL Server Agent and Maintenance Wizards to configure it for them.

To delete the files we’ll just be using some basic PowerShell cmdlets and piping them together. ¬†For this example we’ll assume that your backups are in D:\SQLBackups\, and that each database’s backups are in separate folders, that you are correctly using .bak and .trn , and you want to delete everything over 5 days.

First we use Get-ChildItem to get a list of all the files under our backup folder:

Get-ChildItem d:\SQLBackups -recurse -include *.trn,*.bak -file

This get’s Get-ChildItem to recursively list all bak and trn files under your backup folder. We use the -file switch to make sure we only get files, not folders. Unfortunately -File is only available in PowerShell 3 and above, so if you want do something similar and are on PowerShell 2 then you’d need to incorporate an extra filtering stage.

Next we use Where-Object</> to filter the files down to those over 5 days old. We'll go this by using the LastWriteTime property of the File object, and as we're piping in the output from another cmdlet we'll use the $_ placeholder to indicate that we're working with the passed object:

Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-5)}

As mentioned before, if you’re on PowerShell 2 and only wanted to delete old files, not folders, then you’d extend this filter like so:

Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-5) -and $_.PSIsContainer -eq $False}

Now we’ve got the filtered list of file objects we want to delete, we can just pipe them into Remove-Item:

Get-ChildItem d:\SQLBackups -recurse -include *.trn,*.bak -file | Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-5)} | Remove-Item

or rewriting using backticks(`) to split lines:

Get-ChildItem d:\SQLBackups -recurse -include *.trn,*.bak -file `
   | Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-5)} `
   | Remove-Item

This can either be run as a separate scheduled job, or added to the end of your PowerShell backup script.

If you are appending it to the end of the you Backup script, then putting the following line before starting will make sure you’re running on the filesystem drive rather than SQLSERVER:,

Set-Location c:\

And of course being PowerShell we can quickly adapt this bit of script for other purposes. How about it you don’t want to delete the file after 5 days, but instead want to archive them to bulk long term storage. Then we just replace the final cmdlet with Move-Item:

Get-ChildItem d:\SQLBackups -recurse -include *.trn,*.bak -file `
   | Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-5)} `
   | Move-Item $_ \\ArchiveServer\SQLbackups\

I hope you find that useful, and please get in touch if there’s something that doesn’t make sense. And I’d like to hear of other ways you may end up using this as well.

Update:
In response to Terry’s request in the comments, here’s a version that will delete full backups (.bak) files older than x days, and then also clean up all the orphaned transaction log backups (.trn). Due to needing to work out which database we’re talking about, this only works in a single folder! Not recursively like the above examples. This is because we need to get the age of the oldest existing full backup, and that will be different to every folder.

Get-ChildItem d:\sqlbackups\database1 -include *.bak -file `
    | Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-5)} `
    | Remove-Item
$OldestFullBackup = Get-ChildItem d:\sqlbackups\database1 -include *.bak -file `
    | Sort-Object -Property LastWriteTime `
    | Select-Object -first 1
Get-ChildItem d:\sqlbackups\database1 -include *.trn-file `
    | Where-Object {$_.LastWriteTime -lt ($OldestFullBackup.LastWriteTime) `
    | Remove-Item

So we delete the Full backups we want to remove. Then we find the oldest full backup file, and delete all the t-log files older than that

7 thoughts on “Removing Old SQL Server backup files with PowerShell

  1. Yaniv Etrogi says:

    Hi Stuart,
    I have used your example successfully until deployed in an older server where the ps version was lower.
    Can you send a ps code that will also work on lower ps versions?

    Thanks
    -Yaniv

    • Hi Yaniv,
      What version of PS are you using?

      Cheers
      Stuart

      • Yaniv says:

        Hi,
        Actually there are 2 cases;
        In the first case version 2
        In the second case version 4 and the ps script works when I run it from the ps ISE but the issue I face is that I used that ps code in an SQL Server Agent job step of type power shell and there it fails withe following error:

        A job step received an error at line 1 in a PowerShell script. The corresponding line is ‘Get-ChildItem \\server01\Backup\server02\ -recurse -include *.bak -file | Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-7)} | Remove-Item ‘. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘A parameter cannot be found that matches parameter name ‘file’. ‘. Process Exit Code -1. The step failed.

        Thank you
        -Yaniv

        • This version should work with version 2:
          Get-ChildItem d:\SQLBackups -recurse -include *.trn,*.bak | Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-5) -and $_.PSIsContainer -eq $False} | Remove-Item

          The -file option was added to gci later, so you have to filter in the Where-Object clause.

          I think that the 2nd case is down to the fact that the SQL Agent job is running on an older version of PS than your ISE session. So hopefully using the above syntax will also fix that.

          Let me know if that works for you. At SQLBits atm, so don’t have a test system to hand.

  2. Terry says:

    Almost what I am looking for. Very nicely and clearly written.

    Thing is the trn files normally need the bak file that precedes them to allow a restore. So if you run your full .bak on day1 and .trn ‘s run the next day, then in your scenario the delete might delete the .bak but keep the .trn files that go with the .bak file and they will be useless.

    Therefore the script should ideally be modified to delete all .bak files before X and then delete all .trn files that are older than the oldest .bak file

Leave a Reply

Your email address will not be published. Required fields are marked *