Hello everybody,

I am new to this forum and SQLite database usage.

After some basic thinking and failed tryouts would like to ask your opinion on an incremental backup solution.

What I would like to do is periodically update the backup database with the recent changes, but only with the changes and not copying or backing up the entire database.

Ultimately this would be written in a .Net framework supported solution, probably using System.Data.SQLite but not necesserily. Did not find an official solution regarding this, however some discussion threads are suggesting some directions, not sure if possible using the .Net library:

1. manually storing and copying page files

   reference: https://stackoverflow.com/questions/29154646/how-can-i-incremental-backup-a-sqlite-database

   Questions:

   a. Is this too much of a workaround or can be a good idea to implement in .Net?

2. working with changesets

   reference: https://www.sqlite.org/sessionintro.html

   Questions:

   a.: In a working scenario does this mean every connection will have it's own changeset and in a multi-user environment on the same database there will be changesets for every user/session and all of those should be merged into the target backup db resolving conflicts ? (Kind of seems hard to force all connections to have changeset generation enabled, or to force all applications retrieving a connection from the library I want to implement and generate changesets before closing the connection)

   b.: Does anybody have an idea why the following happy flow is not working on a simple test database using the assembly from the latest nuget package ?

       using (var conn = new SQLiteConnection("Data Source=C:\\Temp\\Test.db"))
       {
           conn.Open();
           var session = conn.CreateSession("Test");
           session.SetToEnabled();
           session.AttachTable("albums");
   
           var sqlite_cmd = conn.CreateCommand();
           sqlite_cmd.CommandText = "INSERT INTO albums (Title, ArtistId) VALUES('Test', 275); ";
           sqlite_cmd.ExecuteNonQuery();
   
           byte[] changes = new byte[0];
           session.CreateChangeSet(ref changes);
       }
   

3. Thinking on playing with the WAL mechanism and files

   reference: https://www.sqlite.org/wal.html

   a.: Is it possible to make it work disabling the automatic checkpoint mechanism and copying the WAL files to use them on the target backup database before creating the checkpoint on the original database ? (could not find .Net library methods to do this)

Thank you,

Ervin