SQL Server Always On实现日志定时备份

在前面的文章中已经介绍了我已经在本地服务器上搭建了基于分离的群集并且搭建了SQL Server 2022 Always On群集。
在一次日常巡检中，我发现数据库的日志文件(.ldf)异常大，甚至达到了200GB，这显然是不正常的，我们先来看看SQL Server日志文件承担什么作用。

• 单个事务恢复：如果应用程序发出了一个ROLLBACK语句，或者如果数据库引擎检测到了错误（如与客户端的通信丢失），则使用日志记录来回滚由不完整事务所做的修改。
• 在SQL Server启动时恢复所有不完整的事务：如果服务器失败，可能会导致一些修改从缓冲区缓存中未写入数据文件，而且数据文件中可能存在一些不完整事务的修改。当SQL Server实例启动时，它会对每个数据库进行恢复。日志中记录的可能未写入数据文件的每个修改都会被前滚。然后，事务日志中找到的每个不完整事务都会被回滚，以确保保持数据库的完整性。
• 将恢复的数据库、文件、文件组或页面前滚到故障点：在硬件损失或影响数据库文件的磁盘故障后，你可以将数据库恢复到故障点。首先恢复最后一次完整的数据库备份和最后一次差异数据库备份，然后恢复后续序列的事务日志备份到故障点。在你恢复每个日志备份时，数据库引擎重新应用日志中记录的所有修改，以前滚所有事务。当最后一个日志备份被恢复时，数据库引擎然后使用日志信息回滚所有在那个点上未完成的事务。
• 支持事务复制：日志文件还支持事务复制。
• 支持高可用性和灾难恢复解决方案：如Always On可用性组、数据库镜像和日志传送。

显然，它的作用是为了提高可用性的，但是如果让其无限制的增长下去，那么很快磁盘将会爆满且SQL Server会出现无法连接的情况，而将数据库日志文件上限进行限制则会出现无法预料的问题。

在单机模式下，将数据库恢复模式设置为简单可以由SQL Server自行管理日志文件，而对于Always On可用组数据库则必须将恢复模式设置为完整才可以加入Always On可用性数据库副本，那么便需要我们定时的对日志数据进行备份，日志备份会对事务进行截断，然后可以使用收缩数据库文件来使日志文件恢复初始大小或一个指定值，我们可以使用以下脚本进行操作，该脚本的主要功能有：

• 判断自身是否为SQL Server运行的主副本节点，如果不是主副本节点则不需要运行。假设在主节点出现故障的情况下故障角色也能迅速的转移到其他节点，数据也不会出现丢失，所以我们一般只需要在主节点上进行备份和日志收缩。
• 连接本地数据库并查询加入了Always On的数据库，请注意，SQL Server登录应该设置为混合模式。
• 运行日志备份动作后并进行日志文件收缩。
• 检查备份文件夹的过期备份文件，脚本内设置的是7天。

# 导入所需的模块
Import-Module FailoverClusters
Import-Module SQLPS -DisableNameChecking

# 获取当前节点的角色
$role = Get-ClusterGroup -Name "YunLengDB-Cluster" # 根据实际角色名变更

# 检查当前节点是否是角色的所有者
if ($role.OwnerNode.Name -eq [System.Net.Dns]::GetHostName()) {
    # 删除7天之前的备份文件
    $limit = (Get-Date).AddDays(-7)
    Get-ChildItem "D:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\" -Recurse | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit } | Remove-Item

    # 连接到本机的SQL Server
    $server = New-Object Microsoft.SqlServer.Management.Smo.Server "localhost"

    # 检查连接是否成功
    if ($server -eq $null) {
        Write-Output "连接到SQL Server数据库失败。"
    } else {
        Write-Output "成功连接到SQL Server数据库。"
    }

    # 使用Invoke-Sqlcmd命令执行SQL查询来获取所有已加入Always On的数据库
    $query = @"
SELECT 
    DB_NAME(database_id) AS DatabaseName,
    group_database_id,
    is_local,
    synchronization_state_desc
FROM sys.dm_hadr_database_replica_states
WHERE is_local = 1
"@
    $databases = Invoke-Sqlcmd -Query $query -ServerInstance "localhost"

    # 打印数据库列表
    Write-Output "已加入Always On的数据库: $($databases.DatabaseName -join ', ')"

    # 对所有已加入Always On的数据库进行日志备份和日志收缩
    foreach ($db in $databases.DatabaseName) {
        try {
            # 执行日志备份
            $backup = New-Object Microsoft.SqlServer.Management.Smo.Backup
            $backup.Action = "Log"
            $backup.Database = $db

            # 在备份文件名中包含数据库名和当前日期
            $date = Get-Date -Format "yyyy-MM-dd"
            $backup.Devices.AddDevice("D:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\$($db)_$($date)_Log.bak", "File")

            $backup.SqlBackup($server)

            Write-Output "成功备份数据库 '$($db)' 的日志文件。"
        } catch {
            Write-Output "备份数据库 '$($db)' 的日志文件时发生错误: $($_.Exception.Message)"
        }

        try {
            # 执行日志收缩
            $server.Databases[$db].LogFiles[0].Shrink(0, "TruncateOnly")
            Write-Output "成功收缩数据库 '$($db)' 的日志文件。"
        } catch {
            Write-Output "收缩数据库 '$($db)' 的日志文件时发生错误: $($_.Exception.Message)"
        }
    }
}

需要修改的地方主要为角色名：可以通过Windows故障转移群集管理器查看，备份目录：根据实际情况进行修改，需要注意的是在每个节点内都需要同样的备份目录，你也可以新建一个文件夹专门存放，部分打印信息在测试成功后可以去除。

使用任务计划管理器为每个节点都添加该任务，建议在每天的00:00运行。在运行后，主节点的日志文件收缩成功，文件也变小，同时在所有辅助节点的文件也会进行同步（文件也变小）。这样便实现了在SQL Server Always On中减小日志文件对SQL Server及系统的影响，且也可以根据灾难恢复需求恢复到对应的时间点。