Scripts.Indisguise.Org - a hobbyist's blog and scripts archive

It seems that a lot of people have been asking around for a tutorial on how to backup and restore your databases/database tables. This is just a quick tutorial on how to backup/restore your databases.

In order to do this tutorial, you will need access to the domain’s control panel — if you are only hosted on someone else’s server, I’m afraid there is nothing you can do but ask him/her to backup your database tables for you.

This tutorial will walk you through backing up your databases and restoring them.

Backing up Comes First

So first, you must log into your Control Panel. The usual is CPanel, which is what I use, but this can differ according to where you’re hosted. You should ask your hosting provider if you’re lost at this point.

After clicking on the above encircled link (if you’re using CPanel, that is), scroll down to the bottom of the page and you will see the following link (also encircled) to your PHPMyAdmin installation.

Click that link. You should then get to your PHPMyAdmin installation, which will look somewhat similar to the screenshot below, depending on the version of PHPMyAdmin installed on your server. Below, an arrow points to a dropdown menu: you must select your fanlisting/s database from this dropdown. This is the database name, not the database table name.

After selecting your database from the dropdown, the right frame should load a list of the database tables in that database. Along the top is a series of links, one of which is Export:

Click that link. After that, that frame will load a page that contains a form. Explanation for each important part of the form is:

Export

This multi-select box contains all the tables in your database. Select the tables you wish to backup by pressing Shift and clicking on them with your mouse pointer. If you’re backing up, you naturally select everything. Selecting only a few tables is useful if you’re adopting out a fanlisting and need to give the members list. :)

SQL Options

By default, you don’t need to change anything in this section. Optionally, though, you can check the Add DROP TABLE checkbox — I’ll explain later why you might want to do this.

Save as file

You should check the checkbox beside this section, and that’s really the only thing you need to change for this section.

After all that, you should click on the submit button marked “Go”. Since you’ve checked the Save as file checkbox, a prompt should come up asking you where the file should be save. Choose a location on your hard drive where you can easily get this file (i.e., the Desktop for now) and go ahead and save it.

When the file has finished downloading, it will be named something like localhost.sql — this file can be opened with any ordinary text editor of your choice. This is also the file you will be sending people if you’re adopting out the fanlisting.

Time for Some Restoring

So now you’ve backed up your databases, you have the .sql file. What do you do with this now, if you want to restore your database after, GASP! a server crash?

You should first make sure that the database is created — not the database tables, but the database itself. After that, once again go to your PHPMyAdmin and select your database from the dropdown.

Usually if your database is wiped clean, there will be nothing here. However — and this is where checking the Add DROP TABLE option may come in handy — if you just want to restore a backup and completely replace your tables, unless you have checked that checkbox, you will have to select all tables (click the Check All link) and choose DROP:

However, again, if you checked the Add DROP TABLE checkbox, you won’t need to do this if you still have a full database.

In any case, now you have to go to the SQL tab instead of the Export tab:

Now, you can ignore the large text box in the resulting form; turn your attention to the Location of the textfile section. There is a file selector in that section (Browse...): click that and you will be able to select your .sql file. Then click “Go” in that section.

And once it’s finished processing (if there are errors, it will show up), you’re done restoring your databases!