通过SQL存储过程删除过期的数据库Bak备份文件

最新推荐文章于 2024-10-14 17:36:50 发布

原创最新推荐文章于 2024-10-14 17:36:50 发布 · 593 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#存储 #数据库 #sql #xp #cmd #dos

SQL语句专栏收录该内容

59 篇文章

订阅专栏

本文介绍如何在SQL Server中通过启用xp_cmdshell扩展存储过程来创建一个存储过程，用于删除指定路径下符合特定条件的文件。该过程包括启用扩展存储过程、创建删除文件的存储过程及测试。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.先启用 xp_cmdshell 扩展存储过程：

Use Master
GO
Exec sp_configure 'show advanced options', 1
GO
Reconfigure;
GO
sp_configure 'xp_cmdshell', 1
GO
Reconfigure;
GO

(注：因为xp_cmdshell是高级选项，所以这里启动xp_cmdshell，需要先将 show advanced option 设置为 1，便可显示高级配置选项。

可以通过语句

Select is_advanced From sys.configurations Where name=N'xp_cmdshell'

查看是否高级选项。

)

2.删除文件的存储过程：

If object_id('sp_DeleteFiles') Is Not Null
    Drop Proc sp_DeleteFiles
Go
Create Proc sp_DeleteFiles
(
    @FilePath            nvarchar(128),
    @SearchPattern        nvarchar(200),
    @LastWriteTimeStart datetime,
    @LastWriteTImeEnd    datetime
)
As
Set Nocount On
Declare @Cmd nvarchar(2000),
        @OutputInfo nvarchar(2000),
        @Dir nvarchar(2000),
        @Date datetime,
        @FileName nvarchar(512)

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

Declare Cur_dir Cursor For
    Select OutputInfo From @tmp  Where Patindex('%/%',OutputInfo)>0 Or IsDate(substring(OutputInfo,1,10))=1 /*过滤只留目录和文件列表*/
Open Cur_dir
Fetch Next From Cur_dir Into @OutputInfo
While @@Fetch_Status = 0
Begin
    If Patindex('%/%',@OutputInfo)>0 /*提取目录*/
        Set @Dir=Substring(@OutputInfo,1,Len(@OutputInfo)-Charindex(Char(32),Reverse(@OutputInfo)))
    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

3. 测试：

Exec sp_DeleteFiles 'F:/test','*.exe','20011001','20091119'

/*
已删除文件： F:/test/Gao/高2009-8-14/B2000HR_FuXing_CHN_060406/HR_FuXing_071101.exe
已删除文件： F:/test/Gao/高2009-8-14/B2000HR_FuXing_CHN_060406/HR_FuXing_080127.exe
已删除文件： F:/test/Gao/高2009-8-14/B2000HR_FuXing_CHN_060406/HR_FuXing_080326.exe
已删除文件： F:/test/Gao/高2009-8-14/B2000HR_FuXing_CHN_060406/HR_FuXing_080328.exe
已删除文件： F:/test/Gao/高2009-8-14/B2000HR_FuXing_CHN_060406/HR_FuXing_080504.exe
已删除文件： F:/test/Gao/高2009-8-14/B2000HR_FuXing_CHN_060406/HR_FuXing_080628.exe

*/