SQL SERVER | How to Delete files older than N days | Advanced SQL

In database we require to delete the backup files older than N number of days from SQL SERVER using T-SQL. This script will help us in deleting the old backup file to free space from disk. With this we do not have to depend on the Manual intervention to delete the files. We can just use schedule the script using SQL jobs and the job is done. We can also get the sucess or failure of this job using SQL alerts.

To achieve this you need to use the help of XP_CMDSHELL feature in SQL Server.

For enabling the XP_CMDSHELL in SQL Server:-

--

Use Master
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO

--

Script to delete the backup files older than N number of days

--

EXEC XP_CMDSHELL 
	'Forfiles -p ''C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER2017\MSSQL\DATA'' 
	-s -m *.trc -d -5 -c ''Cmd /C del @FILE'''

--

Notes

-d -> No of Days. In this example the number of days are 5]

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com