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.
[sourcecode language=”sql”]
/*
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
[/sourcecode]
Leave a Reply