SQLServer数据库迁移到Linux(备份和还原)

SQLServer数据库从Windows迁移到Linux

官方文档：https://learn.microsoft.com/zh-cn/SQL/linux/sql-server-linux-migrate-restore-database?view=sql-server-linux-ver15

1、先决条件

将SQL server数据库从Windows迁移到Linux，需要满足下面的条件：

• Windows安装了下面内容：

  • SQL Server。
  • SQL Server Management Studio。
  • 要迁移的目标数据库。

• Linux中安装了以下内容：

  • 带有命令行工具的SQL Server（RHEL、SLES 或 Ubuntu）。

2、在Windows上进行备份

我们可以通过如下两种方式来对数据库进行备份：

1. SSMS备份。
2. T-SQL(Transact-SQL)进行备份。

2.1 SSMS进行备份

第一步，打开SSMS，并建立连接。

第二步，在“对象资源管理器”中展开“数据库”，找到对应需要备份的数据库。

第三步，右键单击目标数据库，选择“任务”，再单击“备份…”。

第四步，在“备份数据库”对话框中，验证“备份类型”是否为“完整”，以及“备份到”是否为“磁盘”。 注意文件的名称和位置。

上面两张图中，数据库的名称是不一样，这里只是打个样，大概知道怎么做就行了。

这里我使用的SSMS2022版，数据库备份路径默认为C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\。

第五步，单击“确认”，即可完成备份操作。

2.2 Transact-SQL进行备份

创建备份文件的另一种方法是运行 Transact-SQL 查询。 以下 Transact-SQL 命令对名为“YourDB”的数据库执行与前面步骤相同的操作：

BACKUP DATABASE [YourDB] TO  DISK =
N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\YourDB.bak'
WITH NOFORMAT, NOINIT, NAME = N'YourDB-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

解释：

• BACKUP DATABASE: 这是SQL Server中用于备份数据库的命令。
• [YourDB]: 这是要备份的数据库名称。在实际使用中，你需要将YourDB替换为你要备份的数据库名。

• TO DISK = N'C:\Program Files\Microsoft SQL Server...': 表示备份的目的地，即磁盘上的文件路径。在实际使用中，你需要将这个路径替换为你希望存储备份文件的实际路径。
• WITH 子句用于指定备份操作的多个选项。
• NOFORMAT
  • 在早期版本的 SQL Server 中，NOFORMAT 用于指定备份时不应重新格式化媒体头。但在 SQL Server 2005 及更高版本中，由于不再支持磁带备份，这个选项主要用于向后兼容。在磁盘备份中，这个选项没有效果。
• NOINIT
  • NOINIT 指定备份应追加到指定的备份设备上的现有备份集，而不是覆盖它。如果备份媒体上已存在备份集，并且您希望将新的备份追加到该备份集，则可以使用此选项。但在 SQL Server 2014 及更高版本中，建议使用 APPEND 选项。
• NAME = N’YourDB-Full Database Backup’
  • 这个选项允许您为备份集指定一个描述性的名称。这有助于在备份媒体上识别和管理多个备份集。
• SKIP
  • 如果指定了多个备份设备，并且某个设备发生错误，SKIP 选项将允许备份操作跳过该设备并继续到下一个设备。这有助于确保即使某些设备失败，备份也能继续。
• NOREWIND
  • 在备份操作完成后，NOREWIND 选项将使磁带机（如果使用磁带备份）保持打开状态，而不是倒回到开始位置。这允许立即进行另一个备份或还原操作，而无需手动干预。对于磁盘备份，此选项确保备份文件在备份操作后仍然可用，而不必先卸载媒体。
• NOUNLOAD
  • 对于磁带设备，NOUNLOAD 指定在备份操作完成后，磁带应保持加载在磁带机中。对于磁盘备份，此选项没有效果，因为磁盘介质在备份后总是保持“加载”状态。
• STATS = 10
  • STATS 选项用于在备份过程中显示进度信息。数字（在此例中为10）指定在每次完成指定数量的数据库页时报告一次进度。例如，STATS = 10 意味着每完成10个数据库页时，SQL Server 将在消息窗口中报告进度。

3、将备份文件转移到Linux

3.1 使用bash shell传输(SCP)

在 Bash 会话中，导航到包含备份文件的目录。

cd 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\'

使用 scp 命令将文件传输到目标 Linux 计算机。 下面的示例将 YourDB.bak 传输到 Linux 服务器上的 user1 的主目录，IP 地址为 192.0.2.9：

scp YourDB.bak user1@192.0.2.9:./
# 上面的命令包含了两部分的步骤
# 1. 和ip地址为192.0.2.9的目标建立ssh连接
# 2. 将YourDB.bak备份文件传输到用户文件目录下(在linux中一般都是 home/用户文件 )

上面只是一个实现的步骤，一个demo，具体你的备份文件放哪里呀，目标主机的ip都是根据自己实际情况来定的。

3.2 bash shell传输中踩坑

ssh：Connection timed out

注意，在建立ssh连接时可能会出现timed out这个问题，很大概率是下面所述问题，可以一一进行排除：

• 确认目标主机的IP地址和端口号是否正确。

• 确认目标主机上SSH服务是否正在运行。可以使用ps aux | grep sshd命令检查。

• 确认网络连接没有问题，可以使用ping命令检查网络连通性。

• 如果是在防火墙后面，确保防火墙规则允许从你的机器到目标主机的22端口的连接，可以使用sudo ufw allow ssh命令允许ssh连接。

• 如果SSH服务正在运行且网络无问题，检查SSH服务的日志文件，查找可能的错误信息。

