Cannot find SQL backup file for restore

[dc7]

I regularly did backups of my site via CPanel > Backups > Full Backup, by pressing the 'download or generate a full website backup'.

(I also did it under the Partial Backups too - not sure if that means anything).

Anyway, my site was built on a Wordpress platform and I mistakenly uninstalled Wordpress so my site got entirely wiped out. I was able to successfully restore the home directory from the backup file. But I have not been able to successfully restore the MySQL database. The MySQL backup file is only 4K, which seems to small to hold my entire database.

I opened the .gz MySQL backup file and this is all that is in it:

- - - - - - - - -
-- MySQL dump 10.11
--
-- Host: localhost Database: (database_name)
-- ------------------------------------------------------
-- Server version 5.0.92-community-log

/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @[email protected]@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @[email protected]@SQL_NOTES, SQL_NOTES=0 */;

--
-- Temporary table structure for view `titles_data`
--

DROP TABLE IF EXISTS `titles_data`;
- - - - - - - - - - - -

From reading online, it seems that this file should actually contain a bunch of insert commands and data that are used to rebuild all of the dozen or so tables that were in my database. Above only shows one table called titles_data. All of my backups I've done over time look like this. I am worried this backup function didn't work correctly and it is missing 95% of the tables needed to rebuild the database.

I am new to Web Development so any insight would be appreciated.

 

[Infopro]

In the full backup should be a mysql directory that would contain the MySQL databases for your account.

GL!

 

[dc7]

Thanks for the quick response - and on a Saturday!

In the full backup file, I see three sql related top level folders/files:

- mysql folder
- mysql-timestamps folder
- mysql.sql file

A.) I went to the mysql folder and it contains two pertinent files I think:
- database_name.create
- database_name.sql (4k size)

File 'database_name.sql' file shows the same data as I mentioned above. It only references one table (titles_data) of the dozen or so tables that were in the database.

The table 'titles_data' is one of two tables that I created in the database (the others were mostly default tables created by Wordpress from the install). Is it possible that I set up the table in a weird way by mistake and it is a master table of some kind that excluded the others? Or maybe the cPanel backup function just didn't work correctly?

B.) Looking in the sql-timestamps folder - I don't think I see any pertinent files here.

C.) mysql.sql file
I opened the file and it contains about a dozen lines of data with Grant Usage and Privileges stuff that I don't think is what we need.

So I think I still don't see the correct data in the SQL backup file that I think is needed for the restore. Hopefully I am missing something!

Thanks.

 

[tanfwc]

Is this your own server or your hosting provider? If it is yours, you might want to look at /home/username and see if you have any luck that your previous backup is still there

 

[Infopro]

A.) I went to the mysql folder and it contains two pertinent files I think:
- database_name.create
- database_name.sql (4k size)

This one is the proper directory, but that sql sounds like it contains no data.

How are your backup settings in WHM set up? If you have no access to WHM, you might want to get in touch with your Hosting Provider and find an older backup. If you have an older backup, take a look in that one's mysql directory.

Thanks for the quick response - and on a Saturday!

The Internet never sleeps, and I hate missing anything. :p

 

[dc7]

Thanks everyone for the follow ups.

I use a hosting service and have been working with them on this as well. They've gone through my most recent backup files as well as previous backups in detail (I've been backing up a few times a month since last summer) and have not yet found a SQL backup file with all of my table data in it.

fyi - The only backup process I have ever done is through the cPanel backup wizard interface. I also have backup files that I downloaded to my home computer but I believe they are just copies of the backups that I did through the cPanel backup interface.

Sounds like I should also be doing other backup processes too. Can you offer any suggestions in addition to your WHM idea?
- WHM - can automatic scheduled backups be set up there?
- phpMyAdmin backups?
- exporting tables to csv files?

I'll take another look through all of my past backups and see if I get lucky. Miracles happen!

Thanks again.

 

[dc7]

I did some additional checking - I looked at the sql backups I did for another small wordpress site I have. That database_name.sql file is 1.2MB and contains all of types of table and code data that we didn't see in this one.

So the end result is that I think I set up one of the tables (titles_data) in the database in a really really weird way somehow, which caused only it to backup and excluded the others. I do remember that when I used to go into phpMyAdmin and would see the list of all of the tables, the 'titles_data' table would always have a notation that said "Running" or "In Use" or something like that. None of the other tables showed that. I thought it was curious but didn't know any different.

So I believe the data just never got backed up anywhere and I will have to rebuild manually. The bummer is that it was a robots in the movies and tv shows database site and I was up to 250 individual title pages with custom written plot descriptions, along with cast lists, promo poster pics, video trailers, etc. It was my three semester master project that I made in school and it was going to be my portfolio piece to look for work in Web Development.

Thanks again for your follow ups. My first time posting in a forum anywhere and it was a good experience.

Back to the workshop...

 

[cPanelTristan]

I would suggest taking a mysqldump of all server databases if you have that access. It's a relatively simple command to do so:

mysqldump --add-drop-table --all-databases | gzip > /home/alldatabases$(date +%y%m%d).sql.gz

If the database exists in MySQL, this command will save it in the backup archive in /home/alldatabasesYYMMDD.sql.gz location where YY is the 2 digit year, MM is the 2 digit month, and DD is the 2 digit day. This way, if somehow the cPanel MySQL backup didn't contain the database, there would still be a good chance it was in that archived backup using MySQL directly.

Additionally, you could cron this to run periodically by adding a cron command to /var/spool/cron/root location.