THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

PowerShell Script to Remove Old Backups

A Twitter user asked for a script to remove old backups, and I had one I built back in February, so here it is (use at your own risk):

# Removes database and transaction log backups
# Change log:
# February 2, 2010: Allen White
#   Initial Version

# Get the SQL Server instance name from the command line

# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$v = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | out-null

# Handle any errors that occur
Function Error_Handler {
  Write-Host "Error Category: " + $error[0].CategoryInfo.Category 
  Write-Host " Error Object: " + $error[0].TargetObject 
  Write-Host " Error Message: " + $error[0].Exception.Message 
  Write-Host " Error Message: " + $error[0].FullyQualifiedErrorId 
Trap {
  # Handle the error
  # End the script.

# Connect to the specified instance
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst

# Get the directory where backups are stored
$bkdir = $s.Settings.BackupDirectory
$retdays = $days * -1

# Delete the backup files
Get-ChildItem $bkdir -recurse -include *.bak | Where {($_.CreationTime -le $(Get-Date).AddDays($retdays))} | Remove-Item -Force

# Delete the transaction log files
Get-ChildItem $bkdir -recurse -include *.trn | Where {($_.CreationTime -le $(Get-Date).AddDays($retdays))} | Remove-Item -Force


Published Thursday, June 24, 2010 1:46 PM by AllenMWhite



Chad Miller said:


Here's an alternative approach which I'm using in my production environment. This code was written by former co-worker and includes the SQL Agent job creation script:

June 28, 2010 10:26 AM

Toby Worth said:

Thanks for that, just what I wanted!

December 22, 2013 1:33 PM
New Comments to this post are disabled

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog


Privacy Statement