sql server script to delete expired backup files

1. View expired files through scripts, and you can set the time yourself.

for /f "delims=" %i in ('dir /b /a-d "E:\mybak_file\*.bak" ^| findstr /i "backup" ^| findstr /v /i "no_backup"') do @if "%~ti" LSS "2024/09/29 16:50" echo %~ti NAME:%i

Looping through to view files smaller than the specified time point. %i is a file, and can also be output. Remember that the format of %~ti is Year/month/day Time: minute
If you want to compare, please %~ti is enclosed in double quotes, and the subsequent time string is also quoted!

2. Delete expired files

DECLARE @Path NVARCHAR(255) = 'E:\mybak_file\';  -- Backup file storage path
DECLARE @CutoffDate NVARCHAR(120) = REPLACE(CONVERT(VARCHAR(16), DATEADD(DAY, -14, GETDATE()), 120), '-', '/');
DECLARE @Cmd NVARCHAR(4000);

-- Delete backup files for more than two weeks
SET @Cmd = 'for /f "delims=" %i in (''dir /b /a-d "' + @Path + '*.bak" | findstr /i "backup" | findstr /v /i "no_backup" '') do @if "%~ti" LSS "' + CONVERT(VARCHAR, @CutoffDate, 120) + '" del "' + @Path + '%i"';

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

-- Execute your commands
EXEC xp_cmdshell @Cmd;

-- recover xp_cmdshell The status of（Optional）
EXEC sp_configure 'xp_cmdshell', 0;  
RECONFIGURE;

After executing here, it still failed to delete it successfully. Continue to modify the script! ! ! ! ! !

3. Problem resolution

The pipe characters in the command must be escaped, otherwise an error will be reported, and the error content will be as follows

There should not be | at this time.

Misuse of pipeline symbols (|): The T-SQL language of SQL Server itself does not support pipeline symbols (|). If you try to use it directly in a SQL query, this error may be caused. Pipeline symbols are often used in command line interfaces or other programming environments (such as PowerShell).

Error Command: This error may also be raised if a command you execute in SQL Server attempts to use SQL Server's features or options in an incorrect format.

It's really twists and turns, the commands are as follows:

for /f "delims=" %i in ('dir /b /a-d "E:\yunbak_file\*.bak" ^| findstr /i "backup" ^| findstr /v /i "no_backup" ') do @if "%~ti" LSS "2024/09/16 17:31" echo del "E:\yunbak_file\%i"

There is no problem with executing it directly in cmd, but using xp_cmdshell to execute it, it just doesn't work, and it will delete all files by mistake. I've been puzzled. Later, after multiple tests, I found that %~ti was not obtained at all.

Finally found a way to solve it. It was really not easy, but I was still not familiar with the commands. After trying it here many times, I found that the following commands can be solved perfectly! !

for /f "tokens=1,2,4 delims= " %i in ('dir /T:W "E:\yunbak_file\*.bak" ^| findstr /i "backup" ^| findstr /v /i "no_backup" ') do @if "%i %j" LSS "2024/09/16 17:54" echo del "E:\yunbak_file\%k"

4. Ultimate version

The following is a modified complete script to clean up the backup file (Ultimate version）：

DECLARE @Path NVARCHAR(255) = 'E:\yunbak_file\';  -- Backup file storage path
DECLARE @CutoffDate NVARCHAR(120) = REPLACE(CONVERT(VARCHAR(16), DATEADD(DAY, -14, GETDATE()), 120), '-', '/');
DECLARE @Cmd NVARCHAR(4000);

-- Delete backup files for more than two weeks

SET @Cmd = 'for /f "tokens=1,2,4 delims= " %i in (''dir /T:W "' + @Path + '*.bak" ^| findstr /i "backup" ^| findstr /v /i "no_backup" '') do @if "%i %j" LSS "' + CONVERT(VARCHAR, @CutoffDate, 120) + '" del "' + @Path + '%k"';

SELECT @Cmd;
-- Temporarily enabled xp_cmdshell
EXEC sp_configure 'show advanced options', 1;  
RECONFIGURE;  
EXEC sp_configure 'xp_cmdshell', 1;  
RECONFIGURE;


-- Execute your commands
EXEC xp_cmdshell @Cmd;

-- recover xp_cmdshell The status of（Optional）
EXEC sp_configure 'xp_cmdshell', 0;  
RECONFIGURE;

This is the article about the script for deleting expired backup files of SQL server. For more related contents of deleting expired files by SQL server, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!