SQL Server建立自动备份的维护计划的全过程

一、环境准备

1. SQL Server服务配置

确保SQL Server代理服务已启动并设置为自动启动
使用具有sysadmin权限的账户登录SQL Server

二、创建维护计划

方法1：使用SSMS图形界面

步骤1：打开维护计划设计器在SSMS中连接SQL Server 展开"管理" → 右键"维护计划" → "新建维护计划" 输入计划名称（如：DailyBackupPlan）点击"确定"进入设计界面步骤2：配置计划属性在设计器中右键空白处 → "属性" 配置关键属性：

计划名称: DailyBackupPlan
说明: 每日数据库备份计划
所有者: sa 或指定账户

步骤3：设置计划任务从左侧工具箱拖动"备份数据库任务"到设计区双击任务进行配置：

方法2：使用T-SQL脚本

USE msdb;
GO

-- 1. 创建每日备份计划
EXEC dbo.sp_add_job
    @job_name = N'DailyDatabaseBackup',
    @enabled = 1,
    @description = N'每日自动备份数据库';
GO

-- 2. 添加作业步骤
EXEC sp_add_jobstep
    @job_name = N'DailyDatabaseBackup',
    @step_name = N'FullBackup',
    @subsystem = N'TSQL',
    @command = N'
-- 备份所有用户数据库
DECLARE @dbname NVARCHAR(100)
DECLARE @path NVARCHAR(500)
DECLARE @filename NVARCHAR(500)
DECLARE @datetime VARCHAR(20)

