2026年Ubuntu SQL Server备份策略完整指南(2026)

一、SQL Server备份基础概述

数据备份是数据库管理的核心任务之一。对于运行在Ubuntu上的SQL Server，制定完善的备份策略可以确保业务数据安全，在发生灾难时能够快速恢复。

1.1 备份类型详解

备份类型	说明	备份时间	恢复粒度	存储空间
完整备份	备份整个数据库	最长	任意时间点	最大
差异备份	备份自上次完整备份以来的变更	中等	最近完整备份点	中等
事务日志备份	备份事务日志	最短	精确时间点	增长可控

1.2 备份策略选择原则

小型数据库（<10GB）：
– 每日完整备份
– 简单可靠，恢复快速

中型数据库（10-100GB）：
– 每周完整备份
– 每日差异备份
– 每小时事务日志备份

大型数据库（>100GB）：
– 每周完整备份
– 每日差异备份
– 每15-30分钟事务日志备份

1.3 Ubuntu上SQL Server备份路径

# 默认备份目录
/var/opt/mssql/data/

# 建议创建专用备份目录
sudo mkdir -p /var/opt/mssql/backup
sudo chown mssql:mssql /var/opt/mssql/backup

# 修改备份目录权限
sudo chmod 755 /var/opt/mssql/backup

二、完整备份操作

2.1 基本完整备份

-- 使用SQL命令备份
BACKUP DATABASE [TestDB]
TO DISK = '/var/opt/mssql/backup/TestDB_full.bak'
WITH FORMAT, COMPRESSION, CHECKSUM,
     NAME = 'TestDB-Full Backup';
GO

-- 验证备份完整性
RESTORE VERIFYONLY
FROM DISK = '/var/opt/mssql/backup/TestDB_full.bak';
GO

2.2 使用Shell脚本备份

#!/bin/bash
# full_backup.sh - SQL Server完整备份脚本

# 配置参数
BACKUP_DIR="/var/opt/mssql/backup"
DATE=$(date +%Y%m%d_%H%M%3)
SA_PASSWORD="YourStrongPassword"
LOG_FILE="$BACKUP_DIR/backup_$DATE.log"

# 日志函数
log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a $LOG_FILE
}

log "========== 开始完整备份 =========="

# 获取所有用户数据库
DATABASES=$(/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "$SA_PASSWORD" \
    -Q "SET NOCOUNT ON; SELECT name FROM sys.databases WHERE name NOT IN ('master','model','msdb','tempdb')" \
    -h -1 -W)

for DB in $DATABASES; do
    DB=${DB//$' '/}  # 去除空格
    [ -z "$DB" ] && continue

    BACKUP_FILE="$BACKUP_DIR/${DB}_full_$DATE.bak"

    log "正在备份数据库: $DB"

    /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "$SA_PASSWORD" \
        -Q "BACKUP DATABASE [$DB] TO DISK = N'$BACKUP_FILE' WITH FORMAT, COMPRESSION, CHECKSUM"

    if [ $? -eq 0 ]; then
        SIZE=$(du -h $BACKUP_FILE | cut -f1)
        log "备份成功: $DB ($SIZE)"
    else
        log "备份失败: $DB"
    fi
done

log "========== 备份完成 =========="

2.3 同时备份系统数据库

-- 备份master数据库（每次配置更改后）
BACKUP DATABASE [master]
TO DISK = '/var/opt/mssql/backup/master.bak'
WITH FORMAT, COMPRESSION;
GO

-- 备份msdb数据库（保存作业和计划）
BACKUP DATABASE [msdb]
TO DISK = '/var/opt/mssql/backup/msdb.bak'
WITH FORMAT, COMPRESSION;
GO

-- 备份model数据库（自定义模板）
BACKUP DATABASE [model]
TO DISK = '/var/opt/mssql/backup/model.bak'
WITH FORMAT, COMPRESSION;
GO

三、差异备份与事务日志备份

3.1 差异备份

-- 完整备份后，创建差异备份
BACKUP DATABASE [TestDB]
TO DISK = '/var/opt/mssql/backup/TestDB_diff.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;
GO

-- 查找最近的差异备份
SELECT 
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date) AS duration_minutes,
    CAST(bs.backup_size/1024/1024 AS DECIMAL(10,2)) AS backup_size_mb,
    CASE bs.type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Log'
    END AS backup_type
