linux装windows数据库,将 Windows 中的 SQL Server 数据库迁移到 Linux - SQL Server | Microsoft Docs...

使用备份和还原将 SQL Server 数据库从 Windows 迁移到 LinuxMigrate a SQL Server database from Windows to Linux using backup and restore

04/08/2021

本文内容

适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions) - Linux适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions) - Linux

建议使用 SQL Server 的备份和还原功能将数据库从 Windows 上的 SQL Server 迁移到 Linux 上的 SQL Server。SQL Server's backup and restore feature is the recommended way to migrate a database from SQL Server on Windows to SQL Server on Linux. 在本教程中，将逐步完成使用备份和还原方法将数据库迁移到 Linux 的必需步骤。In this tutorial, you will walk through the steps required to move a database to Linux with backup and restore techniques.

使用 SSMS 在 Windows 上创建备份文件Create a backup file on Windows with SSMS

在 Windows 上安装 Bash shellInstall a Bash shell on Windows

将备份文件从 Bash shell 移到 LinuxMove the backup file to Linux from the Bash shell

在 Linux 上通过 Transact-SQL 还原备份文件Restore the backup file on Linux with Transact-SQL

运行查询以验证迁移Run a query to verify the migration

还可以创建一个 SQL Server Always On 可用性组，将 SQL Server 的数据库从 Windows 迁移到 Linux。You can also create a SQL Server Always On Availability Group to migrate a SQL Server database from Windows to Linux.

必备条件Prerequisites

若要完成本教程，需满足以下先决条件：The following prerequisites are required to complete this tutorial:

安装了以下内容的 Windows 计算机：Windows machine with the following:

安装了以下内容的 Linux 计算机：Linux machine with the following installed:

带有命令行工具的 SQL Server(RHEL、SLES 或 Ubuntu)。SQL Server (RHEL, SLES, or Ubuntu) with command-line tools.

在 Windows 上创建备份Create a backup on Windows

有多种方法可在 Windows 上创建数据库的备份文件。There are several ways to create a backup file of a database on Windows. 以下步骤使用 SQL Server Management Studio (SSMS)。The following steps use SQL Server Management Studio (SSMS).

在 Windows 计算机中启动 SQL Server Management Studio。Start SQL Server Management Studio on your Windows machine.

在连接“对话框”中，输入“localhost”。In the connection dialog, enter localhost.

在“对象资源管理器”中，展开“数据库”。In Object Explorer, expand Databases.

右键单击目标数据库，选择“任务”，再单击“备份...”。Right-click your target database, select Tasks, and then click Back Up....

在“备份数据库”对话框中，验证“备份类型”是否为“完全”，以及“备份到”是否为“磁盘”。In the Backup Up Database dialog, verify that Backup type is Full and Back up to is Disk. 注意文件的名称和位置。Note name and location of the file. 例如，SQL Server 2016 上名为“YourDB”的数据库的默认备份路径为 C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\YourDB.bak。For example, a database named YourDB on SQL Server 2016 has a default backup path of C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\YourDB.bak.

单击“确定”以备份数据库。Click OK to back up your database.

备注

创建备份文件的另一种方法是运行 Transact-SQL 查询。Another option is to run a Transact-SQL query to create the backup file. 以下 Transact-SQL 命令对名为“YourDB”的数据库执行与前面步骤相同的操作：The following Transact-SQL command performs the same actions as the previous steps for a database called 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

在 Windows 上安装 Bash shellInstall a Bash shell on Windows

若要还原数据库，首先必须将备份文件从 Windows 计算机传输到目标 Linux 计算机。To restore the database, you must first transfer the backup file from the Windows machine to the target Linux machine. 在本教程中，我们将该文件从在 Windows 上运行的 Bash shell(终端窗口)移动到 Linux。In this tutorial, we move the file to Linux from a Bash shell (terminal window) running on Windows.

在支持“scp”(安全复制)和“ssh”(远程登录)命令的 Windows 计算机上安装 Bash shell。Install a Bash shell on your Windows machine that supports the scp (secure copy) and ssh (remote login) commands. 以下介绍两个示例：Two examples include:

在 Windows 上打开 Bash 会话。Open a Bash session on Windows.

将备份文件复制到 LinuxCopy the backup file to Linux

