删除数据库备份文件存储过程：p_DeleteBakFiles

作者:C/S框架网|www.cscode.ne 发布日期:2021/02/07 16:39:22

删除数据库备份文件存储过程：p_DeleteBakFiles

SQL Code:

ALTER procedure p_DeleteBakFiles
@Days int --天数，7：删除7天前的备份文件
as
begin

/************************************************************

删除数据库备份文件

SELECT * FROM sys_BackupHistory

p_DeleteBakFiles 1
p_DeleteBakFiles 0

*************************************************************/
DECLARE @CMD VARCHAR(1000)

--exec sp_configure 'show advanced options', 1;
reconfigure;
exec sp_configure 'xp_cmdshell', 1; ---1代表开启 xp_cmdshell 0代表停用xp_cmdshell
reconfigure;

declare @name varchar(500)
declare cursor1 cursor for --定义游标cursor1
SELECT BackupPath FROM sys_BackupHistory where GETDATE()-BackupTime>@Days
open cursor1 --打开游标
fetch next from cursor1 into @name --将游标向下移1行，获取的数据放入之前定义的变量@id,@name中
while @@fetch_status=0 --判断是否成功获取数据
begin
print @name
SELECT @CMD='del '+@name --组合命令
exec master.dbo.xp_cmdshell @CMD --删除文件
delete sys_BackupHistory where BackupPath=@name --删除备份记录
fetch next from cursor1 into @name --将游标向下移1行
end

close cursor1 --关闭游标
deallocate cursor1

end;

//来源:C/S框架网 | www.csframework.com | QQ:23404761

参考文档：