SQL Server在Ubuntu上的备份恢复

在Ubuntu上备份和恢复SQL Server数据库可以通过多种方法实现，以下是详细的步骤和示例。

安装和配置SQL Server

1. 安装SQL Server：

   首先，需要在Ubuntu上安装SQL Server。可以参考Microsoft官方文档。

   sudo apt-get update
   sudo apt-get install -y mssql-server
   

2. 配置SQL Server：

   安装完成后，配置SQL Server实例和设置SA密码：

   sudo /opt/mssql/bin/mssql-conf setup
   

备份SQL Server数据库

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

2. 自动备份脚本：

   可以编写脚本并使用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
   

恢复SQL Server数据库

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