2026年CentOS上SQL Server数据备份与恢复完整指南(2026)

发表评论

一、SQL Server备份恢复概述

数据备份与恢复是数据库管理中最关键的环节。SQL Server on Linux提供完整的备份恢复机制，支持完整备份、差异备份、日志备份和部分恢复等多种策略。合理的备份策略可以有效防止数据丢失，确保业务连续性。

备份类型：
– 完整备份（Full Backup）：备份整个数据库，包含所有数据和日志
– 差异备份（Differential Backup）：备份自上次完整备份以来的所有更改
– 日志备份（Log Backup）：备份事务日志，支持时间点恢复
– 文件/文件组备份：备份特定文件或文件组，适合大型数据库

备份存储位置：
– 本地磁盘：/var/opt/mssql/data/
– 网络存储：SMB共享、NFS挂载
– 云存储：Azure Blob Storage等

二、备份前置准备

2.1 创建备份目录

# 创建备份目录
sudo mkdir -p /var/opt/mssql/backup
sudo mkdir -p /var/opt/mssql/backup/full
sudo mkdir -p /var/opt/mssql/backup/diff
sudo mkdir -p /var/opt/mssql/backup/log

# 设置目录权限
sudo chown mssql:mssql /var/opt/mssql/backup
sudo chmod 755 /var/opt/mssql/backup

# 验证目录
ls -la /var/opt/mssql/backup/

2.2 配置备份路径

# 配置SQL Server默认备份路径
sudo /opt/mssql/bin/mssql-conf set backupdirectory /var/opt/mssql/backup

# 验证配置
sudo /opt/mssql/bin/mssql-conf get backupdirectory

# 重启SQL Server生效
sudo systemctl restart mssql-server

2.3 检查磁盘空间

# 查看可用磁盘空间
df -h /var/opt/mssql/backup

# 查看数据库大小
sqlcmd -S localhost -U sa -P "YourStrong@Password" -Q "
SELECT 
    DB_NAME(database_id) AS DatabaseName,
    CAST(SUM(size) * 8.0 / 1024 AS DECIMAL(10,2)) AS SizeMB
FROM sys.master_files
WHERE database_id > 4
GROUP BY database_id;
" -C

三、完整备份

3.1 使用T-SQL进行完整备份

-- 完整备份单个数据库
BACKUP DATABASE TestDB
TO DISK = '/var/opt/mssql/backup/full/TestDB_Full_20260511.bak'
WITH COMPRESSION, CHECKSUM, NAME = 'TestDB Full Backup';
GO

-- 完整备份所有用户数据库
DECLARE @dbname NVARCHAR(100);
DECLARE @backupPath NVARCHAR(500);
DECLARE @sql NVARCHAR(MAX);

SET @backupPath = '/var/opt/mssql/backup/full/';

DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb');

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'BACKUP DATABASE [' + @dbname + '] TO DISK = ''' + @backupPath + @dbname + '_Full_' + FORMAT(GETDATE(), 'yyyyMMdd_HHmmss') + '.bak'' WITH COMPRESSION, CHECKSUM';
    EXEC sp_executesql @sql;

    FETCH NEXT FROM db_cursor INTO @dbname;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;
GO

3.2 使用sqlcmd命令行备份

# 备份单个数据库
sqlcmd -S localhost -U sa -P "YourStrong@Password" -C -Q "
BACKUP DATABASE TestDB 
TO DISK = '/var/opt/mssql/backup/full/TestDB_Full.bak' 
WITH COMPRESSION, CHECKSUM;
"

# 备份系统数据库（master）
sqlcmd -S localhost -U sa -P "YourStrong@Password" -C -Q "
BACKUP DATABASE master 
TO DISK = '/var/opt/mssql/backup/full/master_Full.bak' 
WITH COMPRESSION, CHECKSUM;
"

# 查看备份文件
ls -lh /var/opt/mssql/backup/full/

3.3 备份选项详解

选项 说明 建议
COMPRESSION 压缩备份文件 生产环境建议启用
CHECKSUM 验证备份完整性 始终启用
BLOCKSIZE 块大小（字节） 默认自动选择
BUFFERCOUNT 缓冲区数量 根据内存调整
MAXTRANSFERSIZE 最大传输单元 性能调优参数

四、差异备份

4.1 执行差异备份

-- 差异备份
BACKUP DATABASE TestDB
TO DISK = '/var/opt/mssql/backup/diff/TestDB_Diff_20260511.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM,
     NAME = 'TestDB Differential Backup';
GO

-- 差异备份脚本（自动化）
DECLARE @dbname NVARCHAR(100);
DECLARE @backupPath NVARCHAR(500);
DECLARE @sql NVARCHAR(MAX);