在 Bash 会话中，导航到包含备份文件的目录。In your Bash session, navigate to the directory containing your backup file. 例如：For example:

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

使用 scp 命令将文件传输到目标 Linux 计算机。Use the scp command to transfer the file to the target Linux machine. 下面的示例将 YourDB.bak 传输到 Linux 服务器上的 user1 的主目录，IP 地址为 192.0.2.9：The following example transfers YourDB.bak to the home directory of user1 on the Linux server with an IP address of 192.0.2.9:

scp YourDB.bak user1@192.0.2.9:./

提示

对于文件传输，可以使用 scp 替代方法。There are alternatives to using scp for file transfer. 一种方法是使用 Samba 在 Windows 和 Linux 之间配置 SMB 网络共享。One is to use Samba to configure an SMB network share between Windows and Linux. 关于 Ubuntu 上的演练，请参阅如何通过 Samba 创建网络共享。建立后，可以通过 Windows 将它作为网络共享文件进行访问，如 \\machinenameorip\share.Once established, you can access it as a network file share from Windows, such as \\machinenameorip\share.

在还原前移动备份文件Move the backup file before restoring

此时，备份文件位于你的用户的主目录中的 Linux 服务器上。At this point, the backup file is on your Linux server in your user's home directory. 将数据库还原到 SQL Server 之前，必须将备份放入“/var/opt/mssql”的子目录中，因为这属于用户 mssql 和组 mssql。Before restoring the database to SQL Server, you must place the backup in a subdirectory of /var/opt/mssql, as this is owned by the user mssql and group mssql. 如果想要更改默认备份位置，请查看使用 mssql-conf 进行配置一文。If you are looking to change the default backup location, see the Configure with mssql-conf article.

在同一 Windows Bash 会话中，通过 ssh 远程连接到目标 Linux 计算机。In the same Windows Bash session, connect remotely to your target Linux machine with ssh. 以下示例以用户 user1 的身份连接到 Linux 计算机 192.0.2.9。The following example connects to the Linux machine 192.0.2.9 as user user1.

ssh user1@192.0.2.9

你现在正在远程 Linux 服务器上运行命令。You are now running commands on the remote Linux server.

进入超级用户模式。Enter super user mode.

sudo su

创建新的备份目录。Create a new backup directory. 如果目录已存在，-p 参数不会执行任何操作。The -p parameter does nothing if the directory already exists.

mkdir -p /var/opt/mssql/backup

将备份文件移动到该目录。Move the backup file to that directory. 在下面的示例中，备份文件位于 user1 的主目录。In the following example, the backup file resides in the home directory of user1. 更改命令，使其与你的备份文件的位置和文件名相匹配。Change the command to match the location and file name of your backup file.

mv /home/user1/YourDB.bak /var/opt/mssql/backup/

退出超级用户模式。Exit super user mode.

exit

在 Linux 上还原数据库Restore your database on Linux

要还原数据库备份，可以使用 RESTORE DATABASE Transact-SQL (TQL) 命令。To restore the database backup, you can use the RESTORE DATABASE Transact-SQL (TQL) command.

备注

下列步骤使用 sqlcmd 工具。The following steps use the sqlcmd tool.

在同一终端中，启动 sqlcmd。In the same terminal, launch sqlcmd. 下面的示例以 SA 用户身份连接到本地 SQL Server。The following example connects to the local SQL Server instance with the SA user. 出现提示时输入密码，或使用 -P 参数指定密码。Enter the password when prompted, or specify the password by adding the -P parameter.

sqlcmd -S localhost -U SA

在 >1 提示符下，输入以下 RESTORE DATABASE 命令，并在每行后按 Enter(无法同时复制和粘贴整个多行命令)。At the >1 prompt, enter the following RESTORE DATABASE command, pressing ENTER after each line (you cannot copy and paste the entire multi-line command at once). 将出现的所有 YourDB 替换为数据库的名称。Replace all occurrences of YourDB with the name of your database.

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

应收到已成功还原数据库的消息。You should get a message the database is successfully restored.

RESTORE DATABASE 可能会返回类似于以下示例的错误：RESTORE DATABASE may return an error like the following example:

File 'YourDB_Product' cannot be restored to 'Z:\Microsoft SQL Server\MSSQL11.GLOBAL\MSSQL\Data\YourDB\YourDB_Product.ndf'. Use WITH MOVE to identify a valid location for the file.

