在Ubuntu上备份和恢复SQL Server数据库可以通过多种方法实现,以下是详细的步骤和示例。
安装SQL Server:
首先,需要在Ubuntu上安装SQL Server。可以参考Microsoft官方文档。
sudo apt-get update
sudo apt-get install -y mssql-server
配置SQL Server:
安装完成后,配置SQL Server实例和设置SA密码:
sudo /opt/mssql/bin/mssql-conf setup
使用sqlcmd
进行备份:
使用SQL命令行工具sqlcmd
进行数据库备份。以下是一些常用的备份命令示例:
完全备份:
sqlcmd -S localhost -U SA -P 'YourPassword' -Q "BACKUP DATABASE [YourDatabaseName] TO DISK='/var/opt/mssql/data/[YourDatabaseName]_full.bak' WITH INIT, SKIP, NAME='Full Backup of [YourDatabaseName]' "
差异备份:
sqlcmd -S localhost -U SA -P 'YourPassword' -Q "BACKUP DATABASE [YourDatabaseName] TO DISK='/var/opt/mssql/data/[YourDatabaseName]_diff.bak' WITH DIFFERENTIAL, INIT, SKIP, NAME='Differential Backup of [YourDatabaseName]' "
日志备份:
sqlcmd -S localhost -U SA -P 'YourPassword' -Q "BACKUP LOG [YourDatabaseName] TO DISK='/var/opt/mssql/data/[YourDatabaseName]_log.bak' WITH INIT, SKIP "
自动备份脚本:
可以编写脚本并使用crontab
定时执行备份任务。以下是一个简单的备份脚本示例:
#!/bin/bash
# 备份数据库
sqlcmd -S localhost -U SA -P 'YourPassword' -Q "BACKUP DATABASE [YourDatabaseName] TO DISK='/var/opt/mssql/data/[YourDatabaseName]_full.bak' WITH INIT, SKIP, NAME='Full Backup of [YourDatabaseName]' "
# 差异备份
sqlcmd -S localhost -U SA -P 'YourPassword' -Q "BACKUP DATABASE [YourDatabaseName] TO DISK='/var/opt/mssql/data/[YourDatabaseName]_diff.bak' WITH DIFFERENTIAL, INIT, SKIP, NAME='Differential Backup of [YourDatabaseName]' "
# 日志备份
sqlcmd -S localhost -U SA -P 'YourPassword' -Q "BACKUP LOG [YourDatabaseName] TO DISK='/var/opt/mssql/data/[YourDatabaseName]_log.bak' WITH INIT, SKIP "
将脚本保存为mssql_backup.sh
,然后使用crontab
定时执行:
sudo crontab -e
添加以下行以每天凌晨执行备份:
0 3 * * * /path/to/mssql_backup.sh
使用sqlcmd
进行恢复:
使用以下命令进行数据库恢复:
完全恢复:
sqlcmd -S localhost -U SA -P 'YourPassword' -Q "RESTORE DATABASE [YourDatabaseName] FROM DISK='/var/opt/mssql/data/[YourDatabaseName]_full.bak' WITH NORECOVERY"
从差异备份恢复:
sqlcmd -S localhost -U SA -P 'YourPassword' -Q "RESTORE DATABASE [YourDatabaseName] FROM DISK='/var/opt/mssql/data/[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'"
从日志备份恢复:
sqlcmd -S localhost -U SA -P 'YourPassword' -Q "RESTORE LOG [YourDatabaseName] FROM DISK='/var/opt/mssql/data/[YourDatabaseName]_log.bak' WITH NORECOVERY"
恢复完成后,使用以下命令使数据库可用:
sqlcmd -S localhost -U SA -P 'YourPassword' -Q "RESTORE DATABASE [YourDatabaseName] WITH RECOVERY"
可以使用工具如rclone
将备份数据同步到远程服务器或数据中心:
sudo apt-get install rclone
配置rclone
并同步备份文件:
rclone sync /var/opt/mssql/data/backup/[YourDatabaseName]_diff.bak remote:backup
rclone sync /var/opt/mssql/data/backup/[YourDatabaseName]_log.bak remote:backup
辰迅云「云服务器」,即开即用、新一代英特尔至强铂金CPU、三副本存储NVMe SSD云盘,价格低至29元/月。点击查看>>
推荐阅读: ubuntu查看端口被哪个进程占用