THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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):

#rmbackup.ps1
# 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
param(
  [string]$inst=$null,
  [int]$days=$null
  )

# 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
  Error_Handler;
  # End the script.
  break
  }

# 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

Allen

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

Comments

 

Chad Miller said:

Allen,

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:

http://sqlpowershell.blogspot.com/2009/03/proactive-file-deletion.html

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

(required) 
(required) 
Submit

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

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement