How to use old erpnext database for new erpnext installation

Dan_Rox · November 21, 2017, 3:32pm

Hi Guys

First I must mentions that I’m new be with erpnext and we decide to move from old server to new ubuntu 16

we manage to install erpnext with out errors working properly and now we need to restore our previous server data dump to the new server. is there any way to do this. we try with cloing data base and configuring site1.json and it didn’t work I must be doing it wrong so could you guys guide me how to do it

Thnaks

khushal_t · November 21, 2017, 3:38pm

@Dan_Rox I guess you need to migrate data from old server to new one, if yes then just get SQL file by bench backup on old server and restore that database to new one

Dan_Rox · November 21, 2017, 4:42pm

Thanks for quick reply mate @khushal_t . I just curious is their any way to do it with sql file and direct inject them to new data base

khushal_t · November 21, 2017, 4:57pm

@Dan_Rox Restore will solve your problem, for curiosity you may explore :

James_Robertson · November 21, 2017, 11:34pm

Are the environments the same on the old and new server? What version of ERPNext is running at each place? If they are the same then a bench backup --with-files will work fine. The scp the files from the old server to the new and then do a bench --force restore command.

I have some notes in the admin guide project for backup and restore

github.com

jwrober/erpnext_admin_guide/blob/master/i-u-b/backup.md

Home: [Table of Contents](../ "Table of Contents") | Previous: [3.3.4 Upgrade Troubleshooting](upgrade-trouble "Upgrade Troubleshooting") | Next: [3.5 Restoring from a Previous Backup](restore "Restoring from a Previous Backup")

## 3.4 Backing Up ERPNext

Backing up ERPNext is pretty simple. There are two kinds of backups from the admin guide's perspective. A simple backup and a full backup. Let me explain.

<a name="Simple">&nbsp;</a>
### 3.4.1 Simple Backup

A simple backup just backs up the database and files into `tar` files.  To accomplish this, simply run `bench backup` like this:

    sudo su - erpnext
    cd [bench name]
    bench backup --with files

`bench backup` will create a `tar.gz` file of the database and `tar` files of the public and private files in the `sites/[site name]/private/backups/` directory on the server. For example:

    [yyyymmdd]_[hhmmss]-[site name]-database.sql.gz
    [yyyymmdd]_[hhmmss]-[site name]-files.tar
    [yyyymmdd]_[hhmmss]-[site name]-private-files.tar

This file has been truncated. show original

github.com

jwrober/erpnext_admin_guide/blob/master/i-u-b/restore.md

Home: [Table of Contents](../ "Table of Contents") | Previous: [3.4 Backing Up ERPNext](backup "Backing Up ERPNext") | Next: [4 Setup](../setup/setup "Setup")

## 3.5 Restoring from a Previous Backup