• 如果是在虚拟机或者远程服务器上，请确保你没有超出了分配给虚拟机或服务器的网络带宽。

ssh：Connection refused

排查了一下，发现Linux中ssh并没有安装，所以导致了连接被拒接。当然，也有可能是以下原因：

• ssh服务未开启。

下面装一下ssh服务：

sudo apt update
sudo apt install openssh-server

查看一下ssh服务状态：

sudo systemctl status ssh sshd

解决了这个问题之后，也是可以正常连接传输文件了。

3.3 文件的传输方式

• SCP (Secure Copy Protocol):
  • SCP 是一个基于 SSH 协议的文件传输程序，它允许你安全地在本地主机和远程主机之间复制文件。
  • 使用方法：scp [选项] [[用户@]来源主机:]文件路径 [[用户@]目标主机:]文件路径
• SFTP (SSH File Transfer Protocol):
  • SFTP 也是一个基于 SSH 的文件传输协议，提供了安全的文件访问、文件传输和文件管理的功能。
  • 你可以使用 SFTP 客户端（如 sftp 命令行工具、WinSCP、FileZilla 等）来连接和操作远程 Linux 服务器上的文件。
• Rsync:
  • Rsync 是一个快速且通用的命令行实用程序，用于在本地和远程系统之间同步文件和目录。
  • 它只传输源和目标之间的差异，从而提供快速增量文件传输1。
• FTP (File Transfer Protocol):
  • FTP 是一个标准的网络协议，用于文件传输。
  • 你可以使用 FTP 客户端（如 FileZilla、lftp 等）连接到支持 FTP 的 Linux 服务器，并进行文件传输2。
• NFS (Network File System):
  • NFS 允许一个系统在网络上共享其目录和文件，使得其他计算机可以像访问本地文件一样访问这些文件。
  • 在 Linux 中，你可以配置 NFS 服务器和客户端来实现文件共享和传输。
• SMB/CIFS (Server Message Block/Common Internet File System):
  • SMB/CIFS 是 Microsoft 开发的网络文件共享协议，但也被许多 Linux 发行版所支持。
  • 你可以使用 Samba 软件包在 Linux 上设置 SMB/CIFS 服务器，并使用 SMB 客户端（如 smbclient、gvfs-smb 等）来访问和传输文件。
• 图形界面工具:
  • 对于不熟悉命令行的用户，可以使用图形界面工具（如 Nautilus、Dolphin、Thunar 等）来在本地和远程 Linux 系统之间传输文件。这些工具通常提供了拖放式的文件传输界面。
• WebDAV (Web-based Distributed Authoring and Versioning):
  • WebDAV 是一种基于 HTTP 的协议，用于在远程服务器上编辑和管理文件。
  • 通过支持 WebDAV 的客户端（如 Nautilus、Dolphin、Cyberduck 等），你可以将远程 Linux 服务器上的文件作为本地文件系统进行访问和传输。

4、在还原前移动备份文件

经过上面的操作，备份文件已经放到了你用户的主目录中的Linux服务器上。在将数据库还原到SQLServer之前，我们需要将备份文件移动到/var/opt/mssql的子目录backup中，因为这属于用户mssql和mssql，简单来说这是默认配置(约定)。 如果想要更改默认备份位置，请查看使用 mssql-conf 进行配置一文。

1. 在Windows Bash会话中，通过ssh远程连接到目标Linux计算机。下面示例以用户feichi的身份连接到Linux计算机192.168.131.126。

ssh feichi@192.168.131.126	# 建立ssh连接之后，我们就可以操作远程的Linux服务器了

2. 进入超级用户模式。

sudo su

3. 创建新的备份目录。如果目录已经存在，-p参数则不会执行任何操作。

mkdir -p /var/opt/mssql/backup

4. 将备份文件移动到该目录。 在下面的示例中，备份文件位于 feichi 的主目录。 更改命令，使其与你的备份文件的位置和文件名相匹配。

# mv 被移动文件	移动目标位置
mv /home/feichi/dyyFeiCDB.bak /var/opt/mssql/backup/

5. 退出root模式。

exit

5、在 Linux 上还原数据库

如果你是直接通过自己的方式，将备份文件移动到了/var/opt/mssql/backup目录下，那么可以直接跳过第四步—在还原前移动备份文件。

在Linux中，要还原数据库备份，可以使用 RESTORE DATABASE Transact-SQL (TQL) 命令。

下列步骤使用 sqlcmd 工具。 如果尚未安装 SQL Server 工具，请参阅在 Linux 上安装 SQL Server 命令行工具。

1. 在同一终端中，启动 sqlcmd。 下面的示例以 SA 用户身份连接到本地 SQL Server。 出现提示时输入密码，或使用 -P 参数指定密码。

# 用户名密码连接
sqlcmd -S localhost -U sa -No 

2. 在 >1 提示符下，输入以下 RESTORE DATABASE 命令，并在每行后按 Enter（无法同时复制和粘贴整个多行命令）。 将出现的所有 YourDB 替换为你的数据库的名称。

RESTORE DATABASE YourDB
FROM DISK = '/var/opt/mssql/backup/YourDB.bak'
WITH MOVE 'YourDB' TO '/var/opt/mssql/data/YourDB.mdf',
MOVE 'YourDB_Log' TO '/var/opt/mssql/data/YourDB_Log.ldf'
GO

如下图：

3. 查看还原的数据库和数据库中的所有数据表

select name from sys.databases	# 检索所有的数据库名称
go
use dyyFeiCDB
go
# 查看当前数据库所有的数据表
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' 
ORDER BY TABLE_NAME
go
# 或者使用视图 sys.tables 来获取相同的信息
select name from sys.tables order by name
go