sql server删除过期备份文件脚本

一、通过脚本查看过期文件，时间可以自己设定

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

循环遍历查看小于指定时间点的文件 %i 是文件，也可以输出，切记 %~ti 的格式是 年/月/日 时:分
如果要对比的话要吧%~ti用双引号引起来，后面的时间字符串也引用起来！

二、删除过期文件

DECLARE @Path NVARCHAR(255) = 'E:\mybak_file\';  -- 备份文件存储路径
DECLARE @CutoffDate NVARCHAR(120) = REPLACE(CONVERT(VARCHAR(16), DATEADD(DAY, -14, GETDATE()), 120), '-', '/');
DECLARE @Cmd NVARCHAR(4000);-- 删除超过两周的备份文件
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"';-- 临时启用 xp_cmdshell
EXEC sp_configure 'show advanced options', 1;  
RECONFIGURE;  
EXEC sp_configure 'xp_cmdshell', 1;  
RECONFIGURE;-- 执行您的命令
EXEC xp_cmdshell @Cmd;-- 恢复 xp_cmdshell 的状态（可选）
EXEC sp_configure 'xp_cmdshell', 0;  
RECONFIGURE;

这里执行之后还是没有成功删除，继续修改脚本！！！！！！

三、问题解决

命令里面的管道符，必须要转义，否则会报错，报错内容如下

此时不应有 |。管道符号（|）的误用: SQL Server 的 T-SQL 语言本身并不支持管道符号（|）。如果您尝试在 SQL 查询中直接使用它，可能会导致该错误。管道符号通常用于命令行界面或其他编程环境（例如 PowerShell）。错误的命令: 如果您在 SQL Server 中执行的命令尝试以不正确的格式使用 SQL Server 的功能或选项，可能也会引发此错误。

真是一波三折啊，命令拼好如下：

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"

直接在cmd执行一点问题没有，但是用xp_cmdshell 执行，就是不行，还会误删所有文件，一直百思不得其解，后来多种测试以后发现是%~ti压根没有获取到。

终于找到方法解决了，真是太不容易了，不过还是对命令不熟悉，这里尝试多次后发现以下命令可以完美解决！！

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"

四、终极版本

以下是修改后的完整的清理备份文件的脚本（终极版本）：

DECLARE @Path NVARCHAR(255) = 'E:\yunbak_file\';  -- 备份文件存储路径
DECLARE @CutoffDate NVARCHAR(120) = REPLACE(CONVERT(VARCHAR(16), DATEADD(DAY, -14, GETDATE()), 120), '-', '/');
DECLARE @Cmd NVARCHAR(4000);-- 删除超过两周的备份文件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;
-- 临时启用 xp_cmdshell
EXEC sp_configure 'show advanced options', 1;  
RECONFIGURE;  
EXEC sp_configure 'xp_cmdshell', 1;  
RECONFIGURE;-- 执行您的命令
EXEC xp_cmdshell @Cmd;-- 恢复 xp_cmdshell 的状态（可选）
EXEC sp_configure 'xp_cmdshell', 0;  
RECONFIGURE;