SqlServer使用xp_cmdshell删除备份文件

Reconfigure;
GO
sp_configure 'xp_cmdshell', 1;
GO
Reconfigure;

If object_id('sp_DeleteBakFiles') Is Not Null
    Drop Proc sp_DeleteBakFiles
Go
Create Proc sp_DeleteBakFiles
(
    @FilePath           nvarchar(128), -- 数据库备份目录，如：c:\bak
    @SearchPattern      nvarchar(200), -- 本次需要删除的文件，如：*.bak或者*.*
    @LastWriteTimeStart datetime=null,      -- 修改文件开始时间，如：20240101
    @LastWriteTimeEnd   datetime=null       -- 修改文件结束时间，如：20241231  
)
As

/*************************************************************
程序说明：使用xp_cmdshell删除备份文件
SP所在数据库：master
作者：www.csframework.com
*************************************************************
-- 显示高级选项
Exec sp_configure 'show advanced options', 1;

--
-- 配置 xp_cmdshell ，允许执行cmd sheell
--
Reconfigure;
GO
sp_configure 'xp_cmdshell', 1;
GO
Reconfigure;

--------------------------------------------------------------
-- 测试：

Exec sp_DeleteBakFiles 'c:\bak','*.*'
Exec sp_DeleteBakFiles 'c:\bak','*.*','20240110','20241124'

**************************************************************/
Set Nocount On

DECLARE @Cmd nvarchar(2000),
        @OutputInfo nvarchar(2000),
        @Dir nvarchar(2000),
        @Date datetime,
        @FileName nvarchar(512),
		@days int

-- 默认删除多少天
set @days=7;

-- 没有指定删除日期，自动删除7天前的备份文件
if @LastWriteTimeStart is null AND @LastWriteTimeEnd is null
BEGIN
	set @LastWriteTimeStart='1900-01-01';
	set @LastWriteTimeEnd=GETDATE()-@days;
END;

--创建临时表
DECLARE @Tmp Table(ID int Identity(1,1) Primary Key, OutputInfo nvarchar(2000));

--查询目录下的文件，添加到临时表
Set @Cmd=N'Dir/A:-d/S/T:W/4 '+@FilePath+N'\'+Rtrim(@SearchPattern) /*Dos显示文件代码*/
Insert Into @Tmp Exec xp_cmdshell @Cmd;
-- 过滤只留目录和文件列表
SELECT * INTO #TMP111 FROM @TMP Where Patindex('%\%',OutputInfo)>0 Or IsDate(substring(OutputInfo,1,10))=1; 
-- SELECT * FROM #TMP111;

Declare Cur_dir Cursor For Select OutputInfo From #TMP111
Open Cur_dir
Fetch Next From Cur_dir Into @OutputInfo
While @@Fetch_Status = 0
Begin
    If Patindex('%\%',@OutputInfo)>0 /*提取目录*/
	BEGIN
        Set @Dir=Substring(@OutputInfo,1,Len(@OutputInfo)-Charindex(Char(32),Reverse(@OutputInfo)));
	END
    Else
    Begin
        Set @Date=Substring(@OutputInfo,1,10)
        If @Date Between @LastWriteTimeStart And @LastWriteTImeEnd
        Begin
            /*不同的环境，如在繁体系统，这里取文件名的处理方法可能不同*/
            Set @OutputInfo=Stuff(@OutputInfo,1,17,'') /*过滤掉日期部分*/
            Set @OutputInfo=Stuff(@OutputInfo,1,Patindex('%[0-9]%',@OutputInfo)-1,'') /*过滤掉前缀的空格部分*/
            Set @FileName=Stuff(@OutputInfo,1,Charindex(Char(32),@OutputInfo),'') /*取得文件名*/
            Set @Cmd=N'Del '+@Dir+N'\'+@FileName
            Exec xp_cmdshell @Cmd,No_output
            Print N'已删除文件：'+@Dir+N'\'+@FileName
        End
    End
    Fetch Next From Cur_dir Into @OutputInfo
End
Close Cur_dir
Deallocate Cur_dir
Go

Exec sp_DeleteBakFiles 'c:\bak','*.*','20240110','20241124'