Backing Up a Simple Machines Forum (SMF) database

If you wish to back up the database for a Simple Machines Forum 1.1.x forum, you can do so by logging into it with an administrator account and taking the following steps:

  1. Click on ADMIN.
  2. Under the Maintenance section, click on Forum Maintenance.
  3. You will see a maintenance section with checkboxes for various options.

    Forum Maintenance - Backup Database
    Save the table structure.
    Save the table data. (the important stuff.)

    Compress the file with gzip.

    Check all of the options and click on Download. You will get a file with a name of database_name-complete_date.sql.gz, e.g. smf-complete_2011-03-05.sql.gz.

If you wish to back up the database for a Simple Machines Forum 2.0 forum, you can do so by logging into it with an administrator account and taking the following steps:

  1. Click on ADMIN.
  2. Select Features and Options.
  3. Select Maintenance, Forum Maintenance, then Database.
  4. You will see a maintenance section with checkboxes for various options.

    Forum Maintenance - Backup Database
    Save the table structure.
    Save the table data (the important stuff).

    Compress the file with gzip.

    Check all of the options and click on Download. You will get a file with a name of database_name-complete_date.sql.gz, e.g. smf-complete_2011-03-05.sql.gz.

The file is a compressed .gz file, which can be uncompressed with utilities such as gunzip or other such file compression/decompression utilities. The .sql file within the .gz file contains Structured Query Language (SQL) commands that could be used to rebuild the database from the command line.

E.g., with a MySQLdatabase, you can use a command such as mysql -u username -p db_name < text_file to put tables associated with the forum back into the database, replacing the data that was in the database. For the above example, if I had uncompressed the file I downloaded so that I had smf-complete_2011-03-05.sql, I could use mysql -u jdoe -p < jdoe_smf smf-complete_2011-03-05.sql, presuming the username for the database was jdoe. You specify the username with -u and the -p leads MySQL to prompt you for the database password. In this case, the database name is jdoe_smf.

Another alternative to restoring the database is to do so from within MySQL. From within MySQL you can use the source command, e.g., source file_name or the \. command, e.g. \. filen_name.

mysql> source smf-complete_2011-03-05.sql
mysql> \. smf-complete_2011-03-05.sql

Note: if the database itself still exists and only the tables or data within them have to be restored, then all you need is the backup file you downloaded. If the database itself no longer exists or needs to be recreated on another system, then you need to create the database first.

You may also wish to backup all of the files associated with the forum as well. That can be done by downloading all of the files to your local system or copying all of the files to a backup directory on the server on which the forum resides.

On a Linux system, you can use the cp --archive source_directory destination_directory command to copy the files from one directory to another maintaining the permissions on the files when they are copied and to copy all subdirectories and files.

E.g., you could make a temp directory underneath your logn directory and copy all of the files to a directory named backup within it as shown below:

$ mkdir ~/temp
$ cp --archive forum ~/temp/backup

Note: If you, instead, simply download the files to a PC without putting them in a tar file first, you may lose all the permissions on the files, which may or may not be problematical when restoring them.

On a Linux, Unix, or Mac OS X system, you can use the tar command to create an archive file. E.g., the command below would create a tar file named example_20131003.tar from the directory exampledir. All files and subdirectories beneath exampledir would be placed in the tar file.

tar -cvf example_20131003.tar exampledir

If needed, the files could later be extracted with tar -xvf example_20131003.tar. The -c parameter is used for creating a tar file and the -x parameter extracts files from a tar file. The -f parameter specifies the file name and -v provides verbose output, i.e., you get more detail on what is happening.

You may be able to substantially reduce the size of the tar file using the gzip command. E.g.:

gzip example_20131003.tar

The command would produce a example_20131003.tar.gz file.

Note: if you are using the TinyPortal mod, you might encounter an error when restoring the forum database associated with TinyPortal such as I encountered when restoring a database. I saw the error below:

ERROR 1064 (42000) at line 4149: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on tinyint(4),
   PRIMARY KEY (id)
)

References:

  1. 4.5.1.5. Executing SQL Statements from a Text File
    MySQL :: Developer Zone

Valid HTML 4.01 Transitional

Created: March 5, 2011