如何将 SQL Server .bak 文件导入 MySQL？

How to import a SQL Server .bak file into MySQL?

提问by Marcel

The title is self explanatory. Is there a way of directly doing such kind of importing?

标题是不言自明的。有没有办法直接进行这种导入？

回答by Richard Harrison

The .BAK files from SQL server are in Microsoft Tape Format (MTF) ref: http://www.fpns.net/willy/msbackup.htm

来自 SQL 服务器的 .BAK 文件采用 Microsoft 磁带格式 (MTF) 参考：http: //www.fpns.net/willy/msbackup.htm

The bak file will probably contain the LDF and MDF files that SQL server uses to store the database.

bak 文件可能包含 SQL Server 用于存储数据库的 LDF 和 MDF 文件。

You will need to use SQL server to extract these. SQL Server Express is free and will do the job.

您将需要使用 SQL 服务器来提取这些。SQL Server Express 是免费的，可以完成这项工作。

So, install SQL Server Express edition, and open the SQL Server Powershell. There execute sqlcmd -S <COMPUTERNAME>\SQLExpress(whilst logged in as administrator)

因此，安装 SQL Server Express 版本，并打开 SQL Server Powershell。那里执行sqlcmd -S <COMPUTERNAME>\SQLExpress（以管理员身份登录时）

then issue the following command.

然后发出以下命令。

restore filelistonly from disk='c:\temp\mydbName-2009-09-29-v10.bak';
GO

This will list the contents of the backup - what you need is the first fields that tell you the logical names - one will be the actual database and the other the log file.

这将列出备份的内容——你需要的是告诉你逻辑名称的第一个字段——一个是实际的数据库，另一个是日志文件。

RESTORE DATABASE mydbName FROM disk='c:\temp\mydbName-2009-09-29-v10.bak'
WITH 
   MOVE 'mydbName' TO 'c:\temp\mydbName_data.mdf', 
   MOVE 'mydbName_log' TO 'c:\temp\mydbName_data.ldf';
GO

At this point you have extracted the database - then install Microsoft's "Sql Web Data Administrator".together with this export tooland you will have an SQL script that contains the database.

此时你已经解压了数据库——然后安装微软的“Sql Web Data Administrator”。与此导出工具一起使用，您将拥有一个包含数据库的 SQL 脚本。

回答by AutoCiudad

MySql have an application to import db from microsoft sql. Steps:

MySql 有一个从 microsoft sql 导入数据库的应用程序。脚步：

1. Open MySql Workbench
2. Click on "Database Migration" (if it do not appear you have to install it from MySql update)
3. Follow the Migration Task List using the simple Wizard.

1. 打开 MySql 工作台
2. 单击“数据库迁移”（如果没有出现，则必须从 MySql 更新中安装它）
3. 使用简单的向导遵循迁移任务列表。

回答by Marcel

I did not manage to find a way to do it directly.

我没有设法找到直接做到这一点的方法。

Instead I imported the bak file into SQL Server 2008 Express, and then used MySQL Migration Toolkit.

相反，我将 bak 文件导入 SQL Server 2008 Express，然后使用MySQL Migration Toolkit。

Worked like a charm!

像魅力一样工作！

回答by Indrajith Ekanayake

In this problem, the answer is not updated in a timely. So it's happy to say that in 2020Migrating to MsSQLinto MySQLis that much easy. An online converter like RebaseDatawill do your job with one click. You can just upload your .bakfile which is from MsSQLand convert it into .sqlformat which is readable to MySQL.

在这个问题中，答案没有及时更新。所以很高兴地说，在 2020 年迁移MsSQL到MySQL这里非常容易。像RebaseData这样的在线转换器将一键完成您的工作。您只需上传来自的.bak文件MsSQL并将其转换.sql为可读的格式即可MySQL。

Additional note: Thiscan not only convert your .bakfiles but also this site is for all types of Database migrations that you want.

附加说明：这不仅可以转换您的.bak文件，而且该站点还适用于您想要的所有类型的数据库迁移。

回答by Levi Rosol

