Safely copy a SQLite database

what's diff with using sqlite3 db ".dump";

by buynsell.sg - 3 years ago.

The difference is the file format you end up with:

.dump ?TABLE? ... Dump the database in an SQL text format

As you can see, .dump results in an SQL text format, whereas the method described above will result in an exact copy of the database itself.

by Sybren Stüvel - 3 years ago.

Found the reference to "begin immediate" via google. Thank you! In return, here's a quick python program to do it programatically.

#! /usr/bin/env python

try:

import sqlite3

except ImportError:

from pysqlite2 import dbapi2 as sqlite3

import shutil, optparse, os, time

def sqlite3_backup ( dbfile, backupdir ):

if not os.path.isdir ( backupdir ):

raise Exception ( "backup directory does not exist: %s" % backupdir )

Definition list ends without a blank line; unexpected unindent.

backupfile = os.path.join ( backupdir, os.path.basename(dbfile) + time.strftime(".%Y%m%d-%H%M") ) db = sqlite3.connect ( dbfile ) cur = db.cursor () cur.execute ( 'begin immediate' ) shutil.copyfile ( dbfile, backupfile ) cur.execute ( 'rollback' )

usage = "%prog [options] db-file backup-dir"

description = """Backup sqlite3 database to a directory, appending a datestamp on the backup."""

def get_options ():

parser = optparse.OptionParser ( usage=usage, description=description ) opts, args = parser.parse_args () if len(args) != 2:

Unexpected indentation.

raise parser.error ( "incorrect number of parameters" )

Block quote ends without a blank line; unexpected unindent.

opts.databasefile, opts.backupdir = args return opts

if __name__ == "__main__":

opts = get_options () sqlite3_backup ( opts.databasefile, opts.backupdir )

by Chris Cogdon - 5 months ago.

here's a quick python program to do it programatically. (better indenting now):

  #! /usr/bin/env python
try:
       import sqlite3
except ImportError:
       from pysqlite2 import dbapi2 as sqlite3
import shutil, optparse, os, time
def sqlite3_backup ( dbfile, backupdir ):
       if not os.path.isdir ( backupdir ):
               raise Exception ( "backup directory does not exist: %s" % backupdir )
       backupfile = os.path.join ( backupdir, os.path.basename(dbfile) + time.strftime(".%Y%m%d-%H%M") )
       db = sqlite3.connect ( dbfile )
       cur = db.cursor ()
       cur.execute ( 'begin immediate' )
       shutil.copyfile ( dbfile, backupfile )
       cur.execute ( 'rollback' )
usage = "%prog [options] db-file backup-dir"
description = """\
Backup sqlite3 database to a directory, appending a datestamp
on the backup."""
def get_options ():
       parser = optparse.OptionParser ( usage=usage, description=description )
       opts, args = parser.parse_args ()
       if len(args) != 2:
               raise parser.error ( "incorrect number of parameters" )
       opts.databasefile, opts.backupdir = args
       return opts
if __name__ == "__main__":
       opts = get_options ()
       sqlite3_backup ( opts.databasefile, opts.backupdir )

by Chris Cogdon - 5 months ago.

Thanks Chris!

by Sybren - 4 months, 4 weeks ago.