Learn more about SQL Server tools


Latest SQL Server Tips

Free SQL Server Learning

SQL Server Audit with SQL Compliance Manager and SQL Secure

How to Roll your Own Value, RegEx and SoundEx Pattern Profiler in SSIS

Are You Making the Right Choices for SQL Server HA?

SQL Server Security Essentials

Implementing a SANLess SQL Server Cluster in Under an Hour

Using a PowerShell Script to delete old files for SQL Server

MSSQLTips author Jugal Shah By:   |   Read Comments (10)   |   Related Tips: More > PowerShell

Many clients are using custom stored procedures or third party tools to backup databases in production environments instead of using database maintenance plans. One of the things that you need to do is to maintain the number of backup files that exist on disk, so you don't run out of disk space.  There are several techniques for deleting old files, but in this tip I show how this can be done using PowerShell.


While there are many processes for file deletion that you can use in SQL Server, I will show you how this can be done using PowerShell. 

PowerShell is a powerful scripting tool which allows administrators and developers to automate server administration and application deployment. PowerShell scripts support more complex logic than Transact-SQL scripts, giving SQL Server administrators the ability to build robust administration and automation scripts.

Below are some of the most commonly used processes to delete older backup files:

Here I will show you two different options to run a PowerShell script which will delete files based on how old the file is based on days.

Note: to delete files from the backup share/folder the account must have read, write and delete subfolders/files permission.

Option 1 - Using SSMS / T-SQL

The main advantage of using a PowerShell script is that we can easily execute it from within SSMS or any stored procedure and it doesn't require that the script be deployed on a server. 

To run a PowerShell script using SSMS or from a stored procedure XP_CMDSHELL must be enabled. You can enable it using the below script.

--Script to enable the XP_CMDSHELL
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
-- To update the currently configured value for advanced options.
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
-- To update the currently configured value for this feature.

In the below script, you have to pass the below values:

  • targetpath - here you have to pass the full path of the file location which you want to delete.
  • olddays - delete files which are older than days. For example if you want to delete files older than 5 days, pass -5
  • extension - extension of the files you want to delete
--Script to delete the old files
DECLARE @pscmd varchar(1000)
DECLARE @targetpath varchar(8000)
DECLARE @olddays int
DECLARE @extension varchar(5)
DECLARE @cmdstring varchar(1000)
--assigning value to parameters, you can customize as per your need
SET @targetpath = 'D:\Dtest\'
SET @olddays = -5 --pass the days with negative values 
SET @extension = 'txt'
SET @pscmd = '"& '+
'Get-ChildItem ' + Quotename(@targetpath,'''') + ' | '+
'where {$_.lastWriteTime -lt ((Get-Date).AddDays('+ CAST(@olddays as varchar) +')) 
-and ($_.Extension -match ' + QUOTENAME(@extension,'''')+ ') } | ' +
'Remove-Item  -force " '
SET @cmdstring = ' ""powershell.exe" '+ @pscmd
exec master..xp_cmdshell @cmdstring

You can schedule a job by just pasting the code above into job step. Make sure SQLAgent Service account should have Read, Write and Delete permission on the folder.

Create SQL Agent to run PowerShell script

Option 2 - Create and Deploy a PowerShell Script File

Copy the below script and save it as "deleleoldfiles.ps1". For my test I am saving it to the "D:\" folder.