Although my MySQL background is limited, I don't think you have much luck doing that. However, you should be able to migrate over all of your data by restoring the db to a MSSQL server, then creating a SSIS or DTS package to send your tables and data to the MySQL server.

虽然我的 MySQL 背景有限，但我认为你这样做的运气并不好。但是，您应该能够通过将数据库恢复到 MSSQL 服务器，然后创建 SSIS 或 DTS 包将您的表和数据发送到 MySQL 服务器来迁移所有数据。

hope this helps

希望这可以帮助

回答by The Aelfinn

The .bak file from SQL Server is specific to that database dialect, and not compatible with MySQL.

SQL Server 中的 .bak 文件特定于该数据库方言，与 MySQL 不兼容。

Try using etlalchemyto migrate your SQL Server database into MySQL. It is an open-sourced tool that I created to facilitate easy migrations between different RDBMS's.

尝试使用etlalchemy将您的 SQL Server 数据库迁移到 MySQL。它是我创建的一个开源工具，用于促进不同 RDBMS 之间的轻松迁移。

Quick installation and examples are provided here on the github page, and a more detailed explanation of the project's origins can be found here.

github页面上提供了快速安装和示例，可以在此处找到对项目起源的更详细说明。

回答by Charles Graham

I highly doubt it. You might want to use DTS/SSIS to do this as Levi says. One think that you might want to do is start the process without actually importing the data. Just do enough to get the basic table structures together. Then you are going to want to change around the resulting table structure, because whatever structure tat will likely be created will be shaky at best.

我非常怀疑。正如 Levi 所说，您可能想使用 DTS/SSIS 来执行此操作。一种认为您可能想要做的是在不实际导入数据的情况下启动该过程。只需将基本的表结构放在一起就足够了。然后，您将想要围绕结果表结构进行更改，因为可能创建的任何结构 tat 充其量都是不稳定的。

You might also have to take this a step further and create a staging area that takes in all the data first n a string (varchar) form. Then you can create a script that does validation and conversion to get it into the "real" database, because the two databases don't always work well together, especially when dealing with dates.

您可能还需要更进一步，并创建一个暂存区域，该区域首先接收所有数据 na 字符串 (varchar) 形式。然后您可以创建一个执行验证和转换的脚本以将其放入“真实”数据库中，因为这两个数据库并不总是能很好地协同工作，尤其是在处理日期时。

回答by CyberFonic

SQL Server databases are very Microsoft proprietary. Two options I can think of are:

SQL Server 数据库是 Microsoft 专有的。我能想到的两个选项是：

1. Dump the database in CSV, XML or similar format that you'd then load into MySQL.

2. Setup ODBC connection to MySQL and then using DTS transport the data. As Charles Graham has suggested, you may need to build the tables before doing this. But that's as easy as a cut and paste from SQL Enterprise Manager windows to the corresponding MySQL window.

1. 以 CSV、XML 或类似格式转储数据库，然后将其加载到 MySQL 中。

2. 设置到 MySQL 的 ODBC 连接，然后使用 DTS 传输数据。正如 Charles Graham 所建议的，您可能需要在执行此操作之前构建表。但这就像从 SQL 企业管理器窗口剪切和粘贴到相应的 MySQL 窗口一样简单。

回答by Traveling_Monk

The method I used included part of Richard Harrison's method:

我使用的方法包括了 Richard Harrison 方法的一部分：

So, install SQL Server 2008 Express edition,

所以，安装 SQL Server 2008 Express 版，

This requires the download of the Web Platform Installer "wpilauncher_n.exe" Once you have this installed click on the database selection ( you are also required to download Frameworks and Runtimes)

这需要下载 Web 平台安装程序“wpilauncher_n.exe” 一旦你安装了这个，点击数据库选择（你还需要下载框架和运行时）

After instalation go to the windows command prompt and:

安装后转到 Windows 命令提示符并：

use sqlcmd -S \SQLExpress (whilst logged in as administrator)

then issue the following command.