SET @datetime = CONVERT(VARCHAR(20), GETDATE(), 112) + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108), '':'', '''')
SET @path = ''E:\SQLBackup\''  -- 修改为你的备份路径

DECLARE db_cursor CURSOR FOR
SELECT name 
FROM sys.databases 
WHERE name NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
AND state = 0  -- 只备份在线数据库

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @filename = @path + @dbname + ''_'' + @datetime + ''.bak''

    BACKUP DATABASE @dbname 
    TO DISK = @filename
    WITH COMPRESSION,  -- 启用压缩
         STATS = 10,
         CHECKSUM;

    FETCH NEXT FROM db_cursor INTO @dbname
END

CLOSE db_cursor
DEALLOCATE db_cursor',
    @retry_attempts = 3,
    @retry_interval = 5;
GO

-- 3. 创建计划
EXEC sp_add_schedule
    @schedule_name = N'Daily_2AM',
    @freq_type = 4,  -- 每天
    @freq_interval = 1,
    @active_start_time = 020000;  -- 凌晨2点
GO

-- 4. 将计划附加到作业
EXEC sp_attach_schedule
    @job_name = N'DailyDatabaseBackup',
    @schedule_name = N'Daily_2AM';
GO

-- 5. 启动作业
EXEC sp_add_jobserver
    @job_name = N'DailyDatabaseBackup';
GO

三、详细配置选项

备份任务配置要点：

1. 备份类型选择

完整备份：全量备份
差异备份：只备份上次完整备份后的变化
事务日志备份：备份日志文件

2. 备份目标配置

目标: 磁盘
备份文件位置: 
  - 单个文件: D:\Backup\DBName.bak
  - 多个文件: D:\Backup\DBName_01.bak, D:\Backup\DBName_02.bak
  - 按数据库名称和日期: D:\Backup\$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(DBN)).bak

3. 备份选项配置

-- 推荐的备份选项
WITH 
    COMPRESSION,           -- 压缩备份（SQL Server 2008+）
    CHECKSUM,              -- 验证页校验和
    STATS = 10,            -- 每完成10%显示进度
    INIT,                  -- 覆盖现有文件
    MAXTRANSFERSIZE = 4194304,  -- 4MB传输大小
    BUFFERCOUNT = 50       -- 缓冲区数量

四、完整维护计划示例

包含清理功能的完整计划

USE msdb;
GO

-- 创建包含备份和清理的作业
EXEC dbo.sp_add_job
    @job_name = N'DB_Maintenance_Full',
    @enabled = 1,
    @description = N'完整数据库维护：备份+清理旧文件';
GO

-- 步骤1：完整备份
EXEC sp_add_jobstep
    @job_name = N'DB_Maintenance_Full',
    @step_name = N'FullBackup',
    @subsystem = N'TSQL',
    @command = N'
DECLARE @BackupPath NVARCHAR(500) = N''E:\SQLBackup\Full\'';

-- 创建备份文件夹（如果不存在）
EXEC master.dbo.xp_create_subdir @BackupPath;

DECLARE @SQL NVARCHAR(MAX);
SELECT @SQL = COALESCE(@SQL + CHAR(13) + CHAR(10), N'''') + 
N''BACKUP DATABASE ['' + name + ''] 
TO DISK = N'''''' + @BackupPath + name + ''_'' + 
    REPLACE(CONVERT(VARCHAR(20), GETDATE(), 120), '':'', '''') + ''.bak'''' 
WITH COMPRESSION, CHECKSUM, STATS = 10;''
FROM sys.databases 
WHERE name NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
AND state_desc = ''ONLINE'';

EXEC sp_executesql @SQL;',
    @retry_attempts = 2;
GO

-- 步骤2：清理旧备份文件（保留7天）
EXEC sp_add_jobstep
    @job_name = N'DB_Maintenance_Full',
    @step_name = N'CleanupOldBackups',
    @subsystem = N'TSQL',
    @command = N'
DECLARE @BackupPath NVARCHAR(500) = N''E:\SQLBackup\Full\'';
DECLARE @RetentionDays INT = 7;
DECLARE @DeleteDate VARCHAR(10) = CONVERT(VARCHAR(10), DATEADD(DAY, -@RetentionDays, GETDATE()), 120);

-- 使用xp_delete_file扩展存储过程删除旧文件
EXEC master.dbo.xp_delete_file 
    0,          -- 文件类型：0=备份文件
    @BackupPath,
    N''bak'',    -- 文件扩展名
    @DeleteDate,
    1;          -- 子目录：1=包含子目录',
    @retry_attempts = 2;
GO

-- 步骤3：验证备份
EXEC sp_add_jobstep
    @job_name = N'DB_Maintenance_Full',
    @step_name = N'VerifyBackup',
    @subsystem = N'TSQL',
    @command = N'
-- 验证最新的备份文件
DECLARE @BackupPath NVARCHAR(500) = N''E:\SQLBackup\Full\'';

DECLARE @VerifySQL NVARCHAR(MAX);
SELECT @VerifySQL = COALESCE(@VerifySQL + CHAR(13) + CHAR(10), N'''') + 
N''RESTORE VERIFYONLY FROM DISK = N'''''' + @BackupPath + name + ''_'' + 
    REPLACE(CONVERT(VARCHAR(20), GETDATE(), 120), '':'', '''') + ''.bak'''';''
FROM sys.databases 
WHERE name NOT IN (''master'', ''model'', ''msdb'', ''tempdb'');

EXEC sp_executesql @VerifySQL;';
GO

-- 创建每日凌晨2点执行的计划
EXEC sp_add_schedule
    @schedule_name = N'Daily_2AM_Schedule',
    @freq_type = 4,
    @freq_interval = 1,
    @active_start_time = 020000,
    @active_end_time = 235959;
GO

EXEC sp_attach_schedule
    @job_name = N'DB_Maintenance_Full',
    @schedule_name = N'Daily_2AM_Schedule';
GO

EXEC sp_add_jobserver
    @job_name = N'DB_Maintenance_Full';
GO

五、监控和维护

1. 监控作业执行状态

-- 查看作业执行历史
SELECT 
    j.name AS JobName,
    h.run_date,
    h.run_time,
    CASE h.run_status
        WHEN 0 THEN '失败'
        WHEN 1 THEN '成功'
        WHEN 2 THEN '重试'
        WHEN 3 THEN '取消'
        WHEN 4 THEN '正在执行'
    END AS Status,
    h.message
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
WHERE j.name LIKE '%Backup%'
ORDER BY h.run_date DESC, h.run_time DESC;

2. 查看备份历史

-- 查看备份历史记录
SELECT 
    database_name,
    backup_start_date,
    backup_finish_date,
    type,  -- D=完整, I=差异, L=日志
    backup_size / 1024 / 1024 AS BackupSizeMB,
    physical_device_name
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
ORDER BY backup_start_date DESC;

3. 检查磁盘空间

-- 监控备份目录磁盘空间
EXEC master.dbo.xp_fixeddrives;

六、最佳实践建议

备份策略：

完整备份：每周一次
差异备份：每天一次
事务日志备份：每15-30分钟一次（完整恢复模式）

存储管理：

使用单独的物理磁盘存放备份
定期清理过期备份文件
考虑异地备份或云存储

验证机制：

定期执行RESTORE VERIFYONLY验证备份完整性
定期进行恢复测试

安全配置：

备份文件设置适当的NTFS权限
考虑备份文件加密

性能优化：

使用压缩减少存储空间
调整BUFFERCOUNT和MAXTRANSFERSIZE参数
在业务低峰期执行备份

七、故障排除

常见问题解决：

SQL Server代理未运行：

# PowerShell启动服务
Start-Service SQLSERVERAGENT

权限不足：

确保运行账户对备份目录有写权限
在SQL Server配置管理器中修改服务账户

磁盘空间不足：

监控磁盘使用率
设置备份保留策略

备份失败：

-- 查看具体错误信息
SELECT * FROM msdb.dbo.sysjobhistory 
WHERE run_status = 0 
ORDER BY run_date DESC;

八、自动化维护脚本

创建完整的维护解决方案：

-- 创建所有维护作业的主控程序
CREATE PROCEDURE dbo.SetupDatabaseMaintenance
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 检查并创建备份目录
    DECLARE @BackupPath NVARCHAR(500) = 'E:\SQLBackup\';

    IF NOT EXISTS (SELECT * FROM sys.dm_os_file_exists(@BackupPath))
    BEGIN
        EXEC master.dbo.xp_create_subdir @BackupPath;
        EXEC master.dbo.xp_create_subdir @BackupPath + 'Full\';
        EXEC master.dbo.xp_create_subdir @BackupPath + 'Diff\';
        EXEC master.dbo.xp_create_subdir @BackupPath + 'Log\';
    END

    -- 2. 设置备份计划（调用前面创建的脚本）
    -- ... 执行前面的创建脚本
END
GO