备份日志，并删除过期的备份

BACKUP DATABASE 【数据库】TO DISK = 【路径】 WITH COMPRESSION,NOINIT,NAME=@BACKUPNAME, EXPIREDATE = @EXPIREDATE

--删除过期的备份

INSERT INTO @TBL_BACKUPHISTORY

SELECT NAME,expiration_date AS [EXPIREDATE] FROM MSDB..BACKUPSET WHERE NAME LIKE '%.bak' and expiration_date<=getdate()

SELECT @COUNT = MAX(ID) FROM @TBL_BACKUPHISTORY

IF @COUNT>0

BEGIN

　　SET @I = 1

　　SELECT @CDATE = MAX([EXPIREDATE]) FROM @TBL_BACKUPHISTORY

　　WHILE @I<=@COUNT

　　BEGIN

　　　　SELECT @DELFILENAME = NAME FROM @TBL_BACKUPHISTORY WHERE ID = @I

　　　　SET @DELBAKSQL = 'DEL 【路径】'+@DELFILENAME

　　　　exec master.dbo.xp_cmdshell @DELBAKSQL

　　END

 

　　--删除备份和还原历史记录中所有小于@CDATE的记录

　　EXEC msdb..SP_DELETE_BACKUPHISTORY @CDATE

    END

转载于:https://www.cnblogs.com/zhaoyx/archive/2012/09/27/2705055.html