By: Jugal Shah | Updated: 2012-06-26 | Comments (14) | Related: > 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.
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"
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.
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.
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
About the author
Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2012-06-26
