Restore a MYSQL database from a BAK backup file

Question

I have a backup of a MYSQL database. Backup was a MS SQL format and extension is a BAK extension. Does anyone have steps to restore the BAK file to a MYSQL database? Do I need to convert it to a SQL extension then use MYSQL Workbench to restore?

Thank you

Answer 1

Your post is confusing. You first say that you have a backup of a MySQL database, and next you say that it was MS SQL format. I don't know MySQL, but I seriously doubt that any DBMS is able to produce a backup according to the format of a competing engine.

If you indeed have a backup of a MySQL Database, you need to ask in a MySQL forum for how to deal with it. This is a forum for Microsoft products and services, and the specific tag you have chosen is for Microsoft SQL Server.

If you have a backup of an database from Microsoft SQL Server... Well, again, I don't know MySQL, but I would say that chances are slim that MySQL is able to read SQL Server backups. (For instance, SQL Server is not able to read backup files from any other RDBMS). You would need to restore the backup on SQL Server, and then create scripts for the tables and data and run that script on MySQL. Beware that the script may fail on MySQL, because MySQL does not support all syntax supported by SQL Server (and vice versa).

I would also ask for an advice in a MySQL forum where they may have more experience of this.

Note that the extension is only part of the name. If you change MyBackup.BAK to MyBackup.SQL, this will not change anything.

Answer 2

Hi Gladiator,

Welcome to Q&A Forum, this is a great place to get support, answers and tips.

Thank you for posting your query, I'll be more than glad to help you out.

I just want to add something to the answer from Erland...

If you really have a backup file from an (old) Microsoft SQL Server with the ending *.bak and you want to restore it to a (Oracle) mySQL server... then you have to restore it to a Microsoft SQL Server and migrate it to mySQL...

Maybe this post can help you to understand the differences:

https://hevodata.com/learn/convert-sql-server-to-mysql/

I hope my answer is helpful to you,

Your

Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

Answer 3

yes, from what I have been reading this is related to SQL Studio Management. This tool is required to assist with the restore process. This is where I was hoping to get assistance from.

Again, it is still very unclear. If you are working with MySQL, why would use SSMS?

Since you talk about SSMS, I assume that you have SQL Server installed. (And if you don't, you have no use for SSMS.) Open SSMS and connect and open a query window. To restore this backup on Microsoft SQL Server, first run this command:

RESTORE FILELISTONLY FROM DISK = 'C:\Path\MyBackup.BAK'

If you get an output like this:

Msg 3241, Level 16, State 0, Line 11 The media family on device 'C:\temp\Clipboard01.jpg' is incorrectly formed. SQL Server cannot process this media family. Msg 3013, Level 16, State 1, Line 11 RESTORE FILELIST is terminating abnormally.

It's game over. What you have is not a valid SQL Server backup, and you have no use for SSMS.

On the other hand, if you get a result set like:

You are on the right track.

The next step is to restore the database itself:

RESTORE DATABASE MyDB FROM DISK = 'C:\path\MyBackup.mdf'
WITH MOVE 'MyDB' TO 'C:\Somepath\MyDB.mdf',
    MOVE 'MyDB_log' TO 'C:\Somepath\MyDB.ldf

Note that MyDB is only a placeholder, and you can use any name you like. However, for the names that follows MOVE, you should use the names the LogicalName column in the output from RESTORE FILELISTONLY.