SQL Server在Ubuntu上的備份與恢復技巧

SQL Server在Ubuntu上的備份與恢復技巧

一、準備工作

在Ubuntu上使用SQL Server進行備份恢復前，需完成基礎環境配置：

1. 安裝SQL Server：通過Microsoft官方文檔指導安裝SQL Server for Linux（sudo apt-get install -y mssql-server），并通過sudo /opt/mssql/bin/mssql-conf setup配置SA密碼及實例設置。
2. 安裝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選項，但會丟失部分數據）。

四、注意事項

1. 備份文件安全：定期將備份文件同步到異地（如使用rclone工具），防止本地磁盤故障導致數據丟失。示例同步命令：rclone sync /var/opt/mssql/backup remote:sql_backup。
2. 測試恢復流程：定期在測試環境中執行備份恢復操作，驗證備份文件的可用性和恢復步驟的正確性，避免生產環境出現問題。
3. 監控備份狀態：通過STATS選項或日志監控備份進度，確保備份成功完成。若備份失敗，及時排查原因（如磁盤空間不足、權限問題）。
4. 權限管理：確保執行備份恢復的用戶（如SA）具有足夠權限（sysadmin角色），避免因權限不足導致操作失敗。