Help with Database backup


#1

Summary

I accidentally deleted calendars after/while migrating owncloud to nextcloud. I’m trying to recover from a backup, but it’s hard and I might need help.

Background

System

Nextcloud version: 14.0.3
Operating system and version (eg, Ubuntu 17.04): NixOS unstable
Apache or nginx version (eg, Apache 2.4.25): nginx
PHP version (eg, 7.1): 7

Background information

  • I recently upgraded Owncloud 9 -> Nextcloud 9 -> […] Nextcloud 14, and then migrated from Debian to NixOS.
  • I then changed my hostname from www.mydomain.com/owncloud to nextcloud.mydomain.com.
  • The upgrade itself worked great, and I can reach nextcloud fine under the new host.

Backups and tests

  • I didn’t backup and test after each step, I only tested after I finished the whole migration.
  • I still have the backup database from before the migration, when I had Nextcloud 14 on Debian.

Side notes (hopefully irrelevant)

In the beginning I wanted to support the old url with an nginx redirect, but there was a really strange bug in the owncloud desktop client, so I gave up on that eventually. I can explain the details if anyone thinks this is relevant.

The problem

How it happened

  • I had problems syncing my calendars with DavDroid. It wouldn’t sync with the redirect to www.mydomain.com/owncloud in place.
  • After deleting the old DavDroid account and creating a new one, it didn’t recognise the existing calendars
  • To test, I created a new calendar from DavDroid. Possibly, I did something else. Now all my calendars are deleted.

Restoring from Backup

So I had a look at my backups and thought to myself “Basically, just a few rows from the database were dropped and a few were created. Find those, fix it, done.” But the reality looked a bit different.

sqlite3 current_database.db > dump_after_delete_calendar_20181105.sql
sqlite3 backup_database.db > dump.sql

Now comparing those dumps it looks like that they are almost completely different. They contain 80k and 60k lines each, and only share ~100 same lines or so.

Questions

  1. Is it not recommended to simply copy the sqlite database file? (As opposed to dumping to SQL and recreating from SQL)? What could possibly go wrong? (Note, that’s how I migrated the database in the first place.)

  2. Why is the SQL dump of the current database completely different from the dump of the former database?
    a) How would I ever get the correct lines from the backup dump and insert those in the new database?
    b) Is it safe to simply copy the old database and say "upgrade" or "maintenance:repair" or so?


#2

Regarding 1:
I think copying the SQLite3 database should work as long as the paths to the data stay the same. The URL is not stored in the database as far as I know - so even with changing th URL this should work.

Regarding 2:
I don’t know why the 2 databases are different. This shouldn’t be and I just can guess that something went wrong.

Regarding 2a:
You can use my script calcardbackup (it is a bash-Script and from what I read, there is a bash-Package for nixOS) to save your calendars from the old database as *.ics files and import them to a client connected to your nextcloud. The client will then sync you calendars back to your nextcloud. I posted a detailed howto here: Restoring Data from Database Backup

Regarding 2b:
this also might work (See „regarding 1:“)


#3

Importing a backup *.ics worked, thanks!


#4

Glad to hear that you could recover your data with my script :slight_smile: