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

Question

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

Solution 1

The .BAK files from SQL server are in Microsoft Tape Format (MTF) ref: 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.

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

So, install SQL Server Express edition, and open the SQL Server Powershell. There execute sqlcmd -S <COMPUTERNAME>\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

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

Solution 2

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

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.

Solution 3

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.

Worked like a charm!

Solution 4

In this problem, the answer is not updated in a timely. So it's happy to say that in 2020 Migrating to MsSQL into MySQL is that much easy. An online converter like RebaseData will do your job with one click. You can just upload your .bak file which is from MsSQL and convert it into .sql format which is readable to MySQL.

Additional note: This can not only convert your .bak files but also this site is for all types of Database migrations that you want.

Solution 5

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.

hope this helps

View more solutions

Comments

[Geoffrey]

The problem is that SQL Server 2008 Express Edition can only handle database files that are 10GB and smaller. I have a 30GB file I have to work with. The Internet connection at work is too slow to download the 4GB .iso image of the full SQL Server 2008 R2 version, which I get for free from dreamspark.com as a student. So I have this exact same question.

[Piotr Kula]

Yes the import worked fine for me. Right click on the 'databases' node click import- First select the BAK file then choose database - It automatically gets the database name from the loaded .bak file and puts it into the list.(to create the new database) Select that and click import.. Woohoo - 6 days later!

[Jakob]

MySQL Migration Toolkit has reached EOL. see dev.mysql.com/downloads/gui-tools/5.0.html

[Xiao]

MySql Workbench sucks. It cannot handle character encodings good enough

[Mike Lischke]

@Sean, if you see a problem with MySQL Workbench then file a bug report. Migration is a complicated matter, so implementations need a few rounds to become stable.

[dazweeja]

The 'Database Migration' functionality is now part of MySQL Workbench

[Brian Leishman]

The first install software link is broken

[Mikhail T.]

Are you sure about the file format being MTF? When I try to use the command-line mtf-tool to list the contents of a .bak file, I get an error...

[Richard Harrison]

@MikhailT.when I wrote this answer nearly 10 years ago I'm fairly sure that MTF was being used; this may have since changed.

[Titi]

Note we paid for RebaseData to have access to the Premium features (as Free only lets you do very small files) and we haven't been able to get RebaseData working with our nearly 35GB .bak file no matter what we tried: CLI tool, upload via API, etc. It pretty much ran out of memory everywhere (even though our server has 32GB memory and we gave it all we could)

[Titi]

Also worth noting they have a '7 day money back guarantee' if you can't convert your files and even though I emailed support with a very detailed explanation of everything we tried and hoping they'll be able to have a look on their side at the uploaded file via the API we still haven't heard back from them in over 2 weeks.

[realsarm]

etlalchemy is not maintained