备份、恢复与日常运维

7.1 恢复模式与事务日志管理

7.1.1 三种恢复模式对比

-- 查看并修改恢复模式
SELECT name, recovery_model_desc FROM sys.databases;

-- 切换到完整恢复模式（生产数据库）
ALTER DATABASE [YourDB] SET RECOVERY FULL;

-- 切换到简单模式（开发数据库）
ALTER DATABASE [DevDB] SET RECOVERY SIMPLE;

-- ⚠️ 从简单模式切换到完整模式后，
-- 必须立即做一次完整备份，否则日志链断裂，无法做日志备份！
ALTER DATABASE [YourDB] SET RECOVERY FULL;
BACKUP DATABASE [YourDB] TO DISK = 'E:\Backup\YourDB_SWITCH.bak'
WITH FORMAT, COMPRESSION, CHECKSUM;

7.1.2 事务日志管理

-- 查看日志使用率（所有数据库）
DBCC SQLPERF(LOGSPACE);

-- 查看日志未截断原因（log_reuse_wait_desc）
SELECT name,
       log_reuse_wait_desc,  -- NOTHING=正常, LOG_BACKUP=等待日志备份, ACTIVE_TRANSACTION=有长事务
       log_size_mb     = (SELECT SUM(size)*8.0/1024 FROM sys.database_files WHERE type=1),
       CAST(DATABASEPROPERTYEX(name,'LogReuseWaitDesc') AS NVARCHAR(50)) AS ReuseWait
FROM sys.databases
WHERE database_id > 4;

-- 收缩日志文件（仅在截断后生效，临时手段，不建议在生产频繁操作）
USE [YourDB];

-- 先做一次日志备份（触发截断）
BACKUP LOG [YourDB] TO DISK = 'NUL';  -- NUL 是 Windows 虚拟设备，丢弃备份（仅测试用）

-- 查看逻辑日志文件名
SELECT name, size * 8.0/1024 AS SizeMB FROM sys.database_files WHERE type = 1;

-- 收缩到目标大小（MB）
DBCC SHRINKFILE ('YourDB_log', 512);  -- 收缩到512MB

-- ⚠️ 永久解决日志过大：检查是否有未完成的长事务或复制延迟

7.2 备份策略设计

7.2.1 备份类型

恢复目标：最大丢失 15 分钟数据
建议策略：
  ├── 完整备份（每天凌晨2:00）
  ├── 差异备份（每4小时：6:00, 10:00, 14:00, 18:00, 22:00）
  └── 日志备份（每15分钟）

恢复时：完整 + 最新差异 + 差异后的所有日志备份

7.2.2 T-SQL 备份命令

-- ① 完整备份（WITH COMPRESSION 压缩，一般可减少50-75%大小）
BACKUP DATABASE [YourDB]
TO DISK = 'E:\Backup\YourDB_Full_20240615.bak'
WITH
    FORMAT,                    -- 创建新媒体集，覆盖现有
    NAME = 'YourDB Full Backup 2024-06-15',
    COMPRESSION,               -- 压缩（建议始终开启）
    CHECKSUM,                  -- 写入校验和（还原时验证完整性）
    STATS = 10;                -- 每完成10%报告进度

-- ② 差异备份
BACKUP DATABASE [YourDB]
TO DISK = 'E:\Backup\YourDB_Diff_20240615_1000.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM, STATS = 10;

-- ③ 事务日志备份
BACKUP LOG [YourDB]
TO DISK = 'E:\Backup\YourDB_Log_202406151015.bak'
WITH COMPRESSION, CHECKSUM, STATS = 10;

-- ④ 备份到多个文件（并行写，速度提升）
BACKUP DATABASE [YourDB]
TO DISK = 'E:\Backup\YourDB1.bak',
   DISK = 'F:\Backup\YourDB2.bak',
   DISK = 'G:\Backup\YourDB3.bak'
WITH FORMAT, COMPRESSION, CHECKSUM;

