How to deleting backup files from a folder using SQL Server ?

Deepak M

Hello everyone,

We maintain a database with a significant volume of daily transactions. To ensure data integrity and recovery options, we regularly create differential backups each day and transaction log backups every 15 minutes. However, the continuous growth of our backup folder poses a challenge, making it impractical to manually delete older backup files.

To tackle this concern, we need a SQL Server script for deleting backup files from a folder that exceed a 30-day.

Can any one help?

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty3y ago

Accepted answer

DECLARE @DaysToKeep INT = 30;
DECLARE @FolderPath NVARCHAR(255) = 'D:\Backups'

-- Enable xp_cmdshell
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

DECLARE @Cmd NVARCHAR(4000);
SET @Cmd = 'FORFILES /P "' + @FolderPath + '" /S /M * /D -' + CAST(@DaysToKeep AS NVARCHAR(3)) + ' /C "CMD /C DEL @FILE"';
EXEC xp_cmdshell @Cmd;