SQL server 每日自动全量备份数据库+5分钟事务日志备份

工作中需要进行数据库备份，但手工备份总是忘记，每天全量备份时间又长，让AI写了个脚本

作业名称	执行时间	作用
ACDB 每日备份	每天凌晨 2:00	完整备份，生成 `.bak` 文件
ACDB 日志备份	每天 09:00-21:00 每5分钟	事务日志备份，生成 `.trn` 文件
ACDB 清理过期备份	每天凌晨 3:00	删除7天前的 `.bak` + 1天前的 `.trn`

实现自动化功能，在SSMS中新建查询直接执行，默认备份目录是F:\数据库备份\

USE msdb;
GO

-- ==========================================
-- 清理旧作业（如果存在）
-- ==========================================
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = N'ACDB 每日备份')
    EXEC msdb.dbo.sp_delete_job @job_name = N'ACDB 每日备份', @delete_unused_schedule=1;
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = N'ACDB 日志备份')
    EXEC msdb.dbo.sp_delete_job @job_name = N'ACDB 日志备份', @delete_unused_schedule=1;
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = N'ACDB 清理过期备份')
    EXEC msdb.dbo.sp_delete_job @job_name = N'ACDB 清理过期备份', @delete_unused_schedule=1;
GO

-- ==========================================
-- 作业1：每日完整备份（凌晨 2:00 执行）
-- ==========================================
EXEC msdb.dbo.sp_add_job
    @job_name = N'ACDB 每日备份',
    @enabled = 1,
    @description = N'每天凌晨2点执行ACDB完整备份，保留7天';
GO

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'ACDB 每日备份',
    @step_name = N'完整备份',
    @subsystem = N'TSQL',
    @command = N'
DECLARE @filename NVARCHAR(260)
SET @filename = N''F:\数据库备份\ACDB_Full_''
    + CONVERT(NVARCHAR(8), GETDATE(), 112)
    + N''.bak''

BACKUP DATABASE [ACDB]
TO DISK = @filename
WITH COMPRESSION, INIT, STATS = 10',
    @database_name = N'master';
GO

EXEC msdb.dbo.sp_add_jobschedule
    @job_name = N'ACDB 每日备份',
    @name = N'凌晨2点执行',
    @freq_type = 4,
    @freq_interval = 1,
    @active_start_time = 020000;
GO

EXEC msdb.dbo.sp_add_jobserver
    @job_name = N'ACDB 每日备份',
    @server_name = N'(local)';
GO

-- ==========================================
-- 作业2：事务日志备份（09:00-21:00 每5分钟）
-- ==========================================
EXEC msdb.dbo.sp_add_job
    @job_name = N'ACDB 日志备份',
    @enabled = 1,
    @description = N'工作时间09:00-21:00每5分钟备份ACDB事务日志，保留1天';
GO

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'ACDB 日志备份',
    @step_name = N'日志备份',
    @subsystem = N'TSQL',
    @command = N'
