SQL SERVER 自动备份并且自动删除过期备份

 

use master
--获取完整备份文件名(每周日做完整备份，其它时间每天做差异备份)
declare @filename nvarchar(100)
set @filename=N'D:\ 数据库备份\TestDB'+convert(char(8),DATEADD(wk, DATEDIFF(wk,0,getdate()), -1),112)+'.bak'
print @filename

--完整备份
if(DatePart(dw,getdate())=1)
begin
BACKUP DATABASE [TestDB] TO DISK = @filename WITH NOINIT , NOUNLOAD , NAME = N'TestDB 备份', NOSKIP , STATS = 10, NOFORMAT
end
else
begin
--差异备份
BACKUP DATABASE [TestDB] TO DISK =@filename WITH DIFFERENTIAL
end

--删除30天前备份
EXEC xp_cmdshell ''FORFILES /p d:数据库备份 /s /m TestDB*.bak /d -30 /c "CMD /C del /Q /F @FILE"''
GO