How to do the online backup by executing a specific SQL ?

(1) By anonymous on 2022-02-14 03:28:24 [link] [source]

I'd like to unify all the application function like (CRUD) to use SQL. However, I found the online backup API needs to call specific C-API. 
If there was a specific SQL for example "BACKUP [file/or URI]" and "RESTORE [file/or UIR]",  it would be better for those applications that does not want to interact with C code.
Is it possible ?

(2) By Keith Medcalf (kmedcalf) on 2022-02-14 04:30:53 in reply to 1 [link] [source]

There is the existing VACUUM INTO command. https://sqlite.org/lang_vacuum.html

Though this does more than just backup the database ...

(3) By anonymous on 2022-02-14 07:06:56 in reply to 2 [link] [source]

Yes, but it seems just doing the backup. How to restore the database ?

(4) By Gunter Hick (gunter_hick) on 2022-02-14 07:47:22 in reply to 3 [source]

Use the OS shell to replace the SQLite database file with a backup you created earlier while making sure that no application is acessing it.

Or change the direction of the VACUUM INTO process by attaching the backup you created earlier and VACUUM INTO the original file.

(5) By anonymous on 2022-02-14 09:06:47 in reply to 4 [link] [source]

That's a good idea!.

BTW, Is the following steps correct to restore a memory db from a file ?

Open the file db
Attach a memory db.
execute "VACUUM INTO :memory:"
Detach the file db.

(6) By Gunter Hick (gunter_hick) on 2022-02-14 10:39:42 in reply to 5 [link] [source]

I am quite sure that will not be a simple as you imagine. It might work using an URI filename to specify a named memory database but you would have to try that.