?

?

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

*/

?

?

?

轉載于:https://www.cnblogs.com/wghao/archive/2009/11/22/1608248.html

總結

以上是生活随笔為你收集整理的通过SQL存储过程删除过期的数据库Bak备份文件的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。