SQL Server在Ubuntu上的备份恢复

827
2025/3/10 6:32:02
栏目: 云计算
开发者测试专用服务器限时活动,0元免费领,库存有限,领完即止! 点击查看>>

在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

辰迅云「云服务器」,即开即用、新一代英特尔至强铂金CPU、三副本存储NVMe SSD云盘,价格低至29元/月。点击查看>>

推荐阅读: ubuntu查看端口被哪个进程占用