How to backup sqlite database?

Question

What is the right way to do this? Am I just copying a .sq3 file?

What if there are users on the site and the file is recorded during copying?

Answer 1

The sqlite3 command line tool contains the .backup dot command .

You can connect to your database using

 sqlite3 my_database.sq3 

and run the backup dot command:

 .backup backup_file.sq3 

Instead of interactively connecting to the database, you can also back up and close the connection with

 sqlite3 my_database.sq3 ".backup 'backup_file.sq3'" 

In either case, the result is a copy called backup_file.sq3 the database my_database.sq3 .

It differs from regular file copying because it takes care of any users currently working in the database. The database has the correct locks, so the backup is done exclusively.

Answer 2

For incremental real-time backups (copying only modified pages) you can use litereplica .

It implements unidirectional database replication with support for time recovery, therefore, if a record or table is accidentally deleted, we can restore db (or primary or replica) to some point before the problem.

The backup is updated after each transaction, so if a failure occurs on the host computer, you have an updated backup at a different endpoint.

On the main side, the application will open db using the modified URI, for example:

 "file:/path/to/main.db?replica=master&slave=tcp://my.server.ip:1234" 

And in the replica / backup you should have a running application to receive updates. This application will open a copy of db using a URI as follows:

 "file:/path/to/copy.db?replica=slave&bind=tcp://0.0.0.0:1234" 

To enable time recovery, we need to add some parameters to the URI file name, for example:

 &pitr=on&pitr_limit=200M 

It can be included either in main db, as in backup / replica.

And to enable encryption (database and communication), add something like:

 &cipher=chacha20&key=your_key_here 

Answer 3

.backup is the best way.

 sqlite3 my_database .backup my_database.back 

You can also try the .dump command, it gives you the ability to unload the entire database or table into a text file. If TABLE is specified, only dump tables matching the LIKE TABLE pattern.

 sqlite3 my_database .dump > my_database.back 

Answer 4

 try { final String inFileName = "/data/data/your app package/databases/db"; File dbFile = new File(inFileName); FileInputStream fis = new FileInputStream(dbFile); String path = Environment.getExternalStorageDirectory().getAbsolutePath() + "/CALC/Backup"; File dir = new File(path); if (!dir.exists()) dir.mkdirs(); String outFileName = path + "/filename"; // output file name // Open the empty db as the output stream OutputStream output = new FileOutputStream(outFileName); // Transfer bytes from the inputfile to the outputfile byte[] buffer = new byte[1024]; int length; while ((length = fis.read(buffer)) > 0) { output.write(buffer, 0, length); } Toast.makeText(getActivity(), "Backup Successfully", 2).show(); // Close the streams output.flush(); output.close(); fis.close(); } catch (Exception e) { e.printStackTrace(); }