I’ve noticed there are quite some hits on this post and the old format of the T-SQL code looked a bit messy, so I’ve done a makeover of the script on 2014-04-10.
This script requires a not-too-strict security setting for sql server’s service account, because it will have to execute Windows batch commands to achieve the goals of removing older backups.
/* Script to delete older than N days backup from a specific directory Author: Nick Xu Date: 2014-04-10 Applied version: 2000, 2005+ */ DECLARE @N AS VARCHAR(2) -- days for retention DECLARE @path AS VARCHAR(128) -- the path for deletion DECLARE @cmd AS VARCHAR(512) -- the actually command SET @N = '3' -- change the days here, remember it is type VARCHAR SET @path = 'C:\backups' -- ending back slash is not necessary SET @cmd = 'forfiles /P "' + @path + '" /S /M *.bak /D -' + @N + ' /C "cmd /c del @PATH"' EXEC master.dbo.xp_cmdshell @cmd /* The following part is for version 2005+. You will need the following to enable xp_cmdshell in SQL Server to let service account EXECute windows cmd's. But you might have to work with your angry security officer to get this done. */ EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sp_configure 'xp_cmdshell', 1 GO RECONFIGURE GO