SQL Server在Ubuntu上的备份与恢复技巧

SQL Server在Ubuntu上的备份与恢复技巧

一、准备工作

在Ubuntu上使用SQL Server进行备份恢复前，需完成基础环境配置：

安装SQL Server：通过Microsoft官方文档指导安装SQL Server for Linux（sudo apt-get install -y mssql-server），并通过sudo /opt/mssql/bin/mssql-conf setup配置SA密码及实例设置。
安装sqlcmd工具：sqlcmd是SQL Server的命令行客户端，用于执行备份恢复命令。安装命令：sudo apt-get install -y mssql-tools unixodbc-dev，安装后需将工具路径加入环境变量（echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc并执行source ~/.bashrc）。

二、备份技巧

1. 完整备份（Full Backup）

完整备份是基础，包含数据库所有数据和对象。推荐使用WITH FORMAT选项覆盖旧备份，WITH COMPRESSION减少文件大小，WITH STATS显示进度。
示例命令：

sqlcmd -S localhost -U SA -P 'YourPassword' -Q "BACKUP DATABASE [YourDatabaseName] TO DISK='/var/opt/mssql/backup/YourDatabaseName_full.bak' WITH FORMAT, COMPRESSION, STATS 10"

注意：备份目录需提前创建并授权SQL Server用户（sudo mkdir -p /var/opt/mssql/backup && sudo chown mssql:mssql /var/opt/mssql/backup）。

2. 差异备份（Differential Backup）

差异备份仅备份自上次完整备份以来修改的数据，节省时间和存储空间。需在完整备份后执行，使用WITH DIFFERENTIAL选项。
示例命令：

sqlcmd -S localhost -U SA -P 'YourPassword' -Q "BACKUP DATABASE [YourDatabaseName] TO DISK='/var/opt/mssql/backup/YourDatabaseName_diff.bak' WITH DIFFERENTIAL, INIT, SKIP"

提示：差异备份需与完整备份配合使用，恢复时需先恢复完整备份，再恢复最新的差异备份。

3. 事务日志备份（Transaction Log Backup）

事务日志备份记录自上次日志备份以来的所有事务，支持时间点恢复（Point-in-Time Recovery）。需在完整备份后执行，使用WITH INIT选项覆盖旧日志。
示例命令：

sqlcmd -S localhost -U SA -P 'YourPassword' -Q "BACKUP LOG [YourDatabaseName] TO DISK='/var/opt/mssql/backup/YourDatabaseName_log.bak' WITH INIT, SKIP"

适用场景：对数据一致性要求高的业务（如金融系统），需定期执行日志备份。

4. 自动化备份（Cron Job）

通过Linux的crontab设置定时任务，实现无人值守备份。编辑当前用户的crontab（crontab -e），添加如下内容（每天凌晨2点执行完整备份）：

0 2 * * * /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'YourPassword' -Q "BACKUP DATABASE [YourDatabaseName] TO DISK='/var/opt/mssql/backup/YourDatabaseName_full_$(date +\%Y\%m\%d).bak' WITH FORMAT, COMPRESSION, STATS 10"

注意：日期格式需转义（\%Y\%m\%d），避免cron解析错误。

三、恢复技巧

1. 完整恢复（Full Recovery）

恢复完整备份时，使用WITH NORECOVERY选项（若需后续恢复差异或日志备份），或WITH RECOVERY选项（直接使数据库可用）。
示例命令：

sqlcmd -S localhost -U SA -P 'YourPassword' -Q "RESTORE DATABASE [YourDatabaseName] FROM DISK='/var/opt/mssql/backup/YourDatabaseName_full.bak' WITH NORECOVERY"

说明：若无需后续恢复，直接使用WITH RECOVERY即可。

2. 差异恢复（Differential Recovery）

恢复差异备份时，需指定差异备份文件，并使用WITH NORECOVERY（若有后续日志备份）或WITH RECOVERY（直接可用）。
示例命令：

sqlcmd -S localhost -U SA -P 'YourPassword' -Q "RESTORE DATABASE [YourDatabaseName] FROM DISK='/var/opt/mssql/backup/YourDatabaseName_diff.bak' WITH NORECOVERY, MOVE 'YourDatabaseName_Data' TO '/var/opt/mssql/data/YourDatabaseName.mdf', MOVE 'YourDatabaseName_Log' TO '/var/opt/mssql/data/YourDatabaseName_log.ldf'"

提示：MOVE选项用于指定数据文件和日志文件的新路径（可选，若路径未改变可省略）。

3. 日志恢复（Log Recovery）

恢复事务日志备份时，需依次恢复所有日志文件，最后使用WITH RECOVERY使数据库可用。
示例命令：

sqlcmd -S localhost -U SA -P 'YourPassword' -Q "RESTORE LOG [YourDatabaseName] FROM DISK='/var/opt/mssql/backup/YourDatabaseName_log1.bak' WITH NORECOVERY"
sqlcmd -S localhost -U SA -P 'YourPassword' -Q "RESTORE LOG [YourDatabaseName] FROM DISK='/var/opt/mssql/backup/YourDatabaseName_log2.bak' WITH RECOVERY"

注意：日志恢复需按顺序执行，遗漏或顺序错误会导致恢复失败。

4. 恢复验证（DBCC CHECKDB）

恢复完成后，使用DBCC CHECKDB命令检查数据库完整性，确保存储结构无损坏。
示例命令：

sqlcmd -S localhost -U SA -P 'YourPassword' -Q "DBCC CHECKDB([YourDatabaseName]) WITH NO_INFOMSGS, ALL_ERRORMSGS"

说明：若输出无错误，则数据库完整性正常；若有错误，需进一步修复（如使用REPAIR_ALLOW_DATA_LOSS选项，但会丢失部分数据）。

四、注意事项

备份文件安全：定期将备份文件同步到异地（如使用rclone工具），防止本地磁盘故障导致数据丢失。示例同步命令：rclone sync /var/opt/mssql/backup remote:sql_backup。
测试恢复流程：定期在测试环境中执行备份恢复操作，验证备份文件的可用性和恢复步骤的正确性，避免生产环境出现问题。
监控备份状态：通过STATS选项或日志监控备份进度，确保备份成功完成。若备份失败，及时排查原因（如磁盘空间不足、权限问题）。
权限管理：确保执行备份恢复的用户（如SA）具有足够权限（sysadmin角色），避免因权限不足导致操作失败。