SQL Server备份与恢复 #

SQL Server备份类型：
├── 完整备份（Full Backup）
│   └── 备份整个数据库
├── 差异备份（Differential Backup）
│   └── 备份自上次完整备份后的变化
├── 事务日志备份（Transaction Log Backup）
│   └── 备份事务日志
├── 尾日志备份（Tail-Log Backup）
│   └── 备份未备份的日志
└── 文件/文件组备份
    └── 备份特定文件或文件组

恢复模式：
├── SIMPLE（简单）
│   ├── 不支持日志备份
│   ├── 自动截断日志
│   └── 只能恢复到备份点
├── FULL（完整）
│   ├── 支持日志备份
│   ├── 可恢复到任意时间点
│   └── 需要定期备份日志
└── BULK_LOGGED（大容量日志）
    ├── 大容量操作最小日志
    └── 可恢复但有限制

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

-- 设置恢复模式
ALTER DATABASE mydb SET RECOVERY SIMPLE;
ALTER DATABASE mydb SET RECOVERY FULL;
ALTER DATABASE mydb SET RECOVERY BULK_LOGGED;

-- 基本完整备份
BACKUP DATABASE mydb
TO DISK = 'D:\Backup\mydb_full.bak';

-- 带选项的完整备份
BACKUP DATABASE mydb
TO DISK = 'D:\Backup\mydb_full.bak'
WITH 
    FORMAT,              -- 格式化介质
    MEDIANAME = 'mydb_backup',
    NAME = 'mydb_full_backup',
    DESCRIPTION = 'Full backup of mydb',
    COMPRESSION,         -- 压缩备份
    STATS = 10;          -- 每10%显示进度

-- 备份到多个文件
BACKUP DATABASE mydb
TO DISK = 'D:\Backup\mydb_full_1.bak',
   DISK = 'D:\Backup\mydb_full_2.bak'
WITH FORMAT;

-- 查看备份文件信息
RESTORE HEADERONLY FROM DISK = 'D:\Backup\mydb_full.bak';

-- 查看备份文件列表
RESTORE FILELISTONLY FROM DISK = 'D:\Backup\mydb_full.bak';

-- 查看备份标签
RESTORE LABELONLY FROM DISK = 'D:\Backup\mydb_full.bak';

-- 查看备份历史
SELECT 
    database_name,
    backup_start_date,
    backup_finish_date,
    type,
    backup_size
FROM msdb.dbo.backupset
ORDER BY backup_start_date DESC;

-- 先创建完整备份
BACKUP DATABASE mydb
TO DISK = 'D:\Backup\mydb_full.bak'
WITH FORMAT;

-- 创建差异备份
BACKUP DATABASE mydb
TO DISK = 'D:\Backup\mydb_diff.bak'
WITH DIFFERENTIAL;

-- 带选项的差异备份
BACKUP DATABASE mydb
TO DISK = 'D:\Backup\mydb_diff.bak'
WITH 
    DIFFERENTIAL,
    NAME = 'mydb_differential_backup',
    DESCRIPTION = 'Differential backup',
    COMPRESSION;

差异备份策略：
完整备份（周日）
├── 差异备份（周一）
├── 差异备份（周二）
├── 差异备份（周三）
├── 差异备份（周四）
├── 差异备份（周五）
└── 差异备份（周六）

优点：恢复时只需完整备份+最新差异备份
缺点：差异备份会越来越大

-- 数据库必须是FULL恢复模式
ALTER DATABASE mydb SET RECOVERY FULL;

-- 创建完整备份
BACKUP DATABASE mydb
TO DISK = 'D:\Backup\mydb_full.bak'
WITH FORMAT;

-- 创建日志备份
BACKUP LOG mydb
TO DISK = 'D:\Backup\mydb_log_1.trn';

-- 带选项的日志备份
BACKUP LOG mydb
TO DISK = 'D:\Backup\mydb_log_2.trn'
WITH 
    NAME = 'mydb_log_backup',
    DESCRIPTION = 'Transaction log backup',
    COMPRESSION;

日志备份策略：
完整备份（周日）
├── 日志备份（周一每小时）
├── 日志备份（周二每小时）
├── ...
└── 日志备份（周六每小时）

优点：可恢复到任意时间点
缺点：需要管理多个日志文件

-- 数据库损坏时备份尾日志
BACKUP LOG mydb
TO DISK = 'D:\Backup\mydb_tail.trn'
WITH NORECOVERY, NO_TRUNCATE;

-- 正常尾日志备份
BACKUP LOG mydb
TO DISK = 'D:\Backup\mydb_tail.trn'
WITH NORECOVERY;

