Quick help needed with .sql file

[p0intblank]

I've been asked to transfer someone's website to a new host. While I know how to do everything via FTP, I am also required to install a new database. The client has sent me an .sql file, but I'm not really sure what to do with it. Can anyone please provide quick help? I would really appreciate it!

 

[twoodcc]

well it depends on the host on how you manage mysql databases. you have to import that file

 

[Phil A.]

Wirelessly posted (iPod touch 32GB: Mozilla/5.0 (iPhone; U; CPU iPhone OS 3_0 like Mac OS X; en-us) AppleWebKit/528.18 (KHTML, like Gecko) Version/4.0 Mobile/7A341 Safari/528.16)

It depends on what type of database it came from and what type of database it's going to (MySQL, SQL Server, Oracle, etc)
you'll definitely need more than FTP access to import it on to the new site though (ssh or remote desktop/vnc)

 

[angelwatt]

Here's some documentation for MySQL (if the file was from MySQL) that explains how you can execute the .sql file. You'll be able to do it once you are logged into the server via SSH or whatever they have available. You can even use phpMyAdmin to do the import if that's installed on the server.

 

[p0intblank]

Here's some documentation for MySQL (if the file was from MySQL) that explains how you can execute the .sql file. You'll be able to do it once you are logged into the server via SSH or whatever they have available. You can even use phpMyAdmin to do the import if that's installed on the server.

The host we are using (Network Solutions) does support phpMyAdmin. I've created a new database, but when I try to import the .sql file, it says the database already exists. Isn't there a way to import the file before creating a database? They seem to be conflicting with each other.

 

[angelwatt]

The host we are using (Network Solutions) does support phpMyAdmin. I've created a new database, but when I try to import the .sql file, it says the database already exists. Isn't there a way to import the file before creating a database? They seem to be conflicting with each other.

You don't need to create the new database in order to import. You start with the import. Though, you'll want to make sure it won't over write any existing databases you need. I have version 2.10.2 of phpMyAdmin, and once I went to the page there was an import on the front page, which took me to a screen where I could upload a file.

 

[p0intblank]

You don't need to create the new database in order to import. You start with the import. Though, you'll want to make sure it won't over write any existing databases you need. I have version 2.10.2 of phpMyAdmin, and once I went to the page there was an import on the front page, which took me to a screen where I could upload a file.

There is no option to import from the start. I have to add a new database, and then later upload the file. I'm attaching a screenshot to show you the options I am seeing.

 

[angelwatt]

Ah, so it's not quite phpMyAdmin. I see two options. You can see if you're able to SSH into your account to access MySQL more directly and use the link I provided earlier, though it's quite possible the host has limited the commands you can use through the command line.

The second option, is to open the sql file and edit it so it skips the database creation. It should be a plain text file and that should be very close to the top of the file. If you need help you can post part of that file here. Not sure how large it is so not sure if posting the whole thing would be a good idea. You could edit it so it checks if the database currently exists.

CREATE DATABASE IF NOT EXISTS dallas;

 

[p0intblank]

Ah, so it's not quite phpMyAdmin. I see two options. You can see if you're able to SSH into your account to access MySQL more directly and use the link I provided earlier, though it's quite possible the host has limited the commands you can use through the command line.

The second option, is to open the sql file and edit it so it skips the database creation. It should be a plain text file and that should be very close to the top of the file. If you need help you can post part of that file here. Not sure how large it is so not sure if posting the whole thing would be a good idea. You could edit it so it checks if the database currently exists.

CREATE DATABASE IF NOT EXISTS dallas;

I'm pretty sure it is phpMyAdmin. After I create the new database, I can then log into phpMyAdmin and configure everything there, including importing the database file. That's where I get stuck, though. The person I am doing this for also tried it himself and received an error. I'm going to contact Network Solutions and see what the deal is.

 

[NoNameBrand]

Clearly, the .sql file has a line in it (near the top!) to create and then use a database of a certain name.

CREATE DATABASE foo;
USE foo;

Make a copy of the file, delete that line and import the .sql file.

Update the site's code to use the new database info you have (host/user/password/database name).

 

[SrWebDeveloper]

Been reading through this, saw some conflicting advice. In my experience, a manually exported .sql is usually only table data and structure in ANSI SQL format, and its up to the importing user to create their own database named whatever they want (to avoid naming conflicts if this was decided by the exporter) set proper permissions, and then import to build just the tables and insert the data. The only times you usually see database creation included is for commercial software installation where an install script is involved. ANSI SQL is common to many of the database formats listed in this topic, i.e. MySQL, SQL SERVER, etc. This infers the .sql is in standard universal ANSI format, so MySQL is one choice of many (and a good choice) for import. One thing worth mentioning, since you didn't say from which database the export originated, Oracle is different and much more complex. Let us know if you're attempting Oracle->MySQL.

The person I am doing this for also tried it himself and received an error.

We would like to help, too, but not until you tell us the error and the original database format (prior to export) if possible. Thanks!

-jim