How to: Restoring Databases on a server with an MMA for cPanel/WHM Server

Please note:

The following guide is only relevant to customers with a cPanel or WHM server who also have an active Monitoring and Maintenance Agreement (MMA) attached to the server.
To restore databases older than the last 24 hours, simply use veeam or r1soft to restore the cpmove file/s located in /home/r1softtemp from an older restore point. Once the older cpmove file is restored you can continue the guide below.

With databases, you have the option of using the cpmove file within /home/r1softtemp.

Example:

To restore using the SQL from the cpmove file within /home/r1softtemp, you can extract the SQL using the following method.
Find your user (I will be using "acctest" for this example). Find the account, and the time the backup was done using the below example.

# cd /home/r1softtemp/
# ls acctest -lah

root@server [/home/r1softtemp]# ls acctest -lah
-rw------- 1 root root 6.3M May 6 02:26 cpmove-acctest.tar.gz

You will see that there is a backup from 2 AM, on the 6th of May.
To list the SQL files, you can use the following command on the file.

# tar tf cpmove-{USER}.tar.gz | grep sql | grep -v 'timestamps\|.create'

root@server [/home/r1softtemp]# tar tf cpmove-acctest.tar.gz | grep sql | grep -v 'timestamps\|.create'

cpmove-acctest/psql_grants.sql
cpmove-acctest/mysql/
cpmove-acctest/mysql/openfileslimit
cpmove-acctest/mysql/acctest_test.sql
cpmove-acctest/mysql.sql
cpmove-acctest/psql/
cpmove-acctest/psql_users.sql

This gives a list of all available databases (removing mysql-timestamp files, and .create files which are not required for us).
If I want to extract the acctest_test database, I would use the following command

# tar xzvf cpmove-acctest.tar.gz cpmove-acctest/mysql/acctest_test.sql

As you can see, this extracts only the file itself.

root@server [/home/r1softtemp]# find cpmove-acctest

cpmove-acctest
cpmove-acctest/mysql
cpmove-acctest/mysql/acctest_test.sql

To Restore this file you will want to take a backup of the already existing database first. This is highly recommended in case you run into any problems:

# mysqldump acctest_test > /home/acctest/acctest_test.BAK20140506.sql

You will then be able to restore the database using the following command:

# mysql acctest_test < cpmove-acctest/mysql/acctest_test.sql