restore filelistonly from disk='c:\temp\mydbName-2009-09-29-v10.bak'; GO This will list the contents of the backup - what you need is the first fields that tell you the logical names - one will be the actual database and the other the log file.

RESTORE DATABASE mydbName FROM disk='c:\temp\mydbName-2009-09-29-v10.bak' WITH MOVE 'mydbName' TO 'c:\temp\mydbName_data.mdf', MOVE 'mydbName_log' TO 'c:\temp\mydbName_data.ldf'; GO

使用 sqlcmd -S \SQLExpress （以管理员身份登录时）

然后发出以下命令。

从 disk='c:\temp\mydbName-2009-09-29-v10.bak' 恢复 filelistonly; GO 这将列出备份的内容——你需要的是告诉你逻辑名称的第一个字段——一个是实际的数据库，另一个是日志文件。

RESTORE DATABASE mydbName FROM disk='c:\temp\mydbName-2009-09-29-v10.bak' WITH MOVE 'mydbName' TO 'c:\temp\mydbName_data.mdf', MOVE 'mydbName_log' TO 'c:\ temp\mydbName_data.ldf'; 走

I fired up Web Platform Installer and from the what's new tab I installed SQL Server Management Studio and browsed the db to make sure the data was there...

我启动了 Web 平台安装程序，然后从新选项卡中安装了 SQL Server Management Studio 并浏览了数据库以确保数据在那里......

At that point i tried the tool included with MSSQL "SQL Import and Export Wizard" but the result of the csv dump only included the column names...

那时我尝试了 MSSQL“SQL 导入和导出向导”中包含的工具，但 csv 转储的结果只包含列名...

So instead I just exported results of queries like "select * from users" from the SQL Server Management Studio

因此，我只是从 SQL Server Management Studio 导出了诸如“select * from users”之类的查询结果

回答by Andrew

For those attempting Richard's solution above, here are some additional information that might help navigate common errors:

对于那些尝试上述 Richard 解决方案的人，以下是一些可能有助于解决常见错误的附加信息：

1) When running restore filelistonly you may get Operating system error 5(Access is denied). If that's the case, open SQL Server Configuration Manager and change the login for SQLEXPRESS to a user that has local write privileges.

1) 运行 restore filelistonly 时，您可能会收到操作系统错误 5（访问被拒绝）。如果是这种情况，请打开 SQL Server 配置管理器并将 SQLEXPRESS 的登录名更改为具有本地写入权限的用户。

2) @"This will list the contents of the backup - what you need is the first fields that tell you the logical names" - if your file lists more than two headers you will need to also account for what to do with those files in the RESTORE DATABASE command. If you don't indicate what to do with files beyond the database and the log, the system will apparently try to use the attributes listed in the .bak file. Restoring a file from someone else's environment will produce a 'The path has invalid attributes. It needs to be a directory' (as the path in question doesn't exist on your machine). Simply providing a MOVE statement resolves this problem.

2）@“这将列出备份的内容 - 您需要的是告诉您逻辑名称的第一个字段” - 如果您的文件列出了两个以上的标题，您还需要说明如何处理这些文件恢复数据库命令。如果您没有指明如何处理数据库和日志之外的文件，系统显然会尝试使用 .bak 文件中列出的属性。从其他人的环境中恢复文件将产生“路径具有无效属性”。它必须是一个目录”（因为您的机器上不存在有问题的路径）。只需提供 MOVE 语句即可解决此问题。

In my case there was a third FTData type file. The MOVE command I added:

就我而言，有第三个 FTData 类型的文件。我添加的 MOVE 命令：

MOVE 'mydbName_log' TO 'c:\temp\mydbName_data.ldf',
MOVE 'sysft_...' TO 'c:\temp\other';

in my case I actually had to make a new directory for the third file. Initially I tried to send it to the same folder as the .mdf file but that produced a 'failed to initialize correctly' error on the third FTData file when I executed the restore.

就我而言，我实际上不得不为第三个文件创建一个新目录。最初，我尝试将它发送到与 .mdf 文件相同的文件夹，但是当我执行恢复时，在第三个 FTData 文件上产生了“无法正确初始化”错误。