DECLARE @delFileName NVARCHAR(200)
DECLARE @cmd VARCHAR(50)
DECLARE @saveDateNum INT
SET @saveDateNum=-60
WHILE @saveDateNum<-30
BEGIN
SET @delFileName = 'E:/back/Database/dbName_' + convert(varchar(10),dateadd(day,@saveDateNum,getdate()),112) + '.bak'
SET @cmd = 'del '+ @delFileName
EXEC master.dbo.xp_cmdshell @cmd
SET @saveDateNum=@saveDateNum+1
END
go
DECLARE @fileName NVARCHAR(200)
SET @fileName = 'E:/back/Database/dbName_' + convert(NVARCHAR(19),getdate(),112) + '.bak'
BACKUP DATABASE [dbName] TO DISK = @fileName WITH NOINIT, NOUNLOAD, NOSKIP, STATS = 10, NOFORMAT
====================
因为可能是每周备份，30天以前del,30-60删除
可根据需要修改以上数据
SQL2005 开启XP_CMDSHELL
EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;--
关闭一样.只是将上面的后面的那个"1"改成"0"就可以了.
;EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 0;RECONFIGURE;--
为安全可以换成
declare @d Nvarchar(64);
set @d = convert(varchar,dateadd(day,-5,getdate()),120);
EXECUTE master.dbo.xp_delete_file 0,N'D:/databack/GFSM',N'bak',@d;
EXECUTE master.dbo.xp_delete_file 0,N'D:/databack/GFSM_KEY',N'bak',@d