-- 恢复完整备份
RESTORE DATABASE mydb
FROM DISK = 'D:\Backup\mydb_full.bak'
WITH 
    MOVE 'mydb' TO 'D:\Data\mydb.mdf',
    MOVE 'mydb_log' TO 'D:\Log\mydb.ldf',
    REPLACE,           -- 替换现有数据库
    RECOVERY;          -- 恢复后可访问

-- 恢复到不同数据库名
RESTORE DATABASE mydb_new
FROM DISK = 'D:\Backup\mydb_full.bak'
WITH 
    MOVE 'mydb' TO 'D:\Data\mydb_new.mdf',
    MOVE 'mydb_log' TO 'D:\Log\mydb_new.ldf';

-- 恢复完整备份（NORECOVERY）
RESTORE DATABASE mydb
FROM DISK = 'D:\Backup\mydb_full.bak'
WITH NORECOVERY;

-- 恢复差异备份
RESTORE DATABASE mydb
FROM DISK = 'D:\Backup\mydb_diff.bak'
WITH RECOVERY;

-- 恢复完整备份
RESTORE DATABASE mydb
FROM DISK = 'D:\Backup\mydb_full.bak'
WITH NORECOVERY;

-- 恢复日志到指定时间点
RESTORE LOG mydb
FROM DISK = 'D:\Backup\mydb_log_1.trn'
WITH NORECOVERY;

RESTORE LOG mydb
FROM DISK = 'D:\Backup\mydb_log_2.trn'
WITH 
    STOPAT = '2024-01-15 14:30:00',
    RECOVERY;

-- 在事务中设置标记
BEGIN TRANSACTION UpdateData WITH MARK 'BeforeUpdate';
-- 操作
COMMIT TRANSACTION;

-- 恢复到标记
RESTORE LOG mydb
FROM DISK = 'D:\Backup\mydb_log.trn'
WITH 
    STOPATMARK = 'BeforeUpdate',
    RECOVERY;

-- 创建永久备份设备
EXEC sp_addumpdevice 'disk', 'mydb_backup', 'D:\Backup\mydb.bak';

-- 使用备份设备
BACKUP DATABASE mydb TO mydb_backup;

-- 查看备份设备
SELECT * FROM sys.backup_devices;

-- 删除备份设备
EXEC sp_dropdevice 'mydb_backup';

-- 镜像备份到多个位置
BACKUP DATABASE mydb
TO DISK = 'D:\Backup\mydb.bak'
MIRROR TO DISK = 'E:\Backup\mydb.bak'
WITH FORMAT;

SSMS创建维护计划：
1. 展开 Management → Maintenance Plans
2. 右键 → New Maintenance Plan
3. 添加备份任务
4. 设置计划
5. 配置通知

-- 创建作业
USE msdb;
GO

EXEC sp_add_job 
    @job_name = 'Daily Full Backup';

EXEC sp_add_jobstep
    @job_name = 'Daily Full Backup',
    @step_name = 'Backup mydb',
    @subsystem = 'TSQL',
    @command = 'BACKUP DATABASE mydb TO DISK = ''D:\Backup\mydb.bak'' WITH COMPRESSION';

EXEC sp_add_schedule
    @schedule_name = 'Daily at 2 AM',
    @freq_type = 4,  -- 每天
    @active_start_time = 20000;  -- 02:00:00

EXEC sp_attach_schedule
    @job_name = 'Daily Full Backup',
    @schedule_name = 'Daily at 2 AM';

EXEC sp_add_jobserver
    @job_name = 'Daily Full Backup';

-- 服务器级别启用压缩
EXEC sp_configure 'backup compression default', 1;
RECONFIGURE;

-- 单次备份压缩
BACKUP DATABASE mydb
TO DISK = 'D:\Backup\mydb.bak'
WITH COMPRESSION;

-- 禁用压缩
BACKUP DATABASE mydb
TO DISK = 'D:\Backup\mydb.bak'
WITH NO_COMPRESSION;

压缩效果：
├── 通常可压缩50%-70%
├── 压缩会增加CPU使用
├── 减少I/O时间
└── 减少存储空间

备份策略建议：
├── 生产环境使用FULL恢复模式
├── 定期完整备份（每天）
├── 频繁日志备份（每小时）
├── 定期测试恢复
├── 异地存储备份
├── 加密敏感备份
└── 监控备份状态

恢复策略建议：
├── 制定恢复计划
├── 定期测试恢复
├── 记录恢复步骤
├── 准备备用服务器
└── 监控恢复时间