Msg 5133, Level 16, State 1, Server servername, Line 1

Directory lookup for the file "Z:\Microsoft SQL Server\MSSQL11.GLOBAL\MSSQL\Data\YourDB\YourDB_Product.ndf" failed with the operating system error 2(The system cannot find the file specified.).

在这种情况下，数据库包含辅助文件。In this case, the database contains secondary files. 如果未在 RESTORE DATABASE 的 MOVE 子句中指定这些文件，则还原过程将尝试在与原始服务器相同的路径中创建这些文件。If these files are not specified in the MOVE clause of RESTORE DATABASE, the restore procedure will try to create them in the same path as the original server.

可以列出备份中包含的所有文件：You can list all files included in the backup:

RESTORE FILELISTONLY FROM DISK = '/var/opt/mssql/backup/YourDB.bak'

GO

应会看到如下所示的列表(仅列出前两列)：You should get a list like the one below (listing only the two first columns):

LogicalName PhysicalName ..............

----------------------------------------------------------------------------------------------------------------------

YourDB Z:\Microsoft SQL Server\MSSQL11.GLOBAL\MSSQL\Data\YourDB\YourDB.mdf ..............

YourDB_Product Z:\Microsoft SQL Server\MSSQL11.GLOBAL\MSSQL\Data\YourDB\YourDB_Product.ndf ..............

YourDB_Customer Z:\Microsoft SQL Server\MSSQL11.GLOBAL\MSSQL\Data\YourDB\YourDB_Customer.ndf ..............

YourDB_log Z:\Microsoft SQL Server\MSSQL11.GLOBAL\MSSQL\Data\YourDB\YourDB_Log.ldf ..............

可使用此列表为其他文件创建 MOVE 子句。You can use this list to create MOVE clauses for the additional files. 在本示例中，RESTORE DATABASE 为：In this example, the RESTORE DATABASE is:

RESTORE DATABASE YourDB

FROM DISK = '/var/opt/mssql/backup/YourDB.bak'

WITH MOVE 'YourDB' TO '/var/opt/mssql/data/YourDB.mdf',

MOVE 'YourDB_Product' TO '/var/opt/mssql/data/YourDB_Product.ndf',

MOVE 'YourDB_Customer' TO '/var/opt/mssql/data/YourDB_Customer.ndf',

MOVE 'YourDB_Log' TO '/var/opt/mssql/data/YourDB_Log.ldf'

GO

通过列出服务器上的所有数据库来验证还原。Verify the restoration by listing all of the databases on the server. 应该会列出已还原的数据库。The restored database should be listed.

SELECT Name FROM sys.Databases

GO

在已迁移的数据库上运行其他查询。Run other queries on your migrated database. 以下命令将上下文切换到 YourDB 数据库，并从其一个表中选择行。The following command switches context to the YourDB database and selects rows from one of its tables.

USE YourDB

SELECT * FROM YourTable

GO

使用 sqlcmd 完成后，键入 exit。When you are done using sqlcmd, type exit.

在远程 ssh 会话中完成操作后，再次键入 exit。When you are done working in the remote ssh session, type exit again.

后续步骤Next steps

在本教程中，了解了如何在 Windows 上备份数据库，并将其移动到运行 SQL Server 的 Linux 服务器。In this tutorial, you learned how to back up a database on Windows and move it to a Linux server running SQL Server. 你已了解如何执行以下操作：You learned how to:

使用 SSMS 和 Transact-SQL 在 Windows 上创建备份文件Use SSMS and Transact-SQL to create a backup file on Windows

在 Windows 上安装 Bash shellInstall a Bash shell on Windows

使用 scp 将备份文件从 Windows 移动到 LinuxUse scp to move backup files from Windows to Linux

使用 ssh 远程连接到 Linux 计算机Use ssh to remotely connect to your Linux machine

重新定位备份文件以准备还原Relocate the backup file to prepare for restore

使用 sqlcmd 运行 Transact-SQL 命令Use sqlcmd to run Transact-SQL commands

通过 RESTORE DATABASE 命令还原数据库备份Restore the database backup with the RESTORE DATABASE command

运行查询以验证迁移Run the query to verify the migration

接下来，请浏览 Linux 上的 SQL Server 的其他迁移方案。Next, explore other migration scenarios for SQL Server on Linux.