阮胜昌的博客-Linux&MySQL数据库运维

-- 开启 CDC 的步骤：
-- 第一步：开启数据库的 CDC 功能
USE shukuinfo;
GO

-- 启用数据库级别的 CDC
EXEC sys.sp_cdc_enable_db;
GO

-- 为库下的所有表开启CDC功能
-- 声明变量
DECLARE @tableName NVARCHAR(128);
DECLARE @schemaName NVARCHAR(128);
DECLARE @sql NVARCHAR(MAX);
-- 游标遍历所有用户表
DECLARE table_cursor CURSOR FOR
SELECT 
    s.name AS SchemaName,
    t.name AS TableName
FROM 
    sys.tables t
INNER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.is_ms_shipped = 0  -- 排除系统表
    AND t.name NOT LIKE 'cdc%'  -- 排除 CDC 自身创建的表
    AND NOT EXISTS (  -- 排除已经开启 CDC 的表
        SELECT 1 
        FROM sys.tables st
        JOIN sys.schemas ss ON st.schema_id = ss.schema_id
        WHERE st.name = t.name 
          AND ss.name = s.name
          AND st.is_tracked_by_cdc = 1
    );

OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @schemaName, @tableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 构造并执行开启 CDC 的语句
    SET @sql = 'EXEC sys.sp_cdc_enable_table
        @source_schema = ''' + @schemaName + ''',
        @source_name   = ''' + @tableName + ''',
        @role_name     = NULL,
        @supports_net_changes = 1;';
    
    PRINT 'Enabling CDC for: [' + @schemaName + '].[' + @tableName + ']';
    EXEC sp_executesql @sql;


    FETCH NEXT FROM table_cursor INTO @schemaName, @tableName;
END

CLOSE table_cursor;
DEALLOCATE table_cursor;
GO

-- 验证 CDC 是否开启成功
-- 查看数据库是否启用了 CDC
SELECT name, is_cdc_enabled 
FROM sys.databases 
WHERE name = 'shukuinfo';

-- 查看哪些表已开启 CDC
SELECT 
    s.name AS SchemaName,
    t.name AS TableName,
    t.is_tracked_by_cdc
FROM 
    sys.tables t
INNER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.is_tracked_by_cdc = 1;

配置全量日志恢复模式：
USE [master];
GO
ALTER DATABASE [shukuinfo] SET RECOVERY FULL WITH NO_WAIT;
GO

全库备份数据库
BACKUP DATABASE [shukuinfo]
TO DISK = N'D:\SQL_Backup\shukuinfo_full_init.bak' -- 示例路径，请自行替换
WITH INIT, NAME = N'shukuinfo-完整数据库备份', STATS = 10, CHECKSUM;
GO

全量备份日志：（覆盖备份）
USE shukuinfo
BACKUP LOG shukuinfo
TO DISK = 'H:\fullbak\shukuinfo_log.trn'
WITH COMPRESSION, INIT;

配置定时任务，定时执行：
 SSMS 图形化创建定时日志备份作业（最直观，推荐）
步骤 1：打开 SQL Server 代理
左侧对象资源管理器 → 展开「SQL Server 代理」
若看不到 SQL Server 代理：右键实例 → 刷新；或确认代理服务已启动。
右键「作业」→ 新建作业
步骤 2：常规页填写作业基础信息
名称：shukuinfo_每30分钟事务日志备份
说明：FULL恢复模式定时截断日志，防止日志文件暴涨
所有者：选 sa 或本地管理员账号，确定权限
步骤 3：切换到【步骤】标签 → 新建步骤
步骤常规配置
步骤名称：备份shukuinfo事务日志
类型：Transact-SQL(T-SQL)
数据库：master（不影响，脚本内部指定库）
命令框粘贴完整脚本：
 
DECLARE @bakPath NVARCHAR(500)
-- 拼接带时间戳的日志备份文件名，避免覆盖 （每次备份一个日志文件 ）
SET @bakPath = N'D:\Backup\shukuinfo_log_' 
+ REPLACE(CONVERT(VARCHAR,GETDATE(),112),' ','') 
+ N'_' 
+ REPLACE(CONVERT(VARCHAR,GETDATE(),108),':','') 
+ N'.trn'

-- 执行日志备份，开启压缩，覆盖本文件
BACKUP LOG [shukuinfo] 
TO DISK = @bakPath
WITH 
    COMPRESSION,  -- SQL2012企业版支持压缩，节省磁盘
    INIT,         -- 覆盖当前备份文件
    STATS=10;     -- 每10%输出进度日志

-- 可选：打印备份完成信息到消息窗口
PRINT '日志备份完成，文件路径：' + @bakPath;
GO


事务日志覆盖模式 （每次会覆盖同一个日志文件 ）
USE shukuinfo
BACKUP LOG shukuinfo
TO DISK = 'H:\fullbak\shukuinfo_log_20260702.trn'
WITH COMPRESSION, INIT;
 
步骤【高级】标签配置
成功操作：转到下一步（只有 1 步，选「退出作业报告成功」）
失败操作：退出作业报告失败
重试次数：2（备份偶尔磁盘异常自动重试）
重试间隔：5 分钟
点击「确定」保存步骤。
步骤 4：切换到【计划】标签 → 新建计划（核心定时配置）
计划名称：每30分钟执行日志备份
计划类型：重复执行
频率：每天
每日频率：
发生周期：每 30 分钟
执行时间范围：00:00:00 ~ 23:59:59（全天不间断）
持续时间：无结束日期（长期运行）
点击「确定」保存计划。
步骤 5：可选【通知】标签（备份失败告警）
如果需要备份失败发邮件 / 写入 Windows 事件日志：
勾选「当作业失败时」
可选择：写入 Windows 应用程序事件日志（简单，无需配置数据库邮件）
保存整个作业。

步骤 6：手动测试作业是否正常运行
右键刚创建的作业 → 启动作业

执行完成后查看：
D:\Backup\ 生成带时间戳的 .trn 文件
右键作业 → 查看历史记录，确认无报错；

再执行 DBCC SQLPERF(LOGSPACE)，日志使用率会明显下降。