--备份
DECLARE @strPath NVARCHAR(200)
DECLARE @dbName NVARCHAR(200)
SET @strPath = Convert(NVARCHAR(10),Getdate(),120)
SET @dbName = 'decorvilla'
SET @strPath = 'K:\everyday\'
+ @dbName
+ '_'
+ @strPath
+ '.bak'
--print @strPath
--删除7天前的备份
BACKUP DATABASE @dbName TO DISK = @strPath WITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10, noformat
DECLARE @strDel NVARCHAR(200)
SET @strDel = Convert(NVARCHAR(10),DATEADD(day, -7, getdate()),120)
SET @strDel = 'DEL K:\everyday\'
+ @dbName
+ '_'
+ @strDel
+ '.bak'
--print @strDel
EXEC master..xp_cmdshell @strDel
--还原脚本
ALTER DATABASE test SET OFFLINE WITH ROLLBACK IMMEDIATE
RESTORE DATABASE test
FROM DISK='k:\test_2011-04-19.bak'
WITH REPLACE