ubuntu

SQL Server在Ubuntu上的备份恢复测试

小樊
46
2025-03-24 06:35:58
栏目: 云计算

在Ubuntu上配置和测试SQL Server的备份恢复涉及多个步骤。以下是一个详细的指南,帮助你完成这一过程。

安装和配置SQL Server

  1. 安装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
  1. 配置SQL Server
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

备份SQL Server数据库

  1. 使用 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"

恢复SQL Server数据库

  1. 使用 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"
  1. 自动备份和定期执行
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,并完成数据库的备份和恢复测试。请根据你的实际需求调整备份和恢复策略,并定期检查备份文件的完整性。

0
看了该问题的人还看了