Restore Database from SQL file into New Installation

Cabji · January 16, 2016, 11:36am

Hi all,

This week the sdcard in my home server failed, and it took my invoiceplanes with it. Fortunately I had backups right to the day of the failure to protect my invoice plane data.

I used automysqlbackup to save my database each day so I have .sql files with all the InvoicePlane data in it.

I have now done a fresh OS install, installed InvoicePlane prerequisites and have just done the initial setup and have the login screen in my browser.

Now how and when can I restore my old IP data back into IP? Do I just send the data straight into the database?

Kovah · January 16, 2016, 11:41am

The restore depends on the system you are using. In PhpMyadmin you have to empty the table first and then import the backup. You may have to upload logos again.

Cabji · January 16, 2016, 12:23pm

Thanks Kovah,

I did the sql import using mysql cli client and it seems to have worked. Everything is back, invoices, clients, products, user accounts. The only problem I have found is when I go to System Settings there is no stylesheets for the page.

This is using v1.4.4

I am going to post in the HOWTO section for how to do the restore.

EDIT: The stylesheet problem was mcrypt php module not being activated (add extension=mcrypt.so to php.ini, restart apache)

I have only found one minor problem with this. In Settings -> Updates the version of InvoicePlane seems to show the version that the tables were created on. Examples, my wife’s IP install was first 1.2.1, so hers shows the current version as 1.2.1. My install show 1.4.3 but I have installed 1.4.4 to both new installations. Could it be because the SQL files tells it to “DROP IF EXISTS” for the tables?

Felix · January 17, 2016, 10:25am

Cabji,

The version problem you are facing might be corrected by running again the /setup to upgrade the database. Try visiting [URL HERE]/setup to see if this procedure resolves the problem.

Cabji · January 19, 2016, 9:10am

Thanks Felix. That fixed up the 1.4.3 installation but the 1.2.1 is giving me this:

	    Error Number: 1060Duplicate column name 'quote_discount_amount'# Discounts
ALTER TABLE `ip_quotes`
ADD COLUMN `quote_discount_amount` DECIMAL(20, 2) NOT NULL DEFAULT 0
AFTER `quote_number`,
ADD COLUMN `quote_discount_percent` DECIMAL(20, 2) NOT NULL DEFAULT 0
AFTER `quote_discount_amount`;Filename: /var/www/html/invoiceplane/modules/setup/models/mdl_setup.phpLine Number: 94```

thesaltmustflow · September 13, 2017, 5:41pm

Hi I am having this same problem where is the location of the data?. I have re-installed invoiceplane but I cannot seem to locate the exact directory where the mysql needs to be imported nor did the OP never created a howto tutorial as mentioned above… what a shame. I do have access to the entire invoiceplane folder which was copied back in 11/2016 of that I need to see what I can extract and utilize. thanks whoever can answer this question

Cabji · September 13, 2017, 8:21pm

hi thesaltmustflow,

If you have done a new install of invoiceplane to restore your data you need a backup or export from the MySQL server the old invoiceplane was using. The invoiceplane folder only holds the data for the user interface and possibly your custom templates.

If you have just done an upgrade, your invoice data should be safe still in the MySQL server.

It is always best to create and hold back ups of your data in numerous locations (not on the machine that holds the main server software).

thesaltmustflow · September 13, 2017, 10:32pm

Hi Cabji, The backup I have is a full copy of the invoiceplane directory. I wish I knew the server was going to die maybe I would hve done something in mysql. I got new drives and a new os loaded. So I am not sure what my chances are in retrieving any previous data I have. I installed invoiceplane on the new os. which means i had to create a whole new sql db because the old one is long gone so with the old invoiceplane folder with sub directories is there anything there that has my data or no?.

Cabji · February 23, 2018, 3:36am

hi thesaltmustflow,

sorry for very late reply, but no - if you don’t have exports or backups taken from the old mysql server then all your data is lost (which you probably realize by now)

my set up i have for backing up now is:

I host invoiceplane on the website of our businesses.
On the website hosts, via a bash script and cron jobs a daily export of the mysql servers are done into a .sql file (can be automatically gzipped as well)
On a computer at home a bash script file is scheduled to run via cron jobs a few minutes after the webhots cron job runs. The home computer automatically pulls the backup files from the webhost using rsync
The home computer uses rsync to create multiple copies across various devices (different computers or different hard drives so if one system fails there are more copies of the data)

If your webhost supports it, you can set it to push the backup files to other locations using rsync, but i found my host doesn’t open ports to do things like that, so I have to use an external computer (computer at home) to pull the backups from the webhost. My host is siteground.com (for anyone googling about this)

In the future I am looking to write into the bash scripts to only save a user defined number of backup files, to not waste space storing loads of backups.