SET @backupPath = '/var/opt/mssql/backup/diff/';

DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb');

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'BACKUP DATABASE [' + @dbname + '] TO DISK = ''' + @backupPath + @dbname + '_Diff_' + FORMAT(GETDATE(), 'yyyyMMdd_HHmmss') + '.bak'' WITH DIFFERENTIAL, COMPRESSION, CHECKSUM';
    EXEC sp_executesql @sql;

    FETCH NEXT FROM db_cursor INTO @dbname;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;
GO

4.2 差异备份策略

适用场景：
– 数据库较大，完整备份耗时较长
– 数据变更频繁但可接受一定数据丢失
– 需要平衡备份时间和恢复时间

备份频率建议：
– 完整备份：每周一次（业务低峰期）
– 差异备份：每天一次
– 日志备份：每15-30分钟一次

五、日志备份

5.1 执行日志备份

-- 日志备份（需要恢复模式为FULL）
ALTER DATABASE TestDB SET RECOVERY FULL;

-- 首次日志备份（需要先做完整备份）
BACKUP LOG TestDB
TO DISK = '/var/opt/mssql/backup/log/TestDB_Log_20260511_090000.trn'
WITH COMPRESSION, CHECKSUM;
GO

-- 后续日志备份
BACKUP LOG TestDB
TO DISK = '/var/opt/mssql/backup/log/TestDB_Log_20260511_093000.trn'
WITH COMPRESSION, CHECKSUM;
GO

5.2 日志备份与时间点恢复

-- 查看当前日志序列
SELECT 
    last_log_backup_lsn,
    database_id,
    name
FROM sys.databases
WHERE name = 'TestDB';

-- 日志备份支持恢复到特定时间点
-- 需要按顺序恢复：完整备份 → 差异备份 → 日志备份

六、数据恢复操作

6.1 完整恢复

-- 恢复完整备份
RESTORE DATABASE TestDB
FROM DISK = '/var/opt/mssql/backup/full/TestDB_Full_20260511.bak'
WITH REPLACE, RECOVERY;
GO

6.2 恢复到新数据库

-- 恢复到新数据库（不同名称）
RESTORE DATABASE TestDB_Restore
FROM DISK = '/var/opt/mssql/backup/full/TestDB_Full_20260511.bak'
WITH MOVE 'TestDB_Data' TO '/var/opt/mssql/data/TestDB_Restore.mdf',
     MOVE 'TestDB_Log' TO '/var/opt/mssql/data/TestDB_Restore.ldf',
     RECOVERY;
GO

-- 查看备份文件中的逻辑文件名
RESTORE FILELISTONLY
FROM DISK = '/var/opt/mssql/backup/full/TestDB_Full_20260511.bak';
GO

6.3 差异恢复

-- 1. 先恢复完整备份（NORECOVERY）
RESTORE DATABASE TestDB
FROM DISK = '/var/opt/mssql/backup/full/TestDB_Full_20260511.bak'
WITH NORECOVERY;
GO

-- 2. 再恢复差异备份（NORECOVERY）
RESTORE DATABASE TestDB
FROM DISK = '/var/opt/mssql/backup/diff/TestDB_Diff_20260511.bak'
WITH NORECOVERY;
GO

-- 3. 最后恢复日志备份（RECOVERY）
RESTORE DATABASE TestDB
FROM DISK = '/var/opt/mssql/backup/log/TestDB_Log_20260511_093000.trn'
WITH RECOVERY;
GO

6.4 时间点恢复

-- 恢复到特定时间点
RESTORE DATABASE TestDB
FROM DISK = '/var/opt/mssql/backup/full/TestDB_Full_20260511.bak'
WITH NORECOVERY;
GO

RESTORE DATABASE TestDB
FROM DISK = '/var/opt/mssql/backup/diff/TestDB_Diff_20260511.bak'
WITH NORECOVERY;
GO

-- 恢复到2026-05-11 09:30:00
RESTORE LOG TestDB
FROM DISK = '/var/opt/mssql/backup/log/TestDB_Log_20260511_093000.trn'
WITH RECOVERY, STOPAT = '2026-05-11 09:30:00';
GO

七、自动化备份脚本

7.1 每日完整备份脚本

#!/bin/bash
# daily_full_backup.sh - 每日完整备份脚本

BACKUP_DIR="/var/opt/mssql/backup/full"
DATE=$(date +%Y%m%d)
TIME=$(date +%H%M%S)
LOG_FILE="/var/opt/mssql/backup/logs/backup_$DATE.log"
SA_PASSWORD="YourStrong@Password"

