在Ubuntu上配置和测试SQL Server的备份恢复涉及多个步骤。以下是一个详细的指南,帮助你完成这一过程。
wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc
sudo add-apt-repository "deb [arch=amd64] https://packages.microsoft.com/ubuntu/20.04/mssql-server-2022 main"
sudo apt-get update
sudo apt-get install -y mssql-server
mssql-conf setup
按照提示选择数据库版本及设置SA密码。sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
sudo systemctl restart mssql-server
sudo mkdir /home/d/mssql/data
sudo chown mssql:mssql /home/d/mssql/data
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"
sqlcmd
进行恢复: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 DATABASE [XXDatabase] FROM DISK N'/var/opt/mssql/data/backup/GDQXCENT_diff.bak' WITH NORECOVERY"
crontab
自动备份:sudo crontab -e
# 添加以下行以每天凌晨1点自动创建完全备份
0 1 * * * 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'"
通过以上步骤,你可以在Ubuntu上成功配置SQL Server,并完成数据库的备份和恢复测试。请根据你的实际需求调整备份和恢复策略,并定期检查备份文件的完整性。