SQL Server在CentOS上的备份与还原技巧

一、备份技巧

1. 准备工作：安装必要工具与配置权限

在备份前，需确保系统已安装mssql-tools（包含sqlcmd命令行工具）和unixODBC-devel，用于执行T-SQL命令和数据库连接。同时，创建专用备份目录并设置正确权限，避免权限问题导致备份失败：

sudo yum install -y mssql-tools unixODBC-devel  # 安装工具包
sudo mkdir -p /var/opt/mssql/backups            # 创建备份目录
sudo chown mssql:mssql /var/opt/mssql/backups   # 设置目录所有者为mssql用户
sudo chmod 700 /var/opt/mssql/backups           # 限制目录访问权限

2. 手动备份：T-SQL与SSMS两种方式

（1）使用T-SQL命令备份

通过sqlcmd工具登录SQL Server，执行BACKUP DATABASE命令实现完整备份（推荐格式化备份文件并添加描述）：

BACKUP DATABASE [YourDatabaseName] 
TO DISK = '/var/opt/mssql/backups/YourDatabaseName_Full_$(date +\%F).bak' 
WITH FORMAT, MEDIANAME = 'YourDatabaseNameBackup', NAME = 'Full Backup of YourDatabaseName', STATS = 10;
    

• FORMAT：格式化备份设备，覆盖旧备份（首次备份需使用）；
• MEDIANAME：备份介质名称，便于识别；
• STATS = 10：每完成10%显示进度；
• $(date +\%F)：生成当前日期（如2025-09-24），实现每日备份文件自动命名。

（2）使用SSMS图形界面备份

若习惯图形操作，可通过SSMS完成：

• 连接SQL Server实例，展开“数据库”节点；
• 右键目标数据库→“任务”→“备份”；
• 在“备份数据库”窗口中，选择“备份类型”（完整/差异/事务日志），指定“目标”（添加备份文件路径，如/var/opt/mssql/backups/...）；
• 点击“确定”启动备份，可在“进度”选项卡查看实时状态。

3. 自动化备份：Crontab定时任务

通过crontab设置定时任务，实现每日自动备份（如凌晨2点执行）：

sudo crontab -e

添加以下内容（替换YourDatabaseName、密码和路径）：

0 2 * * * /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'your_password' -Q "BACKUP DATABASE [YourDatabaseName] TO DISK = '/var/opt/mssql/backups/YourDatabaseName_Full_$(date +\%F).bak' WITH FORMAT, STATS = 10;
    "

• 0 2 * * *：表示每日凌晨2点执行；
• $(date +\%F)：需转义%符号（\%F），生成日期格式文件名。

4. 备份策略建议

• 完整备份：每周1次（如周一），备份整个数据库，适合长期归档；
• 差异备份：每日1次（如周二至周日），仅备份自上次完整备份以来的更改，减少备份时间和存储占用；
• 事务日志备份：每15-30分钟1次（针对高并发业务），记录所有事务操作，支持恢复到特定时间点（需数据库恢复模式设置为“完整”）。

5. 备份验证与清理

• 验证备份完整性：使用RESTORE HEADERONLY命令检查备份文件是否有效：

  RESTORE HEADERONLY FROM DISK = '/var/opt/mssql/backups/YourDatabaseName_Full_2025-09-24.bak';
  
  

  确认“BackupType”（1=完整备份）、“Status”（1=有效）等信息。
• 清理旧备份：通过crontab定期删除超过30天的备份文件，避免磁盘空间耗尽：

  0 3 * * * find /var/opt/mssql/backups/ -name "*.bak" -mtime +30 -exec rm {
  }
       \;
      
  

二、还原技巧

1. 准备工作：停止服务与复制备份文件

还原前需停止SQL Server服务（避免文件占用），并将备份文件复制到SQL Server数据目录（如/var/opt/mssql/data/）：

sudo systemctl stop mssql-server      # 停止服务
sudo cp /var/opt/mssql/backups/YourDatabaseName_Full.bak /var/opt/mssql/data/  # 复制备份文件
sudo systemctl start mssql-server     # 启动服务（可选，若需立即还原可保持停止）

2. 手动还原：T-SQL与SSMS两种方式

（1）使用T-SQL命令还原

通过sqlcmd工具执行RESTORE DATABASE命令，根据备份类型选择参数：

• 完整备份还原（覆盖现有数据库）：

  RESTORE DATABASE [YourDatabaseName] 
  FROM DISK = '/var/opt/mssql/data/YourDatabaseName_Full.bak' 
  WITH REPLACE, RECOVERY, STATS = 10;
      
  

  • REPLACE：覆盖现有数据库（若数据库已存在）；
  • RECOVERY：将数据库恢复到可用状态（适用于完整备份或最后一步还原）。

• 差异备份还原（需先还原完整备份，再还原差异备份）：

  -- 还原完整备份（NO_RECOVERY：不恢复数据库，等待差异备份）
  RESTORE DATABASE [YourDatabaseName] 
  FROM DISK = '/var/opt/mssql/data/YourDatabaseName_Full.bak' 
  WITH NORECOVERY, STATS = 10;
      
  
  -- 还原差异备份（RECOVERY：恢复数据库）
  RESTORE DATABASE [YourDatabaseName] 
  FROM DISK = '/var/opt/mssql/backups/YourDatabaseName_Diff.bak' 
  WITH RECOVERY, STATS = 10;
      
  

• 事务日志还原（需依次还原完整备份、差异备份，最后还原事务日志）：

  -- 还原完整备份（NO_RECOVERY）
  RESTORE DATABASE [YourDatabaseName] 
  FROM DISK = '/var/opt/mssql/data/YourDatabaseName_Full.bak' 
  WITH NORECOVERY, STATS = 10;
      
  
  -- 还原差异备份（NO_RECOVERY）
  RESTORE DATABASE [YourDatabaseName] 
  FROM DISK = '/var/opt/mssql/backups/YourDatabaseName_Diff.bak' 
  WITH NORECOVERY, STATS = 10;
      
  
  -- 还原事务日志（最后一次还原用RECOVERY）
  RESTORE LOG [YourDatabaseName] 
  FROM DISK = '/var/opt/mssql/backups/YourDatabaseName_Log.trn' 
  WITH RECOVERY, STATS = 10;
      
  

（2）使用SSMS图形界面还原

• 连接SQL Server实例，右键“数据库”→“任务”→“还原”→“数据库”；
• 在“还原数据库”窗口中，选择“设备”→“浏览”，添加备份文件；
• 切换至“选项”页签，勾选“覆盖现有数据库”（对应REPLACE参数），根据备份类型选择“恢复状态”（如完整备份选“恢复”，差异/日志备份选“不恢复”）；
• 点击“确定”启动还原，可在“进度”选项卡查看状态。

3. 还原后检查

还原完成后，需验证数据库状态和数据完整性：

• 检查数据库状态：通过SSMS查看数据库是否为“在线”状态，或执行以下T-SQL命令：

  SELECT name, state_desc FROM sys.databases WHERE name = 'YourDatabaseName';
      
  

  （state_desc应为ONLINE，表示数据库可用）。
• 验证数据完整性：查询关键表数据，确认备份数据与源数据一致；或使用DBCC CHECKDB命令检查数据库一致性：

  DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
      
  

  若无错误输出，说明数据库结构完整。

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！