Assuming you have a good [simple backup](backup#Simple "Backing Up ERPNext"), you can use these commands to restore the database and files. These commands also assume that you are not [reverting](revert "Reverting to an Older Version") back to a different code base.

**NOTE**: For some reason the admin guide does not understand, `bench` does not handle relative paths very well. Be sure to fully qualify all of the path's you use for the `bench restore` command.

    sudo su - erpnext
    cd [bench name]
    bench --force restore \
      --with-public-files /home/erpnext/[bench name]/sites/[site name]/private/backups/[files.tar] \
      --with-private-files /home/erpnext/[bench name]/sites/[site name]/private/backups/[private-files.tar] \
      /home/erpnext/[bench name]/sites/[site name]/private/backups/[sql.gz file]

This should bring your system's database and files configuration back to the point in time the backup was taken.<br /><br />

Home: [Table of Contents](../ "Table of Contents") | Previous: [3.4 Backing Up ERPNext](backup "Backing Up ERPNext") | Next: [4 Setup](../setup/setup "Setup")

James_Robertson · November 21, 2017, 11:35pm

Forgot to mention, if the installation is different between servers, then after the restore you will want to do a bench migrate to bring the db up to spec with the code.

Dan_Rox · November 22, 2017, 7:51am

Thank you for the well detail response mate @James_Robertson

unfortunately currently I have only the sql file with full database of previous server

I try some think like this and end up with this error

first I create new site with my sql file using

bench new-site [sitename] --force --source_sql /path/to/file.sql

then I Run bench Update command

and now I’m stuck here

pymysql.err.ProgrammingError: (1146, u"Table '76cf18db07302d32.tabTimesheet Detail' doesn't exist")

Note : I checked my data base and it does not contain such table with this name

exiting table is tabTime Sheet Details"

Thanks

James_Robertson · November 22, 2017, 3:49pm

Hm. what version of erpnext is the sql file for? How out of date it is to current?

I would create a new empty site with bench --site [sitename] --force reinstall and then do a restore from the sql file you have with bench --force restore [path to sql file]. Once done to a bench migrate to bring the database up to current. I would finish off with:

bench clear-cache
bench clear-website-cache
bench restart

You should not need to do a bench update because you have installed a fresh copy of the code on the server.

Dan_Rox · November 22, 2017, 5:18pm

Thank you @James_Robertson @khushal_t

Still same error

new erpnext version we installed is this
bench --version
4.1.0

and we using databata backup from Bitnami ERPNext I don’t know the version ,

FYI this is the full error log after restore sql file and run migrate

 Executing frappe.patches.v9_1.move_feed_to_activity_log in nrpnext.local (07f7585cbe03d330)`
 Traceback (most recent call last):
   File "/usr/lib/python2.7/runpy.py", line 174, in _run_module_as_main
 "__main__", fname, loader, pkg_name)
   File "/usr/lib/python2.7/runpy.py", line 72, in _run_code
 exec code in run_globals
   File "/home/erpnext/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 94, in <module>
 main()
   File "/home/erpnext/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 18, in main
 click.Group(commands=commands)(prog_name='bench')
   File "/home/erpnext/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py", line 722, in __call__
 return self.main(*args, **kwargs)
   File "/home/erpnext/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py", line 697, in main
 rv = self.invoke(ctx)
   File "/home/erpnext/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py", line 1066, in invoke
 return _process_result(sub_ctx.command.invoke(sub_ctx))
   File "/home/erpnext/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py", line 1066, in invoke
 return _process_result(sub_ctx.command.invoke(sub_ctx))
   File "/home/erpnext/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py", line 895, in invoke
 return ctx.invoke(self.callback, **ctx.params)
   File "/home/erpnext/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py", line 535, in invoke
 return callback(*args, **kwargs)
   File "/home/erpnext/frappe-bench/env/local/lib/python2.7/site-packages/click/decorators.py", line 17, in new_func
 return f(get_current_context(), *args, **kwargs)
   File "/home/erpnext/frappe-bench/apps/frappe/frappe/commands/__init__.py", line 24, in _func
 ret = f(frappe._dict(ctx.obj), *args, **kwargs)
   File "/home/erpnext/frappe-bench/apps/frappe/frappe/commands/site.py", line 222, in migrate
 migrate(context.verbose, rebuild_website=rebuild_website)
   File "/home/erpnext/frappe-bench/apps/frappe/frappe/migrate.py", line 31, in migrate
 frappe.modules.patch_handler.run_all()
   File "/home/erpnext/frappe-bench/apps/frappe/frappe/modules/patch_handler.py", line 29, in run_all
 if not run_single(patchmodule = patch):
   File "/home/erpnext/frappe-bench/apps/frappe/frappe/modules/patch_handler.py", line 63, in run_single
 return execute_patch(patchmodule, method, methodargs)
   File "/home/erpnext/frappe-bench/apps/frappe/frappe/modules/patch_handler.py", line 83, in execute_patch
 frappe.get_attr(patchmodule.split()[0] + ".execute")()
   File "/home/erpnext/frappe-bench/apps/frappe/frappe/patches/v9_1/move_feed_to_activity_log.py", line 27, in execute
 activity_doc.insert()
   File "/home/erpnext/frappe-bench/apps/frappe/frappe/model/document.py", line 218, in insert
 self.run_before_save_methods()
   File "/home/erpnext/frappe-bench/apps/frappe/frappe/model/document.py", line 859, in run_before_save_methods
 self.run_method("validate")
   File "/home/erpnext/frappe-bench/apps/frappe/frappe/model/document.py", line 755, in run_method
 out = Document.hook(fn)(self, *args, **kwargs)
   File "/home/erpnext/frappe-bench/apps/frappe/frappe/model/document.py", line 1024, in composer
 return composed(self, method, *args, **kwargs)
   File "/home/erpnext/frappe-bench/apps/frappe/frappe/model/document.py", line 1007, in runner
 add_to_return_value(self, fn(self, *args, **kwargs))
   File "/home/erpnext/frappe-bench/apps/frappe/frappe/model/document.py", line 749, in <lambda>
 fn = lambda self, *args, **kwargs: getattr(self, method)(*args, **kwargs)
   File "/home/erpnext/frappe-bench/apps/frappe/frappe/core/doctype/activity_log/activity_log.py", line 19, in validate
 set_timeline_doc(self)
   File "/home/erpnext/frappe-bench/apps/frappe/frappe/core/utils.py", line 18, in set_timeline_doc
 parent_doc = get_parent_doc(doc)
   File "/home/erpnext/frappe-bench/apps/frappe/frappe/core/utils.py", line 11, in get_parent_doc
 doc.parent_doc = frappe.get_doc(doc.reference_doctype, doc.reference_name)
   File "/home/erpnext/frappe-bench/apps/frappe/frappe/__init__.py", line 638, in get_doc
 return frappe.model.document.get_doc(*args, **kwargs)
   File "/home/erpnext/frappe-bench/apps/frappe/frappe/model/document.py", line 68, in get_doc
 return controller(*args, **kwargs)
   File "/home/erpnext/frappe-bench/apps/frappe/frappe/model/document.py", line 103, in __init__
 self.load_from_db()
   File "/home/erpnext/frappe-bench/apps/frappe/frappe/model/document.py", line 142, in load_from_db
 super(Document, self).__init__(d)
   File "/home/erpnext/frappe-bench/apps/frappe/frappe/model/base_document.py", line 58, in __init__
 self.__setup__()
   File "/home/erpnext/frappe-bench/apps/erpnext/erpnext/projects/doctype/project/project.py", line 29, in __setup__
 self.onload()
   File "/home/erpnext/frappe-bench/apps/erpnext/erpnext/projects/doctype/project/project.py", line 26, in onload
 order by total_hours desc''', self.name, as_dict=True))
   File "/home/erpnext/frappe-bench/apps/frappe/frappe/database.py", line 166, in sql
 self._cursor.execute(query, values)
   File "/home/erpnext/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 166, in execute
 result = self._query(query)
   File "/home/erpnext/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 322, in _query
 conn.query(q)
   File "/home/erpnext/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 856, in query
 self._affected_rows = self._read_query_result(unbuffered=unbuffered)
   File "/home/erpnext/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1057, in _read_query_result
 result.read()
   File "/home/erpnext/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1340, in read
 first_packet = self.connection._read_packet()
   File "/home/erpnext/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1014, in _read_packet
 packet.check_error()
   File "/home/erpnext/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 393, in check_error
 err.raise_mysql_exception(self._data)
   File "/home/erpnext/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
 raise errorclass(errno, errval)
 pymysql.err.ProgrammingError: (1146, u"Table '07f7585cbe03d330.tabTimesheet Detail' doesn't exist")

Thank you
Darshana

khushal_t · November 23, 2017, 8:05am

@Dan_Rox Did you install-erpnext manually or after creating site you just restored the database?

James_Robertson · November 23, 2017, 3:03pm

So you have a table named tabTime Sheet Details? Here is the SQL to create the correct table:

--
-- Table structure for table `tabTimesheet Detail`
--

DROP TABLE IF EXISTS `tabTimesheet Detail`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tabTimesheet Detail` (
  `name` varchar(140) COLLATE utf8mb4_unicode_ci NOT NULL,
  `creation` datetime(6) DEFAULT NULL,
  `modified` datetime(6) DEFAULT NULL,
  `modified_by` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `owner` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `docstatus` int(1) NOT NULL DEFAULT '0',
  `parent` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `parentfield` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `parenttype` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `idx` int(8) NOT NULL DEFAULT '0',
  `project` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `workstation` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `task` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `costing_rate` decimal(18,6) NOT NULL DEFAULT '0.000000',
  `billing_amount` decimal(18,6) NOT NULL DEFAULT '0.000000',
  `hours` decimal(18,6) NOT NULL DEFAULT '0.000000',
  `billing_rate` decimal(18,6) NOT NULL DEFAULT '0.000000',
  `completed_qty` decimal(18,6) NOT NULL DEFAULT '0.000000',
  `to_time` datetime(6) DEFAULT NULL,
  `billable` int(1) NOT NULL DEFAULT '0',
  `operation_id` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `operation` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `from_time` datetime(6) DEFAULT NULL,
  `costing_amount` decimal(18,6) NOT NULL DEFAULT '0.000000',
  `activity_type` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `billing_hours` decimal(18,6) NOT NULL DEFAULT '0.000000',
  `sales_invoice` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`name`),
  KEY `parent` (`parent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED;
/*!40101 SET character_set_client = @saved_cs_client */;

If found this by looking at a current backup sql file from my own prod instance. What I would suggest you do is to look at your backup file and find the table and ensure that the code to create the table is correct. If it is close (meaning that there might be missing columns), then I recommend you rename the table after the restore and then run bench migrate again.

https://blog.marceloaltmann.com/en-how-to-rename-table-in-mysql-pt-como-renomear-tabelas-no-mysql/

Good luck

ijpulidos · May 7, 2018, 4:51pm

I’m having a similar problem when I try to restore a database from an old ERPNext (ERPNext: v7.0.44 Frappe Framework: v7.0.32) instance to a brand latest new one. The output I’m getting when doing the restore is the following:

Traceback (most recent call last):
  File "/usr/lib/python2.7/runpy.py", line 174, in _run_module_as_main
    "__main__", fname, loader, pkg_name)
  File "/usr/lib/python2.7/runpy.py", line 72, in _run_code
    exec code in run_globals
  File "/home/erpnext/erpnext-prd/apps/frappe/frappe/utils/bench_helper.py", line 94, in <module>
    main()
  File "/home/erpnext/erpnext-prd/apps/frappe/frappe/utils/bench_helper.py", line 18, in main
    click.Group(commands=commands)(prog_name='bench')
  File "/home/erpnext/erpnext-prd/env/local/lib/python2.7/site-packages/click/core.py", line 722, in __call__
    return self.main(*args, **kwargs)
  File "/home/erpnext/erpnext-prd/env/local/lib/python2.7/site-packages/click/core.py", line 697, in main
    rv = self.invoke(ctx)
  File "/home/erpnext/erpnext-prd/env/local/lib/python2.7/site-packages/click/core.py", line 1066, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/erpnext/erpnext-prd/env/local/lib/python2.7/site-packages/click/core.py", line 1066, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/erpnext/erpnext-prd/env/local/lib/python2.7/site-packages/click/core.py", line 895, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/erpnext/erpnext-prd/env/local/lib/python2.7/site-packages/click/core.py", line 535, in invoke
    return callback(*args, **kwargs)
  File "/home/erpnext/erpnext-prd/env/local/lib/python2.7/site-packages/click/decorators.py", line 17, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/home/erpnext/erpnext-prd/apps/frappe/frappe/commands/__init__.py", line 24, in _func
    ret = f(frappe._dict(ctx.obj), *args, **kwargs)
  File "/home/erpnext/erpnext-prd/apps/frappe/frappe/commands/site.py", line 115, in restore
    force=context.force)
  File "/home/erpnext/erpnext-prd/apps/frappe/frappe/commands/site.py", line 71, in _new_site
    frappe.utils.scheduler.toggle_scheduler(enable_scheduler)
  File "/home/erpnext/erpnext-prd/apps/frappe/frappe/utils/scheduler.py", line 230, in toggle_scheduler
    frappe.db.set_value("System Settings", None, "enable_scheduler", 1 if enable else 0)
  File "/home/erpnext/erpnext-prd/apps/frappe/frappe/database.py", line 653, in set_value
    modified = now()
  File "/home/erpnext/erpnext-prd/apps/frappe/frappe/utils/data.py", line 154, in now
    return now_datetime().strftime(DATETIME_FORMAT)
  File "/home/erpnext/erpnext-prd/apps/frappe/frappe/utils/data.py", line 121, in now_datetime
    dt = convert_utc_to_user_timezone(datetime.datetime.utcnow())
  File "/home/erpnext/erpnext-prd/apps/frappe/frappe/utils/data.py", line 144, in convert_utc_to_user_timezone
    return utcnow.astimezone(timezone(get_time_zone()))
  File "/home/erpnext/erpnext-prd/apps/frappe/frappe/utils/data.py", line 138, in get_time_zone
    return frappe.cache().get_value("time_zone", _get_time_zone)
  File "/home/erpnext/erpnext-prd/apps/frappe/frappe/utils/redis_wrapper.py", line 72, in get_value
    val = generator()
  File "/home/erpnext/erpnext-prd/apps/frappe/frappe/utils/data.py", line 132, in _get_time_zone
    return frappe.db.get_system_setting('time_zone') or 'Asia/Kolkata'
  File "/home/erpnext/erpnext-prd/apps/frappe/frappe/database.py", line 889, in get_system_setting
    return frappe.cache().get_value("system_settings", _load_system_settings).get(key)
  File "/home/erpnext/erpnext-prd/apps/frappe/frappe/utils/redis_wrapper.py", line 72, in get_value
    val = generator()
  File "/home/erpnext/erpnext-prd/apps/frappe/frappe/database.py", line 888, in _load_system_settings
    return self.get_singles_dict("System Settings")
  File "/home/erpnext/erpnext-prd/apps/frappe/frappe/database.py", line 559, in get_singles_dict
    tabSingles where doctype=%s""", doctype))
  File "/home/erpnext/erpnext-prd/apps/frappe/frappe/database.py", line 166, in sql
    self._cursor.execute(query, values)
  File "/home/erpnext/erpnext-prd/env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/home/erpnext/erpnext-prd/env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/home/erpnext/erpnext-prd/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 893, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/erpnext/erpnext-prd/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1103, in _read_query_result
    result.read()
  File "/home/erpnext/erpnext-prd/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1396, in read
    first_packet = self.connection._read_packet()
  File "/home/erpnext/erpnext-prd/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1059, in _read_packet
    packet.check_error()
  File "/home/erpnext/erpnext-prd/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 384, in check_error
    err.raise_mysql_exception(self._data)
  File "/home/erpnext/erpnext-prd/env/local/lib/python2.7/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1146, u"Table '1bd3e0294da19198.tabSingles' doesn't exist")

And effectively I don’t have a tabSingles table in my backup db. Any ideas how should I solve this or what’s the mysql code to create it? Thanks for the feedback.

saurabh6790 · May 9, 2018, 2:45am

Seems backup is broken.