Microsoft.Data.Sqlite: BackupDatabase() should yield

[bricelam]

Currently we call sqlite3_backup_step(backup, -1), but this will lock the database for the duration of the copy.

One of the main purposes of this API is to allow users to continue uninterrupted while a backup of the database is made.

To allow the backup to be made on a background thread while giving other threads opportunities to read, we should update it to step one page at a time and yield in between.

Unfortunately, this opens up the possibility of getting SQLITE_BUSY when stepping. This means we'll need to retry. The backup can even be automatically restarted. Which means it could take a very long time to complete. We'll need to think about timeouts and cancellation when we do this.

[bricelam]

What we currently have is useful when you want the copy done as quickly as possible. This is useful on UWP to export the database, or to save/load an in-memory database. But I don't think these scenarios would be degraded by yielding since there typically isn't concurrent access with them.

[bricelam]

Triage: We decided to keep the existing method as is (no yielding), but to add additional methods/overloads for timeout, cancellation, and progress that yield.

[0xced]

Official SQLite documentation for reference: Using the SQLite Online Backup API.

[0xced]

I’m working on it, I hope to submit a pull request next week.

[0xced]

Haha, make it next year. 🤣

[tyronx]

This would be extremely useful to have. I wrote game server software for my own game where we have very large saves. Currently its not possible to back them up efficiently while the server is running.

As a workaround I will try to run it in a separate thread for now

[MaxDZ8]

The company I work for deploys edge servers. Data is limited but so is CPU and IO performance. Our QoS make blocking unbounded operations a bit concerning.

However, I do realize the situation is particularly delicate so take all the time you need to study the issue.

I would myself love to try mashing something but I doubt the company would allocate schedule.

[urza]

Hello, was there any progress on this issue?

If we could have what is described here as Example 2: Online Backup of a Running Database that would be extremely useful.

[ajcvickers]

@urza This issue is in the Backlog milestone. This means that it is not planned for the next release (7.0). We will re-assess the backlog following the this release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources. Make sure to vote (👍) for this issue if it is important to you.

[roji]

Everyone, please upvote (👍) the top-issue above, and refrain from posting more "me too" comments. We do look at the number of votes when deciding what to work on, but this issue currently has only 24 votes, making it quite low on the priority list.

[faridmzd]

hey, currently i'm working on .net maui blazor hybrid, im using sqlite and ef core. My problem is after online backup backup file remains open even though i tried every approach to close it

async Task BackUpLocalDb(AppDBContext dBContext)
{
    using (var source = new SqliteConnection(dBContext.Database.GetConnectionString()))
    using (var destination = new SqliteConnection("Data Source=C:\\Users\\farid\\source\\repos\\Swietlica\\WhoIsHere\\BackupDb\\SwietlicaDatabaseBackup.db"))
    {
        //try
        //{

        await source!.OpenAsync();
        await destination.OpenAsync();
        source.BackupDatabase(destination);
        //}
        //finally
        //{
        //    // Ensure closure and disposal even if exceptions occurs
        //    await source.CloseAsync();
        //    await sourceAsSqlite.CloseAsync();// Or if unavailable, use using statement
        //    await destination.CloseAsync(); // Or if unavailable, use using statement
        //    await source.DisposeAsync();
        //    await sourceAsSqlite.DisposeAsync();
        //    await destination.DisposeAsync();
        //}
    }
        var stream = File.Open("C:\\Users\\farid\\source\\repos\\Swietlica\\WhoIsHere\\BackupDb\\SwietlicaDatabaseBackup.db", FileMode.Open);
}

[oliome]

hey, currently i'm working on .net maui blazor hybrid, im using sqlite and ef core. My problem is after online backup backup file remains open even though i tried every approach to close it

async Task BackUpLocalDb(AppDBContext dBContext) { using (var source = new SqliteConnection(dBContext.Database.GetConnectionString())) using (var destination = new SqliteConnection("Data Source=C:\Users\farid\source\repos\Swietlica\WhoIsHere\BackupDb\SwietlicaDatabaseBackup.db")) { //try //{

    await source!.OpenAsync();
    await destination.OpenAsync();
    source.BackupDatabase(destination);
    //}
    //finally
    //{
    //    // Ensure closure and disposal even if exceptions occurs
    //    await source.CloseAsync();
    //    await sourceAsSqlite.CloseAsync();// Or if unavailable, use using statement
    //    await destination.CloseAsync(); // Or if unavailable, use using statement
    //    await source.DisposeAsync();
    //    await sourceAsSqlite.DisposeAsync();
    //    await destination.DisposeAsync();
    //}
}
    var stream = File.Open("C:\\Users\\farid\\source\\repos\\Swietlica\\WhoIsHere\\BackupDb\\SwietlicaDatabaseBackup.db", FileMode.Open);

}

I would try SqliteConnection.ClearAllPools().

[benjaminvanrenterghem]

hey, currently i'm working on .net maui blazor hybrid, im using sqlite and ef core. My problem is after online backup backup file remains open even though i tried every approach to close it

async Task BackUpLocalDb(AppDBContext dBContext)
{
    using (var source = new SqliteConnection(dBContext.Database.GetConnectionString()))
    using (var destination = new SqliteConnection("Data Source=C:\\Users\\farid\\source\\repos\\Swietlica\\WhoIsHere\\BackupDb\\SwietlicaDatabaseBackup.db"))
    {
        //try
        //{

        await source!.OpenAsync();
        await destination.OpenAsync();
        source.BackupDatabase(destination);
        //}
        //finally
        //{
        //    // Ensure closure and disposal even if exceptions occurs
        //    await source.CloseAsync();
        //    await sourceAsSqlite.CloseAsync();// Or if unavailable, use using statement
        //    await destination.CloseAsync(); // Or if unavailable, use using statement
        //    await source.DisposeAsync();
        //    await sourceAsSqlite.DisposeAsync();
        //    await destination.DisposeAsync();
        //}
    }
        var stream = File.Open("C:\\Users\\farid\\source\\repos\\Swietlica\\WhoIsHere\\BackupDb\\SwietlicaDatabaseBackup.db", FileMode.Open);
}

"using (var source = new SqliteConnection(dBContext.Database.GetConnectionString()))" using statement is still active

[bricelam]

@benjaminvanrenterghem If you add Pooling=False to the connection string, does it close?