Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Using a PowerShell Script to delete old files for SQL Server

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

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.

Solution

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
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

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
        }
    else
        {
        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
Comments and Feedback:
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


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.


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!


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.


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

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


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 - 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, 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

 


 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.