ID #1143

Can I restore a backup on another server than where it was created?

Yes, you can.

But please first also read this FAQthat explains the basics about the backup functionalities in SQLyog.

As long as the 'main' Server versions are identical SQLyog backups will restore without problems. For instance a backup from 4.1.10 will restore on 4.1.22. Also in backups from 4.0.x will restore on 3.23.x (and vice versa), and all version >= 4.1 will restore on those versions. The problems arise between versions up to and after 4.0 respectively.

The problems can be summarized like this:

MySQL versions before 4.1.x do not support Unicode (neither utf8 nor ucs2).

By default SQLyog uses utf8 for the backup when possible to have the server send as such. This in order to ensure that data will always be correctly read from and written to the database no matter the LOCALE (language setting) of the client machine where the operations are performed.

If you know that you will need to restore a backup from 4.1 or later on a lower version the Scheduled Backup 'powertool' of SQLyog Enterprise and Ultimate gives an option to select another charset/encoding than utf8. Select the charset option that corresponds how you want data stored on the server (like 'latin1' for western languages etc.)

If you need to use an existing backup that uses utf8 you can open it in a text editor (like Notepad) and just 'save as' and select ANSI encoding before pressing OK. Now the file becomes re-encoded. Note that this will re-encode the file to the ANSI codepage used by the LOCALE/language setting of your Windows installation.

Versions before 4.1.x do not support
--> client/session SET NAMES statement
--> client/session SET SQL_MODE statement

SQLyog uses those statements when backing up from server versions that support it. However those statements are enclosed in 'version dependent comments' (like /*!40101 SET
NAMES utf8 */). So those statements will be ignored on server versions that do not support them. You will not need to do anything yourself due to this!

Versions before 4.1.x do not have as advanced CREATE statement for tables as later versions. The details are
--> ENGINE and CHARSET specification in CREATE TABLE statement is not supported.
--> Table and Column comments are not supported.

In a text editor it is very easy to 'search and replace' ENGINE and CHARSET specification with an empty string. Comments can be a little more time-consuming if you have lots of them. But with advanced editors supporting 'wildcards' in 'search and replace' or with an editor or similar tool supporting 'regular expressions' this is also rather fast and easy.

Note that if you need to open very large text files you may experience very big different performance with text editors! And do not use old editors that do not support utf8 encoding when opening a utf8-encoded file - most likely the content will garble and it can be very tricky to correct this!  Also do not use a Word processor.

You should also note that with very early MySQL 5.0.x versions there may be some incompatible syntax in the CREATE statements for Stored Procedures, Functions and Triggers compared to recent versions. But those version affected should now almost be out of use. You should absolutely upgrade the server if you still have such early version! And if you have an old backup from such version you need to restore and need help, then please contact us through the Forums or Support Ticket system.

Finally you should notice that SQLyog Structure Synchronization will work across server versions and generate CREATE statements that will work for every MySQL version. You can use Structure Synchronization for creating the structure (DDL type of statements) and backup tools for inserting data (DML type of statements).

Tags: -

Related entries:

You can comment this FAQ