批量备份数据库日志且30天后自动删除该备份文件-526互联

/*******************************************
 * 批量备份数据库日志且30天后自动删除该备份文件
 *******************************************/
DECLARE @backupfile VARCHAR(1024)  
DECLARE @filename VARCHAR(1024)  
DECLARE @path VARCHAR(1024)  
DECLARE @dbname VARCHAR(1024)  
DECLARE @extension_name VARCHAR(16)  
  
--备份参数  
DECLARE tmp_Cur CURSOR  
FOR  
    SELECT  NAME  
    FROM    [sys].[databases]  
    WHERE   NAME IN ( 'DataBaseName')  
  
SET @path = N'F:\LogBackUPPath\';  
SET @extension_name = N'trn';  
  
--生成文件名  
SET @filename = CONVERT(VARCHAR(1024), GETDATE(), 120)  
SET @filename = REPLACE(@filename, ':', '')  
SET @filename = REPLACE(@filename, '-', '')  
SET @filename = REPLACE(@filename, ' ', '')  
SET @filename = @filename + '_' + CONVERT (VARCHAR(3), DATEPART(ms, GETDATE()))  
    + N'.' + @extension_name  
  
OPEN tmp_Cur;  
FETCH NEXT FROM tmp_Cur INTO @dbname;  
WHILE @@FETCH_STATUS = 0   
    BEGIN  
        -- 得到完整目标文件，数据库将备份到这个文件中  
        SET @backupfile = @path + @dbname + @filename  

        -- 开始备份, COMPRESSION 参数表示压缩，可节省磁盘空间
		BACKUP LOG @dbname TO DISK = @backupfile WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD,RETAINDAYS= 30,STATS = 10,COMPRESSION

        FETCH NEXT FROM tmp_Cur INTO @dbname  
    END  
CLOSE tmp_Cur;  
DEALLOCATE tmp_Cur;