FROM msdb.dbo.backupset bs
WHERE bs.database_name = 'TestDB'
ORDER BY bs.backup_start_date DESC;
GO

3.2 事务日志备份

-- 设置恢复模式为完整
ALTER DATABASE [TestDB] SET RECOVERY FULL;
GO

-- 备份事务日志
BACKUP LOG [TestDB]
TO DISK = '/var/opt/mssql/backup/TestDB_log.trn'
WITH COMPRESSION, CHECKSUM;
GO

3.3 自动化备份脚本

#!/bin/bash
# incremental_backup.sh - 差异+日志备份脚本

BACKUP_DIR="/var/opt/mssql/backup"
DATE=$(date +%Y%m%d_%H%M%S)
SA_PASSWORD="YourStrongPassword"

# 获取用户数据库列表
DATABASES=$(/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "$SA_PASSWORD" \
    -Q "SET NOCOUNT ON; SELECT name FROM sys.databases WHERE name NOT IN ('master','model','msdb','tempdb') AND recovery_model_desc = 'FULL'" \
    -h -1 -W)

for DB in $DATABASES; do
    DB=${DB//$' '/}
    [ -z "$DB" ] && continue

    # 差异备份
    /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "$SA_PASSWORD" \
        -Q "BACKUP DATABASE [$DB] TO DISK = N'$BACKUP_DIR/${DB}_diff_$DATE.bak' WITH DIFFERENTIAL, COMPRESSION"

    # 事务日志备份
    /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "$SA_PASSWORD" \
        -Q "BACKUP LOG [$DB] TO DISK = N'$BACKUP_DIR/${DB}_log_$DATE.trn' WITH COMPRESSION"

    echo "已备份: $DB"
done

四、备份恢复操作

4.1 完整恢复

-- 恢复到某个时间点
RESTORE DATABASE [TestDB]
FROM DISK = '/var/opt/mssql/backup/TestDB_full.bak'
WITH NORECOVERY;
GO

RESTORE DATABASE [TestDB]
FROM DISK = '/var/opt/mssql/backup/TestDB_diff.bak'
WITH NORECOVERY;
GO

RESTORE LOG [TestDB]
FROM DISK = '/var/opt/mssql/backup/TestDB_log_1.trn'
WITH NORECOVERY;
GO

RESTORE LOG [TestDB]
FROM DISK = '/var/opt/mssql/backup/TestDB_log_2.trn'
WITH RECOVERY, STOPAT = '2026-05-09 10:30:00';
GO

4.2 恢复到指定时间点

-- 恢复到精确时间点
RESTORE DATABASE [TestDB]
FROM DISK = '/var/opt/mssql/backup/TestDB_full.bak'
WITH NORECOVERY;
GO

RESTORE LOG [TestDB]
FROM DISK = '/var/opt/mssql/backup/TestDB_log.trn'
WITH RECOVERY, STOPAT = '2026-05-09 08:00:00';
GO

4.3 恢复验证

-- 检查数据库完整性
DBCC CHECKDB ('TestDB') WITH NO_INFOMSGS;
GO

-- 查看数据库状态
SELECT 
    name,
    state_desc,
    user_access_desc,
    recovery_model_desc,
    compatibility_level
FROM sys.databases
WHERE name = 'TestDB';
GO

五、备份存储与生命周期管理

5.1 本地备份存储策略

# 目录结构规划
/var/opt/mssql/backup/
├── daily/          # 每日备份
├── weekly/         # 每周备份
├── monthly/        # 每月备份
└── archive/        # 归档备份

5.2 自动清理过期备份

#!/bin/bash
# cleanup_backups.sh - 清理过期备份

BACKUP_DIR="/var/opt/mssql/backup"

# 保留策略
DAILY_RETENTION=7      # 每日备份保留7天
WEEKLY_RETENTION=30     # 每周备份保留30天
MONTHLY_RETENTION=365   # 每月备份保留1年

# 清理每日备份
find $BACKUP_DIR/daily -name "*.bak" -mtime +$DAILY_RETENTION -delete
find $BACKUP_DIR/daily -name "*.trn" -mtime +$DAILY_RETENTION -delete

# 清理每周备份
find $BACKUP_DIR/weekly -name "*.bak" -mtime +$WEEKLY_RETENTION -delete

# 清理每月备份
find $BACKUP_DIR/monthly -name "*.bak" -mtime +$MONTHLY_RETENTION -delete

# 记录清理日志
echo "[$(date)] 备份清理完成" >> /var/log/mssql_backup_cleanup.log

5.3 备份压缩

-- 使用压缩备份节省空间（通常可压缩70%）
BACKUP DATABASE [TestDB]
TO DISK = '/var/opt/mssql/backup/TestDB_compressed.bak'
WITH COMPRESSION, CHECKSUM;
GO

-- 查看备份压缩率
SELECT 
    bs.backup_set_id,
    bs.backup_size/1024/1024 AS backup_size_mb,
    bs.compressed_backup_size/1024/1024 AS compressed_size_mb,
    CAST((1 - (bs.compressed_backup_size * 1.0 / bs.backup_size)) * 100 AS DECIMAL(5,2)) AS compression_ratio
FROM msdb.dbo.backupset bs
WHERE bs.database_name = 'TestDB'
ORDER BY bs.backup_start_date DESC;
GO

六、异地备份策略

6.1 SCP远程传输

#!/bin/bash
# remote_backup.sh - 备份到远程服务器

REMOTE_HOST="backup-server.example.com"
REMOTE_USER="backupuser"
REMOTE_DIR="/backup/sqlserver"
LOCAL_BACKUP="/var/opt/mssql/backup"
DATE=$(date +%Y%m%d)

# 创建临时备份
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "$SA_PASSWORD" \
    -Q "BACKUP DATABASE [TestDB] TO DISK = '/tmp/TestDB_${DATE}.bak' WITH COMPRESSION"

# 压缩
tar -czf /tmp/TestDB_${DATE}.tar.gz /tmp/TestDB_${DATE}.bak

# 传输到远程服务器
scp /tmp/TestDB_${DATE}.tar.gz $REMOTE_USER@$REMOTE_HOST:$REMOTE_DIR/

# 清理本地临时文件
rm -f /tmp/TestDB_${DATE}.bak /tmp/TestDB_${DATE}.tar.gz

echo "远程备份完成: $DATE"

6.2 Rsync增量备份

#!/bin/bash
# rsync_backup.sh - 使用rsync增量备份

REMOTE_HOST="backup-server.example.com"
REMOTE_USER="backupuser"
REMOTE_DIR="/backup/sqlserver"
LOCAL_BACKUP="/var/opt/mssql/backup"

# 执行rsync增量同步
rsync -avz --progress \
    -e "ssh -i /home/mssql/.ssh/id_rsa" \
    $LOCAL_BACKUP/ \
    $REMOTE_USER@$REMOTE_HOST:$REMOTE_DIR/

echo "Rsync备份完成: $(date)"

6.3 加密备份

#!/bin/bash
# encrypted_backup.sh - 加密备份

BACKUP_FILE="/var/opt/mssql/backup/TestDB.bak"
ENCRYPTED_FILE="/var/opt/mssql/backup/TestDB_encrypted.bak"
ENCRYPTION_KEY="/etc/mssql/backup.key"

# 创建备份
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "$SA_PASSWORD" \
    -Q "BACKUP DATABASE [TestDB] TO DISK = '$BACKUP_FILE' WITH COMPRESSION"

# 使用GPG加密
gpg --symmetric --cipher-algo AES256 \
    --passphrase-file $ENCRYPTION_KEY \
    --output $ENCRYPTED_FILE \
    $BACKUP_FILE

# 清理明文备份
shred -u $BACKUP_FILE

echo "加密备份完成"

七、定时备份配置（Cron）

7.1 配置Cron任务

# 编辑crontab
crontab -e

# 添加以下任务：

# 每天凌晨2点执行完整备份
0 2 * * * /home/mssql/scripts/full_backup.sh >> /var/log/mssql_full_backup.log 2>&1

# 每天中午12点执行差异备份
0 12 * * * /home/mssql/scripts/diff_backup.sh >> /var/log/mssql_diff_backup.log 2>&1

# 每6小时执行事务日志备份
0 */6 * * * /home/mssql/scripts/log_backup.sh >> /var/log/mssql_log_backup.log 2>&1

# 每周日凌晨3点清理过期备份
0 3 * * 0 /home/mssql/scripts/cleanup_backups.sh >> /var/log/mssql_cleanup.log 2>&1

# 每天凌晨4点同步到远程服务器
0 4 * * * /home/mssql/scripts/remote_backup.sh >> /var/log/mssql_remote_backup.log 2>&1

7.2 Cron日志管理

# 创建日志轮转配置
sudo cat > /etc/logrotate.d/mssql-backup << 'EOF'
/var/log/mssql_*.log {
    daily
    rotate 30
    compress
    delaycompress
    notifempty
    create 0644 mssql mssql
    sharedscripts
}
EOF

八、备份验证与恢复测试

8.1 备份完整性验证

-- 验证备份文件可读性
RESTORE VERIFYONLY
FROM DISK = '/var/opt/mssql/backup/TestDB_full.bak'
WITH CHECKSUM;
GO

-- 查看备份文件信息
SELECT 
    bs.backup_set_id,
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.backup_size/1024/1024 AS backup_size_mb,
    bs.compressed_backup_size/1024/1024 AS compressed_size_mb,
    CASE bs.type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Log'
    END AS backup_type,
    bs.is_copy_only,
    bs.is_damaged
FROM msdb.dbo.backupset bs
WHERE bs.database_name = 'TestDB'
ORDER BY bs.backup_start_date DESC;
GO

8.2 定期恢复测试

#!/bin/bash
# test_restore.sh - 测试恢复脚本

TEST_DB="TestDB_Restore_Test"
BACKUP_FILE="/var/opt/mssql/backup/TestDB_full.bak"
SA_PASSWORD="YourStrongPassword"

# 删除测试数据库（如存在）
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "$SA_PASSWORD" \
    -Q "IF EXISTS (SELECT name FROM sys.databases WHERE name = '$TEST_DB') ALTER DATABASE [$TEST_DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [$TEST_DB]"

# 从备份恢复
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "$SA_PASSWORD" \
    -Q "RESTORE DATABASE [$TEST_DB] FROM DISK = '$BACKUP_FILE' WITH MOVE 'TestDB' TO '/var/opt/mssql/data/TestDB_Restore_Test.mdf', MOVE 'TestDB_log' TO '/var/opt/mssql/data/TestDB_Restore_Test_log.ldf'"

# 检查恢复结果
if [ $? -eq 0 ]; then
    /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "$SA_PASSWORD" \
        -Q "DBCC CHECKDB ('$TEST_DB') WITH NO_INFOMSGS"

    # 清理测试数据库
    /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "$SA_PASSWORD" \
        -Q "ALTER DATABASE [$TEST_DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [$TEST_DB]"

    echo "[$(date)] 恢复测试成功"
else
    echo "[$(date)] 恢复测试失败"
fi

8.3 备份健康检查

-- 检查备份历史
SELECT 
    bs.database_name,
    bs.backup_start_date,
    DATEDIFF(HOUR, bs.backup_finish_date, GETDATE()) AS hours_since_backup,
    CASE 
        WHEN DATEDIFF(HOUR, bs.backup_finish_date, GETDATE()) > 48 THEN '⚠️ 超过48小时未备份'
        ELSE '✅ 备份正常'
    END AS backup_status,
    CASE bs.type
        WHEN 'D' THEN '完整备份'
        WHEN 'I' THEN '差异备份'
        WHEN 'L' THEN '日志备份'
    END AS backup_type
FROM msdb.dbo.backupset bs
INNER JOIN (
    SELECT database_name, MAX(backup_start_date) AS max_date
    FROM msdb.dbo.backupset
    GROUP BY database_name
) latest ON bs.database_name = latest.database_name 
    AND bs.backup_start_date = latest.max_date
WHERE bs.database_name NOT IN ('master','model','msdb','tempdb')
ORDER BY bs.database_name;
GO

九、备份策略模板

9.1 小型数据库策略

# 每日凌晨2点完整备份
0 2 * * * /home/mssql/scripts/full_backup.sh >> /var/log/mssql_full.log 2>&1

9.2 中型数据库策略

# 每周日凌晨2点完整备份
0 2 * * 0 /home/mssql/scripts/full_backup.sh >> /var/log/mssql_full.log 2>&1

# 每天凌晨2点差异备份（周日除外）
0 2 * * 1-6 /home/mssql/scripts/diff_backup.sh >> /var/log/mssql_diff.log 2>&1

# 每4小时日志备份
0 */4 * * * /home/mssql/scripts/log_backup.sh >> /var/log/mssql_log.log 2>&1

9.3 大型数据库策略

# 每周完整备份（周日）
0 2 * * 0 /home/mssql/scripts/full_backup.sh >> /var/log/mssql_full.log 2>&1

# 工作日每天差异备份（周一至周六）
0 2 * * 1-6 /home/mssql/scripts/diff_backup.sh >> /var/log/mssql_diff.log 2>&1

# 每15分钟日志备份
0,15,30,45 * * * * /home/mssql/scripts/log_backup.sh >> /var/log/mssql_log.log 2>&1

# 每天同步到远程
0 4 * * * /home/mssql/scripts/remote_backup.sh >> /var/log/mssql_remote.log 2>&1

十、常见问题解答

Q1: 备份失败怎么排查？

A: 常见原因和解决方法：
– 磁盘空间不足 → 清理空间或更改备份目录
– 权限问题 → 检查/var/opt/mssql/backup目录权限
– 数据库正在使用 → 使用WITH COPY_ONLY选项
– 备份文件已存在 → 使用WITH INIT覆盖

Q2: 能否在线备份而不影响业务？

A: 可以。SQL Server支持在线备份，使用COMPRESSION选项减少I/O影响。对于高并发场景，建议在业务低峰期执行备份。

Q3: 备份文件可以删除后重建吗？

A: 不可以。备份文件一旦删除，该时间点之后的所有增量备份都无法使用。建议使用备份保留策略自动清理，而非手动删除。

Q4: 如何加快备份速度？

A: 优化方法：
– 使用固态硬盘作为备份目标
– 启用备份压缩（减少I/O）
– 使用多个备份文件并行备份
– 分离数据和日志文件到不同磁盘

Q5: 备份可以加密吗？

A: 可以。使用TDE（透明数据加密）或第三方加密工具。但需要注意加密后的备份无法直接恢复，需要配合解密过程。

十一、总结

制定完善的SQL Server备份策略需要考虑：

选择合适的备份类型组合：完整+差异+日志
设置合理的备份频率：根据RPO和RTO要求
规划备份存储策略：本地+远程双重保护
建立备份验证机制：定期测试恢复能力
实施备份生命周期管理：自动清理过期备份

掌握以上备份策略，可以确保Ubuntu上的SQL Server数据安全，在发生灾难时能够快速恢复业务。

注：本文基于SQL Server 2022 on Ubuntu 20.04/22.04编写，具体操作请以实际环境为准。