在Ubuntu上使用SQL Server进行备份与恢复是一个重要的任务,以确保数据的安全性和完整性。以下是一些基本的实践和步骤:
首先,确保在Ubuntu上正确安装和配置了SQL Server。可以参考Microsoft的官方文档来安装SQL Server和必要的工具。
sqlcmd
工具进行完全备份。例如:sqlcmd -S localhost -U SA -P 'XXXX' -Q "BACKUP DATABASE [XXDatabase] TO DISK N'/var/opt/mssql/data/backup/XXDatabase_full.bak' WITH INIT, SKIP, NAME 'XXDatabase-full'"
sqlcmd -S localhost -U SA -P 'XXXX' -Q "BACKUP DATABASE [XXDatabase] TO DISK N'/var/opt/mssql/data/backup/GDQXCENT_diff.bak' WITH DIFFERENTIAL,INIT,SKIP, NAME 'XXDatabase-diff'"
sqlcmd -S localhost -U SA -P 'XXXX' -Q "BACKUP LOG [XXDatabase] TO DISK N'/var/opt/mssql/data/backup/GDQXCENT_log.bak' WITH INIT, SKIP"
crontab
设置定时任务自动执行备份脚本。sudo crontab -e
# 添加备份任务
30 3 * * * sh /home/userx/scripts/mssql_backup_diff.sh
40 3 * * * sh /home/userx/scripts/mssql_backup_log.sh
0 3 * * 1,3,6 sh /home/userx/scripts/mssql_backup_full.sh
rclone
等工具将备份数据同步到远程服务器。sqlcmd -S localhost -U SA -P 'XXXX' -Q "RESTORE DATABASE [XXDatabase] FROM DISK N'/var/opt/mssql/data/backup/XXDatabase_full.bak' WITH NORECOVERY"
sqlcmd -S localhost -U SA -P 'XXXX' -Q "RESTORE LOG [XXDatabase] FROM DISK N'/var/opt/mssql/data/backup/XXDatabase_TransactionLogBackup1.trn'"
通过上述步骤和注意事项,可以在Ubuntu上有效地进行SQL Server数据库的备份与恢复,确保数据的安全性和业务的连续性。