博客摘录「 Sql Server 收缩日志文件原理及always on 下的实践」2024年5月22日
四、Always on 环境下实践
先对数据库进行完整备份：
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
DECLARE @DbName NVARCHAR(1000);
DECLARE myCursor CURSOR LOCAL STATIC
FOR
SELECT [name]
FROM sysdatabases
WHERE [name] NOT IN ( 'master', 'model', 'msdb', 'tempdb' )
AND name NOT LIKE '%test%'
AND name NOT LIKE '%bak%'
AND name NOT LIKE '%demo%'
AND version IS NOT NULL
AND version <> 0
ORDER BY [name];
OPEN myCursor;
FETCH NEXT FROM myCursor INTO @DbName;
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
DECLARE @strDate AS NVARCHAR(20),
@strDateBeforeSeven AS NVARCHAR(20),
@strFileName AS NVARCHAR(255),
@strFileNameBeforeSeven AS NVARCHAR(255),
@strCommand AS NVARCHAR(255)
SET @strDate = CONVERT(NVARCHAR(20),GETDATE(),112);
SET @strDateBeforeSeven = CONVERT(NVARCHAR(20),GETDATE()-3,112);
SET @strFileName = 'E:\daybak\['+@DbName+']_bakup_'+@strDate;
SET @strFileNameBeforeSeven = 'E:\daybak\['+@DbName+']_bakup_'+@strDateBeforeSeven;
EXEC ('BACKUP DATABASE ['+@DbName+'] TO DISK = ''' + @strFileName + '.bak''')
SET @strCommand = 'DEL ' + @strFileNameBeforeSeven + '.bak'
EXEC master.dbo.xp_cmdshell @strCommand
FETCH NEXT FROM myCursor INTO @DbName;
END;
CLOSE myCursor;
DEALLOCATE myCursor;
然后对数据库进行事务日志备份并收缩：
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
DECLARE @DbName NVARCHAR(1000);
DECLARE myCursor CURSOR LOCAL STATIC
FOR
SELECT [name]
FROM sysdatabases
WHERE [name] NOT IN ( 'master', 'model', 'msdb', 'tempdb' )
AND name NOT LIKE '%test%'
AND name NOT LIKE '%bak%'
AND name NOT LIKE '%demo%'
AND version IS NOT NULL
AND version <> 0
ORDER BY [name];
OPEN myCursor;
FETCH NEXT FROM myCursor INTO @DbName;
WHILE ( @@FETCH_STATUS = 0 )BEGIN
DECLARE @strDate AS NVARCHAR(20),
@strDateBeforeSeven AS NVARCHAR(20),
@strFileName AS NVARCHAR(255),
@strFileNameBeforeSeven AS NVARCHAR(255),
@strCommand AS NVARCHAR(255)
SET @strDate = CONVERT(NVARCHAR(20),GETDATE(),112);
SET @strDateBeforeSeven = CONVERT(NVARCHAR(20),GETDATE()-3,112);
SET @strFileName = 'E:\Log_daybak\['+@DbName+']_bakup_'+@strDate;
SET @strFileNameBeforeSeven='E:\Log_daybak\'+@DbName+']_bakup_'+@strDateBeforeSeven;
EXEC ('BACKUP LOG ['+@DbName+'] TO DISK = ''' + @strFileName + '.log'';USE ['+@DbName+'];DBCC SHRINKFILE(2,100);')
SET @strCommand = 'DEL ' + @strFileNameBeforeSeven + '.log'
EXEC master.dbo.xp_cmdshell @strCommand
FETCH NEXT FROM myCursor INTO @DbName;
END;
CLOSE myCursor;
DEALLOCATE myCursor;