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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



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

Leave a Comment


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