#----- declare parameters -----#
param([string]$targetpath=$(Throw `
"Parameter missing: -targetpath targetpath"),`
[int]$days=$(Throw "Parameter missing: `
-days days"), `
[string]$Extension=$(Throw "Parameter missing: -Extension Extension"))

$Now = Get-Date
$LastWrite = $Now.AddDays(-$days)
#----- get files based on lastwrite filter in the specified folder ---#
#----- Remove the write-host line from the below code if you want to schedule it using SQL Agent Job ---#
$Files = Get-Childitem $targetpath -Include $Extension -Recurse | Where {$_.LastWriteTime -le "$LastWrite"}
foreach ($File in $Files)
    if ($File -ne $NULL)
        write-host "Deleting File $File" -ForegroundColor "Red"
        Remove-Item $File.FullName | out-null
        Write-Host "No files to delete!" -foregroundcolor "blue"

You have to pass the below parameters to the PowerShell script.

  • targetpath - Here you have to pass the full path of the file location which you want to delete.
  • days - delete files which are older than days.
  • Extension - extension of the files you want to delete.
SL "D:\PowershellScript"
.\deleteoldfiles.ps1 -targetpath "D:\SQLMonitor\SQLMon\DelFiletest" -days "3" 
-Extension "*.txt"

PowerShell Console

You can schedule a SQL Agent Job for the above option. If you want to use the script file option you have to remove the Write-Host section from the script file.

PowerShell Console

Option 3 -  SQL Agent Job with PowerShell Script

You can also run a PowerShell script directly from a SQL Agent Job without deploying a script file. I have changed the code a little bit, instead of passing parameters as in the script file, I am assigning values.

#----enter path---#
$targetpath = "C:\jspace\"
#----enter the days---#
$days = 5
#----extension of the file to delete---#
$Extension = "*.txt"
$Now = Get-Date
$LastWrite = $Now.AddDays(-$days)
#----- get files based on lastwrite filter in the specified folder ---#
$Files = Get-Childitem $targetpath -Include $Extension -Recurse | Where 
{$_.LastWriteTime -le "$LastWrite"}
foreach ($File in $Files)
    if ($File -ne $NULL)
        Remove-Item $File.FullName | out-null

You can schdule the above code directly in a SQL Agent Job step without deploying a script file.

PowerShell script to delete old backup files
Next Steps
  • Check the PowerShell script execution policy
  • You can also create backups using a PowerShell script
  • Add the file deletion step to your backup jobs to ensure you don't run out of disk space
  • Set this up to remove old "bak", "trn" and "txt" files

Last Update: 6/26/2012

About the author
MSSQLTips author Jugal Shah
Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

View all my tips
Related Resources

print tip Print  
Become a paid author

join MSSQLTips for free SQL Server tips     

Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates

       Note: your email address is not published. Required fields are marked with an asterisk (*)

Get free SQL tips:

*Enter Code refresh code     

Thursday, September 25, 2014 - 2:36:26 PM - Charles Herrington Read The Tip

Im working on a similar script but the issue im having is that, in the event of a backup failing on a particular database, how do i get the script to not delete the only good backups I have.

Friday, September 19, 2014 - 4:01:02 AM - Muhammad Tahir Read The Tip

this showing these results. not working :(



Missing closing '}' in statement block.

At line:1 char:89

+ & Get-ChildItem 'D:\New folder\' | where {$_.lastWriteTime -lt ((Get-Date).Ad

dDays(-5))  <<<< 

    + CategoryInfo          : ParserError: (CloseBraceToken:TokenId) [], Paren 


    + FullyQualifiedErrorId : MissingEndCurlyBrace



Monday, March 04, 2013 - 9:33:10 AM - Rohan Sawant Read The Tip

i am using following sql code to delete old backup files.


EXECUTE master.dbo.xp_delete_file 0,N'D:\DB_Backups\Backup',N'bak',N'2013-03-03T16:28:35',1



Monday, July 02, 2012 - 4:58:29 AM - Jason Coombes Read The Tip

PowerShell is a great tool for carrying out many DBA tasks and I use it when ever I can so great to see it getting promoted like this. I'd just like to add a word of caution with this script in that if your backups stop working for longer than the number of days specified you could find all the existing backups removed and then you have no DR.  This shouldn't be a problem because we check our backups are working every day right? ;-)

I have written a similar script to this but it uses MSDB to obtain backup paths for the specified database and will also check to make sure there is at least one full backup remaining on disk for that database.  This means that if the script detects that there isn't at least one backup that is more recent than the number of days it stops. I also have an associated script for managing transaction log backups and again this only removes the TLOG backup files that are older than the specified number of days but also only if they are older than the last full backup.

I'm not trying to criticise Jugal here but I wanted to just add a note of caution because PowerShell is a powerful tool and with great power comes great respoonsibility. :-)

Monday, July 02, 2012 - 2:45:13 AM - Andrey Sribnyak Read The Tip
#can easily #----enter path---# $targetpath = "C:\1\" #----enter the days---# $days = 5 #----extension of the file to delete---# $Extension = "*.txt" $Now = Get-Date dir $targetpath$Extension|?{$_.CreationTime -lt (Get-Date).adddays(-$days)}|rm

Monday, July 02, 2012 - 12:37:49 AM - nitin shinde Read The Tip

Thank you very much. This article is very helpful for me.

Friday, June 29, 2012 - 7:34:25 PM - TimothyAWiseman Read The Tip

Great article.  It might be worth pointing out that enabling XP_cmdshell support can increase the attack surface area for the system and is occassionally seen as a security risk.  It is often a risk worth taking and I frequently enable it myself, but the security factor should still be considered.

Thursday, June 28, 2012 - 1:14:23 PM - msenkogeek Read The Tip

Thank you, this was very helpful.  I am new to PowerShell and was able to get Option 2 to work and help solve one of my problems!

Thursday, June 28, 2012 - 10:49:19 AM - madhu Read The Tip


Indeed it is my frist PS article reading, good to know many things about the PS. Thanks for the post.

Tuesday, June 26, 2012 - 5:38:02 PM - Ameena Read The Tip

Nice Article. I used Option 3 today in one of my job steps to delete older file group and it works great. Thanks

Sponsor Information