|
Posted on
2008-07-17 17:06
AmyQiu
阅读( 312)
评论()
编辑
收藏
举报
DECLARE @sql varchar(4000)
declare @backupfile varchar(2000)
declare @now datetime
declare @retaindays int
declare @deletefiles varchar(2000)
declare @cmd varchar(2000)
declare @i int
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
set @retaindays=14
set @now=getdate()
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
set @i=1
while (@i < 30)
begin
set @deletefiles = '\\IP\DBBackup\FolderName\*'+convert(varchar(8),dateadd(dd,-@retaindays-@i,@now),112)+'*.*'
set @cmd='del ' + @deletefiles
exec master..xp_cmdshell @cmd
set @i = @i +1
end
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
set @backupfile='\\IP\DBBackup\FolderName\?_db_'+
convert(char(8),@now,112)+replace(convert(varchar(20),@now,108),':','')+
'.BAK'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
set @sql='if ''?''<>''tempdb'' backup database ? to disk='''+@backupfile+''' with retaindays='+convert(varchar(10),@retaindays)
exec sp_msforeachdb @command1=@sql
|