DECLARE @filename NVARCHAR(260)
SET @filename = N''F:\数据库备份\ACDB_Log_''
    + CONVERT(NVARCHAR(8), GETDATE(), 112)
    + N''_''
    + REPLACE(CONVERT(NVARCHAR(8), GETDATE(), 108), N'':'', N'''')
    + N''.trn''

BACKUP LOG [ACDB]
TO DISK = @filename
WITH COMPRESSION, INIT, STATS = 10',
    @database_name = N'master';
GO

EXEC msdb.dbo.sp_add_jobschedule
    @job_name = N'ACDB 日志备份',
    @name = N'9点到21点每5分钟',
    @freq_type = 4,
    @freq_interval = 1,
    @freq_subday_type = 4,
    @freq_subday_interval = 5,
    @active_start_time = 090000,
    @active_end_time = 210000;
GO

EXEC msdb.dbo.sp_add_jobserver
    @job_name = N'ACDB 日志备份',
    @server_name = N'(local)';
GO

-- ==========================================
-- 作业3：清理过期备份（凌晨 3:00 执行）
-- 完整备份保留7天，事务日志备份保留1天
-- ==========================================
EXEC msdb.dbo.sp_add_job
    @job_name = N'ACDB 清理过期备份',
    @enabled = 1,
    @description = N'删除7天前的完整备份和1天前的事务日志备份';
GO

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'ACDB 清理过期备份',
    @step_name = N'删除过期备份文件',
    @subsystem = N'TSQL',
    @command = N'
DECLARE @OLDDATE_BAK DATETIME
DECLARE @OLDDATE_TRN DATETIME

-- 完整备份保留7天
SET @OLDDATE_BAK = GETDATE() - 7
-- 事务日志备份保留1天
SET @OLDDATE_TRN = GETDATE() - 1

-- 删除过期的完整备份文件
EXECUTE master.dbo.xp_delete_file 0, N''F:\数据库备份'', N''bak'', @OLDDATE_BAK, 1

-- 删除过时的事务日志备份文件
EXECUTE master.dbo.xp_delete_file 0, N''F:\数据库备份'', N''trn'', @OLDDATE_TRN, 1

PRINT ''清理完成：删除了7天前的完整备份和1天前的事务日志备份。''
',
    @database_name = N'master';
GO

EXEC msdb.dbo.sp_add_jobschedule
    @job_name = N'ACDB 清理过期备份',
    @name = N'每日凌晨3点清理',
    @freq_type = 4,
    @freq_interval = 1,
    @active_start_time = 030000;
GO

EXEC msdb.dbo.sp_add_jobserver
    @job_name = N'ACDB 清理过期备份',
    @server_name = N'(local)';
GO

PRINT '全部完成！已创建以下三个作业：
1. [ACDB 每日备份]    - 每天凌晨2:00 完整备份
2. [ACDB 日志备份]    - 每天09:00-21:00 每5分钟日志备份
3. [ACDB 清理过期备份] - 每天凌晨3:00 清理（完整备份保留7天，日志备份保留1天）'

先观察点时间，可以了就将另外的服务器进行复用，代码留存到这里。

备份多个数据库则需要多个作业：

USE msdb;
GO

-- ==========================================
-- 清理所有旧作业（如果存在）
-- ==========================================
DECLARE @jobs_to_delete TABLE (name NVARCHAR(128));
INSERT INTO @jobs_to_delete VALUES
    (N'ACDB 每日备份'), (N'ACDB 日志备份'), (N'ACDB 清理过期备份'),
    (N'ACWK 每日备份'), (N'ACWK 日志备份'), (N'ACWK 清理过期备份'),
    (N'DBYDB 每日备份'), (N'DBYDB 日志备份'), (N'DBYDB 清理过期备份');

DECLARE @job_name NVARCHAR(128);
DECLARE cur CURSOR FOR SELECT name FROM @jobs_to_delete;
OPEN cur;
FETCH NEXT FROM cur INTO @job_name;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @job_name)
        EXEC msdb.dbo.sp_delete_job @job_name = @job_name, @delete_unused_schedule = 1;
    FETCH NEXT FROM cur INTO @job_name;
END;
CLOSE cur;
DEALLOCATE cur;
GO


-- ============================================================
--  ACDB 每日备份（凌晨 2:00）
-- ============================================================
EXEC msdb.dbo.sp_add_job
    @job_name = N'ACDB 每日备份', @enabled = 1,
    @description = N'每天凌晨2点执行ACDB完整备份，保留7天';
GO
EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'ACDB 每日备份', @step_name = N'完整备份', @subsystem = N'TSQL',
    @command = N'
DECLARE @filename NVARCHAR(260)
SET @filename = N''D:\Backup\SQL\ACDB_Full_'' + CONVERT(NVARCHAR(8), GETDATE(), 112) + N''.bak''
BACKUP DATABASE [ACDB] TO DISK = @filename WITH COMPRESSION, INIT, STATS = 10',
    @database_name = N'master';
GO
EXEC msdb.dbo.sp_add_jobschedule
    @job_name = N'ACDB 每日备份', @name = N'凌晨2点执行',
    @freq_type = 4, @freq_interval = 1, @active_start_time = 020000;
GO
EXEC msdb.dbo.sp_add_jobserver @job_name = N'ACDB 每日备份', @server_name = N'(local)';
GO

-- ============================================================
--  ACDB 日志备份（09:00-21:00 每5分钟）
-- ============================================================
EXEC msdb.dbo.sp_add_job
    @job_name = N'ACDB 日志备份', @enabled = 1,
    @description = N'工作时间09:00-21:00每5分钟备份ACDB事务日志，保留1天';
GO
EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'ACDB 日志备份', @step_name = N'日志备份', @subsystem = N'TSQL',
    @command = N'
DECLARE @filename NVARCHAR(260)
SET @filename = N''D:\Backup\SQL\ACDB_Log_'' + CONVERT(NVARCHAR(8), GETDATE(), 112) + N''_'' + REPLACE(CONVERT(NVARCHAR(8), GETDATE(), 108), N'':'', N'''') + N''.trn''
BACKUP LOG [ACDB] TO DISK = @filename WITH COMPRESSION, INIT, STATS = 10',
    @database_name = N'master';
GO
EXEC msdb.dbo.sp_add_jobschedule
    @job_name = N'ACDB 日志备份', @name = N'9点到21点每5分钟',
    @freq_type = 4, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 5,
    @active_start_time = 090000, @active_end_time = 210000;
GO
EXEC msdb.dbo.sp_add_jobserver @job_name = N'ACDB 日志备份', @server_name = N'(local)';
GO

-- ============================================================
--  ACDB 清理过期备份（凌晨 3:00）
-- ============================================================
EXEC msdb.dbo.sp_add_job
    @job_name = N'ACDB 清理过期备份', @enabled = 1,
    @description = N'删除7天前的ACDB完整备份和1天前的事务日志备份';
GO
EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'ACDB 清理过期备份', @step_name = N'删除过期备份文件', @subsystem = N'TSQL',
    @command = N'
DECLARE @OLDDATE_BAK DATETIME = GETDATE() - 7
DECLARE @OLDDATE_TRN DATETIME = GETDATE() - 1
EXECUTE master.dbo.xp_delete_file 0, N''D:\Backup\SQL'', N''bak'', @OLDDATE_BAK, 1
EXECUTE master.dbo.xp_delete_file 0, N''D:\Backup\SQL'', N''trn'', @OLDDATE_TRN, 1
PRINT ''ACDB清理完成''',
    @database_name = N'master';
GO
EXEC msdb.dbo.sp_add_jobschedule
    @job_name = N'ACDB 清理过期备份', @name = N'每日凌晨3点清理',
    @freq_type = 4, @freq_interval = 1, @active_start_time = 030000;
GO
EXEC msdb.dbo.sp_add_jobserver @job_name = N'ACDB 清理过期备份', @server_name = N'(local)';
GO


-- ============================================================
--  ACWK 每日备份（凌晨 2:00）
-- ============================================================
EXEC msdb.dbo.sp_add_job
    @job_name = N'ACWK 每日备份', @enabled = 1,
    @description = N'每天凌晨2点执行ACWK完整备份，保留7天';
GO
EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'ACWK 每日备份', @step_name = N'完整备份', @subsystem = N'TSQL',
    @command = N'
DECLARE @filename NVARCHAR(260)
SET @filename = N''D:\Backup\SQL\ACWK_Full_'' + CONVERT(NVARCHAR(8), GETDATE(), 112) + N''.bak''
BACKUP DATABASE [ACWK] TO DISK = @filename WITH COMPRESSION, INIT, STATS = 10',
    @database_name = N'master';
GO
EXEC msdb.dbo.sp_add_jobschedule
    @job_name = N'ACWK 每日备份', @name = N'凌晨2点执行',
    @freq_type = 4, @freq_interval = 1, @active_start_time = 020000;
GO
EXEC msdb.dbo.sp_add_jobserver @job_name = N'ACWK 每日备份', @server_name = N'(local)';
GO

-- ============================================================
--  ACWK 日志备份（09:00-21:00 每5分钟）
-- ============================================================
EXEC msdb.dbo.sp_add_job
    @job_name = N'ACWK 日志备份', @enabled = 1,
    @description = N'工作时间09:00-21:00每5分钟备份ACWK事务日志，保留1天';
GO
EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'ACWK 日志备份', @step_name = N'日志备份', @subsystem = N'TSQL',
    @command = N'
DECLARE @filename NVARCHAR(260)
SET @filename = N''D:\Backup\SQL\ACWK_Log_'' + CONVERT(NVARCHAR(8), GETDATE(), 112) + N''_'' + REPLACE(CONVERT(NVARCHAR(8), GETDATE(), 108), N'':'', N'''') + N''.trn''
BACKUP LOG [ACWK] TO DISK = @filename WITH COMPRESSION, INIT, STATS = 10',
    @database_name = N'master';
GO
EXEC msdb.dbo.sp_add_jobschedule
    @job_name = N'ACWK 日志备份', @name = N'9点到21点每5分钟',
    @freq_type = 4, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 5,
    @active_start_time = 090000, @active_end_time = 210000;
GO
EXEC msdb.dbo.sp_add_jobserver @job_name = N'ACWK 日志备份', @server_name = N'(local)';
GO

-- ============================================================
--  ACWK 清理过期备份（凌晨 3:00）
-- ============================================================
EXEC msdb.dbo.sp_add_job
    @job_name = N'ACWK 清理过期备份', @enabled = 1,
    @description = N'删除7天前的ACWK完整备份和1天前的事务日志备份';
GO
EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'ACWK 清理过期备份', @step_name = N'删除过期备份文件', @subsystem = N'TSQL',
    @command = N'
DECLARE @OLDDATE_BAK DATETIME = GETDATE() - 7
DECLARE @OLDDATE_TRN DATETIME = GETDATE() - 1
EXECUTE master.dbo.xp_delete_file 0, N''D:\Backup\SQL'', N''bak'', @OLDDATE_BAK, 1
EXECUTE master.dbo.xp_delete_file 0, N''D:\Backup\SQL'', N''trn'', @OLDDATE_TRN, 1
PRINT ''ACWK清理完成''',
    @database_name = N'master';
GO
EXEC msdb.dbo.sp_add_jobschedule
    @job_name = N'ACWK 清理过期备份', @name = N'每日凌晨3点清理',
    @freq_type = 4, @freq_interval = 1, @active_start_time = 030000;
GO
EXEC msdb.dbo.sp_add_jobserver @job_name = N'ACWK 清理过期备份', @server_name = N'(local)';
GO


-- ============================================================
--  DBYDB 每日备份（凌晨 2:00）
-- ============================================================
EXEC msdb.dbo.sp_add_job
    @job_name = N'DBYDB 每日备份', @enabled = 1,
    @description = N'每天凌晨2点执行DBYDB完整备份，保留7天';
GO
EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'DBYDB 每日备份', @step_name = N'完整备份', @subsystem = N'TSQL',
    @command = N'
DECLARE @filename NVARCHAR(260)
SET @filename = N''D:\Backup\SQL\DBYDB_Full_'' + CONVERT(NVARCHAR(8), GETDATE(), 112) + N''.bak''
BACKUP DATABASE [DBYDB] TO DISK = @filename WITH COMPRESSION, INIT, STATS = 10',
    @database_name = N'master';
GO
EXEC msdb.dbo.sp_add_jobschedule
    @job_name = N'DBYDB 每日备份', @name = N'凌晨2点执行',
    @freq_type = 4, @freq_interval = 1, @active_start_time = 020000;
GO
EXEC msdb.dbo.sp_add_jobserver @job_name = N'DBYDB 每日备份', @server_name = N'(local)';
GO

-- ============================================================
--  DBYDB 日志备份（09:00-21:00 每5分钟）
-- ============================================================
EXEC msdb.dbo.sp_add_job
    @job_name = N'DBYDB 日志备份', @enabled = 1,
    @description = N'工作时间09:00-21:00每5分钟备份DBYDB事务日志，保留1天';
GO
EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'DBYDB 日志备份', @step_name = N'日志备份', @subsystem = N'TSQL',
    @command = N'
DECLARE @filename NVARCHAR(260)
SET @filename = N''D:\Backup\SQL\DBYDB_Log_'' + CONVERT(NVARCHAR(8), GETDATE(), 112) + N''_'' + REPLACE(CONVERT(NVARCHAR(8), GETDATE(), 108), N'':'', N'''') + N''.trn''
BACKUP LOG [DBYDB] TO DISK = @filename WITH COMPRESSION, INIT, STATS = 10',
    @database_name = N'master';
GO
EXEC msdb.dbo.sp_add_jobschedule
    @job_name = N'DBYDB 日志备份', @name = N'9点到21点每5分钟',
    @freq_type = 4, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 5,
    @active_start_time = 090000, @active_end_time = 210000;
GO
EXEC msdb.dbo.sp_add_jobserver @job_name = N'DBYDB 日志备份', @server_name = N'(local)';
GO

-- ============================================================
--  DBYDB 清理过期备份（凌晨 3:00）
-- ============================================================
EXEC msdb.dbo.sp_add_job
    @job_name = N'DBYDB 清理过期备份', @enabled = 1,
    @description = N'删除7天前的DBYDB完整备份和1天前的事务日志备份';
GO
EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'DBYDB 清理过期备份', @step_name = N'删除过期备份文件', @subsystem = N'TSQL',
    @command = N'
DECLARE @OLDDATE_BAK DATETIME = GETDATE() - 7
DECLARE @OLDDATE_TRN DATETIME = GETDATE() - 1
EXECUTE master.dbo.xp_delete_file 0, N''D:\Backup\SQL'', N''bak'', @OLDDATE_BAK, 1
EXECUTE master.dbo.xp_delete_file 0, N''D:\Backup\SQL'', N''trn'', @OLDDATE_TRN, 1
PRINT ''DBYDB清理完成''',
    @database_name = N'master';
GO
EXEC msdb.dbo.sp_add_jobschedule
    @job_name = N'DBYDB 清理过期备份', @name = N'每日凌晨3点清理',
    @freq_type = 4, @freq_interval = 1, @active_start_time = 030000;
GO
EXEC msdb.dbo.sp_add_jobserver @job_name = N'DBYDB 清理过期备份', @server_name = N'(local)';
GO

PRINT '全部完成！已创建9个作业。'

将3个清理作业合并成1个代码，方便今后创建更多作业。

USE msdb;
GO

-- 删除旧的三个清理作业
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = N'ACDB 清理过期备份')
    EXEC msdb.dbo.sp_delete_job @job_name = N'ACDB 清理过期备份', @delete_unused_schedule = 1;
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = N'ACWK 清理过期备份')
    EXEC msdb.dbo.sp_delete_job @job_name = N'ACWK 清理过期备份', @delete_unused_schedule = 1;
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = N'DBYDB 清理过期备份')
    EXEC msdb.dbo.sp_delete_job @job_name = N'DBYDB 清理过期备份', @delete_unused_schedule = 1;
GO

-- 创建统一的清理作业
EXEC msdb.dbo.sp_add_job
    @job_name = N'清理过期备份',
    @enabled = 1,
    @description = N'统一清理所有数据库的过期备份：完整备份保留7天，日志备份保留1天';
GO

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'清理过期备份',
    @step_name = N'删除过期备份文件',
    @subsystem = N'TSQL',
    @command = N'
DECLARE @OLDDATE_BAK DATETIME = GETDATE() - 7
DECLARE @OLDDATE_TRN DATETIME = GETDATE() - 1

-- 删除7天前的完整备份（.bak）
EXECUTE master.dbo.xp_delete_file 0, N''D:\Backup\SQL'', N''bak'', @OLDDATE_BAK, 1

-- 删除1天前的事务日志备份（.trn）
EXECUTE master.dbo.xp_delete_file 0, N''D:\Backup\SQL'', N''trn'', @OLDDATE_TRN, 1

PRINT ''过期备份清理完成''',
    @database_name = N'master';
GO

EXEC msdb.dbo.sp_add_jobschedule
    @job_name = N'清理过期备份',
    @name = N'每日凌晨3点清理',
    @freq_type = 4,
    @freq_interval = 1,
    @active_start_time = 030000;
GO

EXEC msdb.dbo.sp_add_jobserver
    @job_name = N'清理过期备份',
    @server_name = N'(local)';
GO

PRINT '统一清理作业创建完成！'