备份是保护数据的核心环节,Debian下主要通过命令行工具(sqlcmd
)或自动化脚本实现,支持完整备份(Full Backup)、差异备份(Differential Backup)、事务日志备份(Transaction Log Backup)三种类型。
mssql-tools
(包含sqlcmd
):sudo apt update && sudo apt install -y mssql-tools unixodbc-dev
/var/opt/mssql/backup
,需确保SQL Server服务对该目录有写入权限(通常默认可写)。sqlcmd
执行备份通过命令行工具sqlcmd
连接SQL Server实例,执行BACKUP DATABASE
命令:
sqlcmd -S localhost -U sa -P 'YourStrongPassword' -Q "BACKUP DATABASE [YourDatabaseName] TO DISK = '/var/opt/mssql/backup/YourDatabaseName_Full_$(date +%Y%m%d).bak' WITH FORMAT, INIT, STATS = 10"
-S
:SQL Server实例地址(本地为localhost
);-U
/-P
:登录凭据(sa
为用户,YourStrongPassword
为密码);FORMAT
:覆盖现有备份文件;INIT
:初始化备份设备;STATS = 10
:每完成10%显示进度。通过cron
定时任务实现定期备份,避免手动操作:
/usr/local/bin/backup_sql.sh
:#!/bin/bash BACKUP_DIR="/var/opt/mssql/backup" DB_NAME="YourDatabaseName" DATE=$(date +%Y%m%d) BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_Full_$DATE.bak" sqlcmd -S localhost -U sa -P 'YourStrongPassword' -Q "BACKUP DATABASE [$DB_NAME] TO DISK = '$BACKUP_FILE' WITH FORMAT, INIT, STATS = 10" # 检查备份是否成功 if [ $? -eq 0 ]; then echo "$(date): Backup of $DB_NAME succeeded." >> /var/log/sql_backup.log else echo "$(date): Backup of $DB_NAME failed!" >> /var/log/sql_backup.log fi
cron
任务:chmod +x /usr/local/bin/backup_sql.sh echo "0 2 * * * /usr/local/bin/backup_sql.sh" | sudo tee -a /etc/crontab
上述配置表示每天凌晨2点执行备份。备份完成后,检查备份文件是否存在及大小是否符合预期:
ls -lh /var/opt/mssql/backup/
恢复是将备份文件还原到SQL Server实例的过程,需根据备份类型(完整/差异/日志)选择对应命令。
WITH REPLACE
覆盖)。sqlcmd
执行恢复恢复完整备份(首次恢复需使用完整备份):
sqlcmd -S localhost -U sa -P 'YourStrongPassword' -Q "RESTORE DATABASE [YourDatabaseName] FROM DISK = '/var/opt/mssql/backup/YourDatabaseName_Full_20250929.bak' WITH REPLACE, RECOVERY, STATS = 10"
REPLACE
:覆盖现有数据库;RECOVERY
:将数据库恢复至可用状态(若需应用事务日志,改为NORECOVERY
)。恢复差异备份(需在完整备份后执行):
sqlcmd -S localhost -U sa -P 'YourStrongPassword' -Q "RESTORE DATABASE [YourDatabaseName] FROM DISK = '/var/opt/mssql/backup/YourDatabaseName_Diff_20250929.bak' WITH RECOVERY, STATS = 10"
恢复事务日志备份(需在完整备份+差异备份后执行):
# 先恢复完整备份(NORECOVERY) sqlcmd -S localhost -U sa -P 'YourStrongPassword' -Q "RESTORE DATABASE [YourDatabaseName] FROM DISK = '/var/opt/mssql/backup/YourDatabaseName_Full_20250929.bak' WITH NORECOVERY, STATS = 10" # 再恢复事务日志(NORECOVERY) sqlcmd -S localhost -U sa -P 'YourStrongPassword' -Q "RESTORE LOG [YourDatabaseName] FROM DISK = '/var/opt/mssql/backup/YourDatabaseName_Log_20250929.trn' WITH NORECOVERY, STATS = 10" # 最后恢复最新事务日志(RECOVERY) sqlcmd -S localhost -U sa -P 'YourStrongPassword' -Q "RESTORE LOG [YourDatabaseName] FROM DISK = '/var/opt/mssql/backup/YourDatabaseName_Log_20250930.trn' WITH RECOVERY, STATS = 10"
若习惯图形操作,可通过SQL Server Management Studio(SSMS)恢复:
若数据库处于“恢复挂起”状态(如恢复失败),可通过以下命令修复:
-- 设置为紧急模式(允许访问) ALTER DATABASE [YourDatabaseName] SET EMERGENCY; -- 检查数据库一致性(修复错误) DBCC CHECKDB([YourDatabaseName], REPAIR_ALLOW_DATA_LOSS); -- 设置为多用户模式(恢复正常) ALTER DATABASE [YourDatabaseName] SET MULTI_USER;
/var/opt/mssql/backup
目录对mssql
用户可写;