# 创建日志目录
mkdir -p /var/opt/mssql/backup/logs

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

log "开始每日完整备份"

# 备份所有用户数据库
sqlcmd -S localhost -U sa -P "$SA_PASSWORD" -C -Q "
DECLARE @dbname NVARCHAR(100);
DECLARE @backupPath NVARCHAR(500);
DECLARE @sql NVARCHAR(MAX);

SET @backupPath = '/var/opt/mssql/backup/full/';

DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb');

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'BACKUP DATABASE [' + @dbname + '] TO DISK = ''' + @backupPath + @dbname + '_Full_${DATE}_${TIME}.bak'' WITH COMPRESSION, CHECKSUM, STATS = 10';
    EXEC sp_executesql @sql;

    FETCH NEXT FROM db_cursor INTO @dbname;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;
"

log "备份完成，文件保存在：$BACKUP_DIR"

# 列出备份文件
ls -lh $BACKUP_DIR/*_${DATE}_${TIME}.bak 2>/dev/null | tee -a $LOG_FILE

7.2 定时日志备份脚本

#!/bin/bash
# log_backup.sh - 事务日志备份脚本（建议每15-30分钟执行一次）

BACKUP_DIR="/var/opt/mssql/backup/log"
DATE=$(date +%Y%m%d)
TIME=$(date +%H%M%S)
LOG_FILE="/var/opt/mssql/backup/logs/log_backup_$DATE.log"
SA_PASSWORD="YourStrong@Password"

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" >> $LOG_FILE
}

log "开始日志备份"

# 备份所有数据库的日志
sqlcmd -S localhost -U sa -P "$SA_PASSWORD" -C -Q "
DECLARE @dbname NVARCHAR(100);
DECLARE @backupPath NVARCHAR(500);
DECLARE @sql NVARCHAR(MAX);

SET @backupPath = '/var/opt/mssql/backup/log/';

DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases 
WHERE recovery_model_desc = 'FULL' 
AND name NOT IN ('master', 'model', 'msdb', 'tempdb');

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'BACKUP LOG [' + @dbname + '] TO DISK = ''' + @backupPath + @dbname + '_Log_${DATE}_${TIME}.trn'' WITH COMPRESSION';
    EXEC sp_executesql @sql;

    FETCH NEXT FROM db_cursor INTO @dbname;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;
"

log "日志备份完成"

7.3 备份清理脚本

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

BACKUP_DIR="/var/opt/mssql/backup"
LOG_FILE="/var/opt/mssql/backup/logs/cleanup_$(date +%Y%m%d).log"
FULL_KEEP_DAYS=30
DIFF_KEEP_DAYS=7
LOG_KEEP_DAYS=3

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

log "开始清理过期备份文件"

# 清理完整备份（保留30天）
find $BACKUP_DIR/full -name "*.bak" -mtime +$FULL_KEEP_DAYS -delete -exec log "删除过期完整备份: {}" \;

# 清理差异备份（保留7天）
find $BACKUP_DIR/diff -name "*.bak" -mtime +$DIFF_KEEP_DAYS -delete -exec log "删除过期差异备份: {}" \;

# 清理日志备份（保留3天）
find $BACKUP_DIR/log -name "*.trn" -mtime +$LOG_KEEP_DAYS -delete -exec log "删除过期日志备份: {}" \;

# 清理超过30天的日志文件
find $BACKUP_DIR/logs -name "*.log" -mtime +30 -delete -exec log "删除过期日志文件: {}" \;

log "清理完成"

7.4 设置定时任务

# 编辑crontab
sudo crontab -e

# 添加以下定时任务：
# 每日凌晨2点执行完整备份
0 2 * * * /var/opt/mssql/backup/scripts/daily_full_backup.sh >> /var/opt/mssql/backup/logs/cron.log 2>&1

# 每小时执行日志备份
0 * * * * /var/opt/mssql/backup/scripts/log_backup.sh >> /var/opt/mssql/backup/logs/cron.log 2>&1

# 每天凌晨3点清理过期备份
0 3 * * * /var/opt/mssql/backup/scripts/cleanup_old_backups.sh >> /var/opt/mssql/backup/logs/cron.log 2>&1

# 查看crontab
sudo crontab -l

八、备份验证

8.1 验证备份完整性

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

-- 验证多个备份文件
RESTORE VERIFYONLY
FROM DISK = '/var/opt/mssql/backup/full/TestDB_Full_20260511.bak',
     DISK = '/var/opt/mssql/backup/diff/TestDB_Diff_20260511.bak'
WITH CHECKSUM;
GO

8.2 查看备份历史

-- 查看备份历史
SELECT 
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    CASE bs.type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Log'
    END AS backup_type,
    CAST(bs.backup_size / 1024 / 1024 AS DECIMAL(10,2)) AS backup_size_MB,
    CAST(bs.compressed_backup_size / 1024 / 1024 AS DECIMAL(10,2)) AS compressed_size_MB,
    bm.physical_device_name,
    bs.is_copy_only
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bm ON bs.media_set_id = bm.media_set_id
WHERE bs.database_name = 'TestDB'
ORDER BY bs.backup_start_date DESC;

8.3 测试恢复

-- 创建测试恢复数据库
RESTORE DATABASE TestDB_TestRestore
FROM DISK = '/var/opt/mssql/backup/full/TestDB_Full_20260511.bak'
WITH MOVE 'TestDB_Data' TO '/var/opt/mssql/data/TestDB_TestRestore.mdf',
     MOVE 'TestDB_Log' TO '/var/opt/mssql/data/TestDB_TestRestore.ldf',
     RECOVERY;
GO

-- 验证数据
SELECT COUNT(*) AS row_count FROM TestDB_TestRestore.dbo.YourTable;

-- 验证完成后删除测试数据库
-- DROP DATABASE TestDB_TestRestore;

九、常见问题与解决方案

Q1：备份失败提示权限不足？

解决方案：

# 检查目录权限
ls -la /var/opt/mssql/backup

# 修改目录所有权
sudo chown mssql:mssql /var/opt/mssql/backup
sudo chmod 755 /var/opt/mssql/backup

# 如果使用子目录
sudo chown -R mssql:mssql /var/opt/mssql/backup/*

Q2：备份文件过大占用过多磁盘空间？

解决方案：
1. 启用备份压缩
2. 增加备份频率，减少单次备份量
3. 使用差异备份替代部分完整备份
4. 定期清理过期备份

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

Q3：恢复时提示”数据库正在使用”？

解决方案：

-- 强制断开所有连接
ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- 执行恢复
RESTORE DATABASE TestDB
FROM DISK = '/var/opt/mssql/backup/full/TestDB_Full_20260511.bak'
WITH REPLACE, RECOVERY;

-- 恢复为多用户模式
ALTER DATABASE TestDB SET MULTI_USER;

Q4：日志备份失败，提示”日志已满”？

解决方案：
1. 检查日志文件大小和可用空间
2. 执行日志备份或截断日志
3. 增加日志文件大小

-- 查看日志使用情况
DBCC SQLPERF(LOGSPACE);

-- 收缩日志文件
DBCC SHRINKFILE (TestDB_Log, 1000);

-- 增加日志文件大小
ALTER DATABASE TestDB MODIFY FILE (NAME = 'TestDB_Log', SIZE = 100MB);

十、备份策略最佳实践

10.1 推荐备份策略

环境 完整备份 差异备份 日志备份
生产环境 每日1次 每6-12小时1次 每15-30分钟1次
测试环境 每周1次 每日1次 每小时1次
开发环境 按需 按需 按需

10.2 3-2-1备份原则

  • 3份数据副本：原始数据 + 2份备份
  • 2种不同介质：本地磁盘 + 异地存储/云存储
  • 1份异地副本：防止本地灾难

10.3 备份监控

-- 创建备份监控视图
CREATE VIEW vw_BackupStatus AS
SELECT 
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.backup_size,
    CASE bs.type
        WHEN 'D' THEN '完整备份'
        WHEN 'I' THEN '差异备份'
        WHEN 'L' THEN '日志备份'
    END AS backup_type,
    DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date) AS backup_duration_minutes
FROM msdb.dbo.backupset bs
WHERE bs.backup_start_date > DATEADD(DAY, -7, GETDATE());

10.4 灾难恢复计划

  1. 定期测试恢复：每月至少测试一次完整恢复流程
  2. 文档化恢复步骤：记录每种故障的恢复流程
  3. 准备恢复环境：有独立的测试/恢复服务器
  4. 定期演练：模拟各种灾难场景进行恢复演练

总结

CentOS上SQL Server数据备份与恢复的关键要点：

  • 备份类型：完整备份、差异备份、日志备份
  • 备份操作：T-SQL命令、sqlcmd命令行、自动化脚本
  • 恢复策略：完整恢复、差异恢复、时间点恢复
  • 自动化：定时任务、备份脚本、清理脚本
  • 验证：备份完整性检查、测试恢复
  • 最佳实践：3-2-1原则、定期测试、灾难恢复计划

掌握这些备份恢复技巧，可以有效保护数据库数据安全，确保业务连续性。

本文基于SQL Server 2022和CentOS Stream 9编写，适用于CentOS 7+/RHEL 7+/CentOS Stream环境。

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注