-- ⑤ 仅复制备份（不影响差异基准线，适合临时迁移）
BACKUP DATABASE [YourDB]
TO DISK = 'E:\Backup\YourDB_CopyOnly.bak'
WITH COPY_ONLY, COMPRESSION, CHECKSUM;

-- 验证备份文件完整性
RESTORE VERIFYONLY
FROM DISK = 'E:\Backup\YourDB_Full_20240615.bak'
WITH CHECKSUM;

-- 查看备份文件中的内容
RESTORE HEADERONLY FROM DISK = 'E:\Backup\YourDB_Full_20240615.bak';
RESTORE FILELISTONLY FROM DISK = 'E:\Backup\YourDB_Full_20240615.bak';
RESTORE LABELONLY FROM DISK = 'E:\Backup\YourDB_Full_20240615.bak';

7.2.3 自动化备份脚本（SQL Server Agent 作业）

-- 创建标准化的自动备份存储过程
CREATE PROCEDURE dbo.usp_DatabaseBackup
    @DatabaseName  NVARCHAR(100),
    @BackupType    CHAR(1),          -- 'F'=Full, 'D'=Differential, 'L'=Log
    @BackupDir     NVARCHAR(256) = 'E:\SQLBackup',
    @RetainDays    INT = 30          -- 保留天数
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @FileName   NVARCHAR(500);
    DECLARE @BackupName NVARCHAR(200);
    DECLARE @SQL        NVARCHAR(MAX);
    DECLARE @Timestamp  VARCHAR(20) = CONVERT(VARCHAR(20), GETDATE(), 112) +
                                      REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '');

    SET @FileName = @BackupDir + '\' + @DatabaseName + '_' +
                    CASE @BackupType
                        WHEN 'F' THEN 'Full'
                        WHEN 'D' THEN 'Diff'
                        WHEN 'L' THEN 'Log'
                    END + '_' + @Timestamp + '.bak';

    SET @BackupName = @DatabaseName + ' ' +
                    CASE @BackupType
                        WHEN 'F' THEN 'Full'
                        WHEN 'D' THEN 'Differential'
                        WHEN 'L' THEN 'Log'
                    END + ' Backup ' + @Timestamp;

    -- 执行备份
    SET @SQL = CASE @BackupType
        WHEN 'F' THEN N'BACKUP DATABASE ' + QUOTENAME(@DatabaseName)
        WHEN 'D' THEN N'BACKUP DATABASE ' + QUOTENAME(@DatabaseName) + N' WITH DIFFERENTIAL,'
        WHEN 'L' THEN N'BACKUP LOG ' + QUOTENAME(@DatabaseName)
    END;

    SET @SQL += CASE WHEN @BackupType IN ('F','D') THEN N'' ELSE N'' END;

    SET @SQL = CASE @BackupType
        WHEN 'F' THEN N'BACKUP DATABASE ' + QUOTENAME(@DatabaseName)
        WHEN 'D' THEN N'BACKUP DATABASE ' + QUOTENAME(@DatabaseName)
        WHEN 'L' THEN N'BACKUP LOG ' + QUOTENAME(@DatabaseName)
    END +
    N' TO DISK = ' + QUOTENAME(@FileName, '''') +
    N' WITH ' +
    CASE @BackupType WHEN 'D' THEN N'DIFFERENTIAL, ' ELSE N'' END +
    N'COMPRESSION, CHECKSUM, FORMAT, NAME = ' + QUOTENAME(@BackupName, '''') +
    N', STATS = 10';

    PRINT '执行备份: ' + @SQL;
    EXEC sp_executesql @SQL;

    -- 删除过期备份文件
    DECLARE @DelCmd NVARCHAR(500) = 'FORFILES /P "' + @BackupDir +
        '" /M "' + @DatabaseName + '_*.bak" /D -' + CAST(@RetainDays AS VARCHAR) + ' /C "cmd /c del @path"';
    EXEC xp_cmdshell @DelCmd, NO_OUTPUT;

    -- 记录备份历史到自定义表（可选）
    INSERT INTO dbo.BackupHistory (DatabaseName, BackupType, FileName, BackupTime, FileSizeMB)
    SELECT @DatabaseName, @BackupType, @FileName, GETDATE(),
           backup_size / 1048576.0
    FROM msdb.dbo.backupset
    WHERE database_name = @DatabaseName
    ORDER BY backup_start_date DESC
    OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;
END;
GO

7.3 还原策略与灾难恢复

7.3.1 完整还原流程

-- ① 查看备份集内容（确认文件路径）
RESTORE FILELISTONLY FROM DISK = 'E:\Backup\YourDB_Full.bak';
-- 返回：LogicalName(YourDB, YourDB_log), PhysicalName, Type(D/L)

-- ② 完整备份 + 差异备份 + 日志备份的还原（最常见场景）

-- 步骤1：还原完整备份（WITH NORECOVERY = 保持恢复状态，允许继续应用后续备份）
RESTORE DATABASE [YourDB_Restored]
FROM DISK = 'E:\Backup\YourDB_Full_20240615.bak'
WITH
    MOVE 'YourDB'     TO 'D:\Data\YourDB_Restored.mdf',
    MOVE 'YourDB_log' TO 'F:\Log\YourDB_Restored_log.ldf',
    NORECOVERY,        -- 不进入可用状态，等待后续备份
    REPLACE,           -- 覆盖已存在的数据库
    STATS = 10;

-- 步骤2：应用差异备份（WITH NORECOVERY）
RESTORE DATABASE [YourDB_Restored]
FROM DISK = 'E:\Backup\YourDB_Diff_20240615_1000.bak'
WITH NORECOVERY, STATS = 10;

-- 步骤3：应用所有日志备份（按时间顺序！）
RESTORE LOG [YourDB_Restored]
FROM DISK = 'E:\Backup\YourDB_Log_202406151015.bak'
WITH NORECOVERY;

RESTORE LOG [YourDB_Restored]
FROM DISK = 'E:\Backup\YourDB_Log_202406151030.bak'
WITH NORECOVERY;

-- ... 继续应用后续日志备份 ...

-- 步骤4：最后一个日志用 RECOVERY 让数据库上线（或 STOPAT 指定时间点）
RESTORE LOG [YourDB_Restored]
FROM DISK = 'E:\Backup\YourDB_Log_202406151045.bak'
WITH RECOVERY;                    -- 完成还原，数据库变为可用
-- 或时间点还原：
-- WITH STOPAT = '2024-06-15 10:43:00', RECOVERY;

7.3.2 时间点还原（Point-in-Time Recovery）

-- 场景：误删数据，需要恢复到删除操作发生前的时间点

-- 1. 先做一个"尾日志备份"（备份当前还没有备份的日志）
BACKUP LOG [YourDB]
TO DISK = 'E:\Backup\YourDB_TailLog.bak'
WITH NORECOVERY,  -- 让数据库进入还原状态（此后不能访问）
     NO_TRUNCATE; -- 即使数据文件损坏也尝试备份

-- 2. 按正常流程还原：Full + Diff + Log + TailLog
-- 在最后一步的日志还原中指定 STOPAT

-- 确定误操作时间点（从扩展事件或审计日志查找）
-- 假设误删在 10:30:15
RESTORE LOG [YourDB]
FROM DISK = 'E:\Backup\YourDB_TailLog.bak'
WITH STOPAT = '2024-06-15 10:30:14',  -- 停在删除操作前1秒
     RECOVERY;

7.3.3 页级还原（Page-Level Restore）

-- 场景：仅少数数据页损坏，不需要整库还原
-- SQL Server Enterprise 版支持在线页级还原

-- 1. 找出损坏的页
DBCC CHECKDB('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS;
-- 或从错误日志中找: Error 823/824

-- 假设发现页 (1:245) 损坏（文件1，页245）

-- 2. 页级还原（数据库保持联机！）
RESTORE DATABASE [YourDB] PAGE = '1:245'
FROM DISK = 'E:\Backup\YourDB_Full_20240615.bak'
WITH NORECOVERY;

-- 3. 应用后续所有日志备份
RESTORE LOG [YourDB] FROM DISK = 'E:\Backup\YourDB_Log_*.bak'
WITH NORECOVERY;

-- 4. 尾日志备份 + 最终恢复
BACKUP LOG [YourDB] TO DISK = 'E:\Backup\YourDB_TailLog_PageRestore.bak' WITH NORECOVERY;
RESTORE LOG [YourDB] FROM DISK = 'E:\Backup\YourDB_TailLog_PageRestore.bak' WITH RECOVERY;

7.4 SQL Server Agent 自动化运维

7.4.1 创建自动化备份作业

-- 使用 msdb 系统存储过程创建 SQL Agent 作业

-- 1. 创建作业
EXEC msdb.dbo.sp_add_job
    @job_name = N'[DBA] Daily Full Backup',
    @description = N'每日完整备份所有用户数据库',
    @owner_login_name = N'sa';

-- 2. 添加作业步骤
EXEC msdb.dbo.sp_add_jobstep
    @job_name  = N'[DBA] Daily Full Backup',
    @step_name = N'Execute Full Backup',
    @subsystem = N'TSQL',
    @command   = N'
DECLARE @DB NVARCHAR(100);
DECLARE db_cursor CURSOR FOR
    SELECT name FROM sys.databases
    WHERE database_id > 4 AND state_desc = ''ONLINE'' AND is_read_only = 0;
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DB;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC dbo.usp_DatabaseBackup @DatabaseName = @DB, @BackupType = ''F'';
    FETCH NEXT FROM db_cursor INTO @DB;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;',
    @on_success_action = 1,   -- 成功则继续
    @on_fail_action = 2,      -- 失败则退出并报告失败
    @retry_attempts = 1,
    @retry_interval = 5;      -- 失败后等5分钟重试

-- 3. 创建调度计划（每天凌晨2点）
EXEC msdb.dbo.sp_add_jobschedule
    @job_name           = N'[DBA] Daily Full Backup',
    @name               = N'Daily at 02:00',
    @freq_type          = 4,      -- 每天
    @freq_interval      = 1,      -- 每1天
    @active_start_time  = 020000, -- 凌晨2:00:00
    @active_end_time    = 235959;

-- 4. 将作业关联到目标服务器
EXEC msdb.dbo.sp_add_jobserver
    @job_name   = N'[DBA] Daily Full Backup',
    @server_name = N'(LOCAL)';

-- 5. 配置操作员和警报（失败时发送邮件）
EXEC msdb.dbo.sp_add_operator
    @name                 = N'DBA Team',
    @email_address        = N'dba@company.com',
    @pager_address        = N'dba-oncall@company.com',
    @weekday_pager_start_time = 090000,
    @weekday_pager_end_time   = 180000;

-- 为作业添加失败通知
EXEC msdb.dbo.sp_update_job
    @job_name            = N'[DBA] Daily Full Backup',
    @notify_level_email  = 2,  -- 失败时发邮件
    @notify_email_operator_name = N'DBA Team';

7.4.2 数据库完整性检查（DBCC CHECKDB）

-- 基本完整性检查（检查所有用户数据库）
DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- 快速检查（只检查分配结构，不检查逻辑一致性）
DBCC CHECKALLOC WITH NO_INFOMSGS;
DBCC CHECKTABLE('dbo.Orders') WITH NO_INFOMSGS;

-- 创建自动化完整性检查作业（建议每周一次）
-- 命令：DBCC CHECKDB('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY

-- 修复轻微损坏（最后手段，会丢失数据！）
ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB('YourDB', REPAIR_REBUILD);      -- 不丢数据的修复
-- DBCC CHECKDB('YourDB', REPAIR_ALLOW_DATA_LOSS);  -- 允许丢失数据（极端情况）
ALTER DATABASE [YourDB] SET MULTI_USER;

7.5 小结

下一章：主从复制与高可用架构