--开启修改配置功能 exec sp_configure 'allow updates', 0
--启用功能 sp_configure 'xp_cmdshell',1; reconfigure
--临时表 create table #temp (filesname varchar (5000 ), d int , f int ) --备份路径(需要将此文件夹的权限放开) declare @path varchar(1000) set @path='D:\DWDBBackup' --文件后缀名 declare @extent varchar(1000) set @extent='abf'
--获取所有文件 insert into #temp Exec master..xp_dirtree @path,1,1
select * into #temp1 from #temp where filesname like '%.'+@extent+'%' order by filesname desc
select * into #temp2 from #temp1 where filesname not in ( select top 3 filesname from #temp1 order by filesname desc--保留3个文件 ) declare @count int select @count=count(1) from #temp2
while @count>0 begin declare @filename varchar(5000) select top 1 @filename=filesname from #temp2 declare @sql varchar(5000) select @sql='Del '+@path+'\'+@filename Exec master..xp_cmdshell @sql delete from #temp2 where filesname=@filename print @filename select @count=count(1) from #temp2 end
drop table #temp drop table #temp1 drop table #temp2