How to restore a database from a backup (bench backup & bench restore)?

Continuing the discussion from Try Bench Restore?:

can’t confirm this is what actually happens. Getting an Exception: Database [database_name] already exits. instead. So there is at least one step missing in between.

my workflow

  1. cd frappe-bench
  2. bench backup (this creates a new file in /sites/my_site/private/backups)
  3. delete a Sales Invoice in my ERPNext instance
  4. `bench restore \path\to\backupfile.sql.gz
  • do you need to create new empty db first?
  • How would u practically do that?

this older Topic implies you could use a -f option but that does not seem to exist (anymore). So do you have to do that on a new site? If so, do you have to install erpnext to that site manually before restoring the database from the original site?

1 Like

The only workflow that really seems to work for me every time is as follows:

  1. cd frappe-bench
  2. bench backup

(and when ready to restore you first must decompress your backup.sql.gz file)

  1. gunzip yourbackupfile.sql.gz

(this creates the ,sql file from the compressed backup)

  1. sudo bench --force --site your.site.name restore /path.to.your.sql.file

You don’t actually create a blank db, You just force your restore file to overwrite the existing one.

The only thing you have to make sure of with a new installation is that you have passed through the setup wizard on the first login using the Administrator user. Once that is done you can overwrite the database with any valid (and version compatible) erpnext backup.

Hope this helps.

BKM

16 Likes

One very important detail is not mentioned in the backup and restore procedure. Encryption Key
most people don’t have backup of the encryption key only to realize later that it’s needed.

3 Likes

can you elaborate on that? I would guess that this applies only if your database was envrypted to begin with. Proabably that would be the best practice I furthermore assume but I do not think by default this is the case?
You are not talkng about the mysql-root-password, right?

When you do backup and restore in the same server, I guess it’s okay, you might not need an encryption key.

But when you do backup and restore on another different server, encryption key is required in order to decrypt the database. some functions won’t work in ERPNext unless you replace your encryption key with the original one from your previous server.

You can follow up some discussions like this

many people didn’t know even such thing even existed until they encountered an error after restore, and by then it had been too late because the original encryption keys were already lost.

1 Like

thanks, works. Apparentlz the position the position of the --force option was not correct in my last attempt

(and when ready to restore you first must decompress your backup.sqj.gz file)

  1. gunzip yourbackupfile.sql.gz

actually this seems to work even without gunzipping the backupfile upfront. Seems bench restore takes care of the unpacking itself on the .gz file like:

sudo bench --force --site your.site.name restore /path/to/backup.sql.gz

thanks for clarifying.

Is my understanding correct that this comes into play once you have setup an encrypted access (https) to the website you are serving your ERPNext instance to (like bench setup lets-encrypt [your_site]) ?

No not necessary.
I just recently installed SSL on my instances. But even before then, the instances had encryption keys.

You can check on yours on bench/sites/sitename/site_config.json

Also in case you are facing MySQL server gone away errors, use the mysql command for restoring the database

mysql -u root -p [database_name] < [mysql_dump_file]

1 Like

thx. In my current case there is nothing inside the site_config.json which looks like an encryption key.

I completely agree with you that backup & restore of that data needs to be included in a bench backup & bench restore routine. I wouldn’t even expect this to be too difficult to implement. I’d say we need to open an issue on github and a parallel a Topic here in the forum in order to promote that idea and get this fixed.

1 Like

You are correct!! I should have included this as well. The direct MySQL command is also quite helpful if you have a very larger database to restore.

Thank you for adding to the conversation.

BKM

2 Likes

You can try:
First of all you have to copy your database file in frappe bench ,
then try mysql -u Database name -p Password_of_Database Database name < mysql_dump_file

No, HTTPS/TLS is independent from ERPNext. You just point ERPNext to the necessary files to enable it to generate the correct nginx configuration.

After a restore you can just do a fresh set up of Let’s Encrypt without any passwords needed.

1 Like

is this include all custom form and workflow? @bkm

Yes!

All of that is in the database.

BKM

The encryption key is used to encrypt password-related data inside ERPNext documents, e.g. SMTP password, API keys, etc.

Before you’re using such features, the encryption key is not needed (nor generated in vanilla installation of ERPNext).