/*******************************************
* 批量备份数据库日志且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;
标签:tmp,SET,VARCHAR,Cur,30,filename,日志,备份文件,DECLARE
From: https://www.cnblogs.com/purvis/p/17640052.html