7

I have an application which uses the QSQLITE driver with a QSqlDatabse on a file on the local filesystem. I want to write a backup function which will save a snapshot of the database.

Simply copying the file seems like an obvious, easy way to do it but I'm not sure when it is safe to do so.

The application modifies the database at well-defined points. Each time, a new QSqlQuery object is created, used, and immediately destroyed. Explicitly locking/flushing is an acceptable solution, but the Qt API doesn't seem to expose this.

I can't find any documentation on when Qt commits the database to disk. I imagine the QSqlDatabase destructor would do it, but even then I also don't know if (on Windows or Linux) copying the file is guaranteed to result in the most-recent changes being copied (as opposed to, say, only those changes which have been finalised in the filesystem journal). Can someone confirm or deny this? Does it make any difference if the writing filehandle is closed before the copy is executed?

Maybe the only safe way is to do an online copy but I am already using the Qt API and don't know how this would interact.

Any advice would be appreciated.

Community
  • 1
  • 1
spraff
  • 32,570
  • 22
  • 121
  • 229
  • Qt is simply a user of the sqlite database, you're still in control of what's going on. This is not Qt related at all. Simply refer to sqlite documentation for hints about doing hot backups. – Kuba hasn't forgotten Monica Apr 10 '14 at 02:11
  • Check out https://stackoverflow.com/a/27034900/98057 as well. For people just looking to make a copy (without QT) of a sqlite database, check out this answer instead: https://stackoverflow.com/a/25684912/98057 – André Laszlo Aug 29 '19 at 20:09

2 Answers2

5

It's trivial to copy a SQLite database but it's less trivial to do this in a way that won't corrupt it. This will give you a nice clean backup that's sure to be in a proper state, since writing to the database half-way through your copying process is impossible.

QSqlQuery qry(db);
qry.prepare( "BEGIN IMMEDIATE;");
qry.exec();

QFile::copy(databaseName, destination);

qry.prepare( "ROLLBACK;");
qry.exec();

After a BEGIN IMMEDIATE, no other database connection will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE.

Nejat
  • 31,784
  • 12
  • 106
  • 138
  • Thanks. I guess this will work *assuming* that all prior queries have been written to the file. Can you comment on whether this is guaranteed? – spraff Apr 10 '14 at 10:05
  • I don't know whether all prior queries are written to disk or not. But it will make you sure that no one writes to the database during the copy operation which makes your database consistent. – Nejat Apr 10 '14 at 10:16
  • WARNING: any write operations just fails while BEGIN IMMEDIATE is in progress. SQLite is not waiting for it to finish, it just fails instead. – Alexander Dyagilev Jan 21 '20 at 12:46
4

This has very little to do with Qt. It is database related. This procedure will work with any ACID compliant database, and SQLite is one of these.

From http://www.sqlite.org/transactional.html

SQLite is Transactional

A transactional database is one in which all changes and queries appear to be Atomic, Consistent, Isolated, and Durable (ACID). SQLite implements serializable transactions that are atomic, consistent, isolated, and durable, even if the transaction is interrupted by a program crash, an operating system crash, or a power failure to the computer.

This does not mean you can copy the file and it will be consistent. You should probably use block level snapshots for this before you copy. If you are using Linux, read this,

http://tldp.org/HOWTO/LVM-HOWTO/snapshotintro.html

The procedure would then be,

  1. snapshot
  2. copy DB from snapshot to backup device
  3. remove snapshot volume

Snapshots are global "freeze" of file system, which is consistent because of ACID. File copy is linear operation, which cannot be guaranteed to be consistent without halting all DB operations for duration of copy. This means straight copy is not safe for online databases (in general).

user3427419
  • 1,769
  • 11
  • 15