sql server 用DOS 批处理 自动 定期删除 备份文件 包括完整备份和差异备份

系统创建了自动备份后,需要经常清理旧的备份以释放硬盘空间.

--创建自动删除备份文件过程,用个作业调度就省事了.

set nocount on
declare @str varchar(100),
        @dir varchar(100),
@date varchar(10),
@Old int,
@cmdstr varchar(5000)


select @old=20,
  @cmdstr=''
set @dir='D:\databak\'


 exec xp_cmdshell 'del d:\databak\clear.bat'
while(@old>15) --保留天数
begin


set  @date=replace(convert(varchar(10),getdate()-@old,120),'-','')
--删除完整备份
set @str='del '+@dir+@date+'FULLBAK\'+'*.bak' 
set @cmdstr='echo ' +@str+' >>d:\databak\clear.bat'
exec xp_cmdshell @cmdstr


set @str='RD '+@dir+@date+'FULLBAK' 
set @cmdstr='echo ' +@str+' >>d:\databak\clear.bat'
exec xp_cmdshell @cmdstr


set @cmdstr=@cmdstr+@str
--删除差异备份
set @str='del '+@dir+@date+'DIFF\'+'*.bak' 
set @cmdstr='echo ' +@str+' >>d:\databak\clear.bat'
exec xp_cmdshell @cmdstr


set @str='RD '+@dir+@date+'DIFF' 
set @cmdstr='echo ' +@str+' >>d:\databak\clear.bat'
exec xp_cmdshell @cmdstr


set @old=@old-1
end
--执行清理
 exec xp_cmdshell 'd:\databak\clear.bat'

---附常用清理.

DECLARE @DeleteBeforeDate DateTime
SELECT @DeleteBeforeDate = DATEADD(d,-30, GETDATE()) --一个月前的
EXEC msdb..sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate --删除邮件记录
EXEC msdb..sysmail_delete_log_sp @logged_before = @DeleteBeforeDate  --删除邮件日志
exec msdb..sp_delete_backuphistory @oldest_date=@DeleteBeforeDate  --删除备份日志