How to import a .bak file into a new SQL server

[spenland]

Hi everyone, I'm not very well versed in SQL. I've been given a .bak file that is a backup of a clients database. I need to upload this file to a NEW server. I have abosultely no idea how to do this. I've read through some posts and some people have mentioned a "RESTORE" command but I really just don't know what I'm doing. Can someone help me with this?

Thanks!

[wige]

What type and version of SQL server is the backup from, and what version are you uploading to?

[spenland]

I'm not sure what the original backup was made under but the version of SQL on my server is MySQL 4.1.22-log.

[wige]

If the backup is a text file consisting of MySQL commands, you should be able to do this locally through a shell by entering

mysql -u username -ppassword database_name < file.bak

Note there is no space between "-p" and your password.

If you have a utility like phpMyAdmin, there is also a utility for recovering from a backup built in.

[spenland]

Thanks, but I don't know how to run shell commands. I'm using PHPMyAdmin so if there is a way to restore the database through it that would be great.

[wige]

See Backup and Restore of MySql database with phpMyAdmin for instructions. If the backup file is too large, the only option may be using command line tools, which may mean the hosting company has to do it for you.

[spenland]

Funny that you sent that link as I did a google search earlier and followed the instructions on that page. Unfortunately I was not successful in importing the database by following those directions and I received a weird error saying that I may have found a bug in the MYSQL software!

I'm wondering if its because the database names are not the same. My host, aplus.net, doesn't allow you to name your database it just automatically creates one named after your FTP username.

So if my database is named "example1" and the database I'm importing is named "database1B" will I have problems?

[computergenius]

You will have problems if the database name is in your SQL file (what you call your bak file)

How big is the file? Are you aware that it is a text file, and that if you have a suitable editor, you can edit A COPY OF (!) the file. If the file contains the "use database" command, you can just delete it, as you have already told PHPMyAdmin which database to use.

The SQL file should contain a CREATE statement for each table, together with the INSERT commands for all the records.

[spenland]

The file is only 2.5 MB. It is weird because i opened up the file in notepad and it appears as if there are alot of erroneous characters and crap in the file. I saw no CREATE or any other SQL type statements. I'm wondering if the network guy #$%# up when backing this up.

When I try to import into PHPmyAdmin i get this error.

"There is a chance that you may have found a bug in the SQL parser. Please examine your query closely, and check that the quotes are correct and not mis-matched. Other possible failure causes may be that you are uploading a file with binary outside of a quoted text area. You can also try your query on the MySQL command line interface. The MySQL server error output below, if there is any, may also help you in diagnosing the problem. If you still have problems or if the parser fails where the command line interface succeeds, please reduce your SQL query input to the single query that causes problems, and submit a bug report with the data chunk in the CUT section below:"

[seiretto]

That sounds like the file is a .gz (gzip) or .zip file.

Try copying the file and renaming it with a .gz and .zip extension (ie. myfile.gz, and myfile.zip) and try an import again on those.

If it times-out try this:
DWalker.co.uk :: View topic - Large MySQL database restore - server timing out? Try this

Hope that helps

[wige]

There are several different ways to create a backup of a MySQL database. The most common is what has been discussed mostly above and which is covered in the documentation mentioned. This creates a single text file that lists all the SQL commands needed to recreate the database as it was. Other methods include simply taking the actual database data files from the original server and compressing and archiving them. This is usually less than ideal because you can run into problems with differences between versions of MySQL.

Does the original database still exist somewhere, or is it only in the backup file?

[spenland]

Thanks for your responses guys. The database does still exist but I've had a very difficult time connecting to it. This database is on a different server than the website.

If I want to access the FTP web server, i just plug in the website, username, and password and I'm in no problem. However, for the database I was told to use:

unixsql2.interl.net

When I try to connect through MySQL Front, I get an error. What could I be missing that is not allowing me to connect?

[wige]

I'd have to see the documentation to know for sure, but I think this means that the database is stored on another server from your main site. The unixsql2.interl.net address is the URL that your scripts would use to talk to the server. This server probably has a firewall set up to only allow connections from the web servers. You should not be able to connect to that URL from anywhere else.

[spenland]

Yes I was afraid of that, oh well I guess I'll be doing some data entry this weekend.

[computergenius]

Why not set up MySQL on your local Windows computer in your office? Use something like WAMP, it will just about set itself up. Then you can try loading the data without any restrictions.

When you have the data loaded in your own database, you can make a backup, which you can then load into the live database.

Much easier than re-typing all the data!

[hostBrain]

Installing Wamp on your desktop is a pretty good idea, and may come in handy in the future as well. YOu can get one here apache friends - xampp for windows

It would also be a good idea to find out what version of mysql the db is from, and how it was backed up