← back to the blog


Backup and Restore from SQLite.Net

Posted on September 2nd, 2013 in .net, c#, sqlite, development by Simon

Without a server process to run maintenance tasks, applications that use SQLite to store data must perform those tasks themselves. Backup and restore is the most commonly needed of these tasks. So I'll cover how I do that here.

Backup

API Support

SQLite.net provides an API method on a SQLiteConnection object to perform the SQLite backup operation.

In order to call this method you need to pass the following parameters:   

  • destination - An open SQLiteConnection for the destination database;   
  • destinationName - "main" to backup to the main database file, "temp" to backup to the temporary database file, or the name specified after the AS keyword in an ATTACH statement for an attached database;   
  • sourceName - "main" to backup from the main database file, "temp" to backup from the temporary database file, or the name specified after the AS keyword in an ATTACH statement for an attached database;   
  • pages - the number of pages on disk to back up with every iteration of the algorithm, -1 will backup the whole database in one iteration;   
  • callback - a function that is called between every iteration, returns true to continue, or false to stop the algorithm;   
  • retryMilliseconds - number of milliseconds to wait before retrying a failed iteration of the algorithm.

Extending with IObservable<T> and IObserver<T>

The following class wraps the iterative algorithm within an IObservable<SqliteBackupEvent> object. The SqliteBackupEvent class just contains the properties returned to the callback:

For completeness the SqliteBackupEvent class should be:

This can be used to update a GUI or some other form of output, such as logging, with the status of the backup operation:   

The use of these classes in your appilcation then becomes something like:

Summary

I hope someone finds this useful, leave a comment if you have a better way of achieving this.