Re: [sqlite] Backing up a SQLite database without the CLI

[Bohwaz/Fossil]

If you're trying to copy a file while connections still have it open
then you should use SQLite API calls to do it.  The obvious ones are
in the SQLite Online Backup API, which is the set of calls underlying
the '.backup' command you mentioned.  You can find documentation for
this here:



Unfortunately I don't think the PHP sqlite3 tools give access to this 
API.


Not yet, but I did a patch and there is a pending pull request for that: 
https://github.com/php/php-src/pull/3617


It does work, I'm using it, and you could too if you are willing to 
compile your own PHP extension.


Hopefully it will land in PHP 7.4 :)

Cheers.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Backing up a SQLite database without the CLI

[Scott Perry]

`ATTACH` and iterating over all tables with `INSERT INTO SELECT` is how `VACUUM 
INTO` is implemented (see src/vacuum.c).

A less complicated way to back up the database might be to run `BEGIN` followed 
by `PRAGMA user_version` to acquire a read lock, after which you can safely 
copy the database (and wal, if appropriate) files directly.

Scott

On Mar 18, 2019, at 08:21, Jonathan Moules  wrote:
> 
> Hi Simon,
> 
> Thanks for your thoughts. Sorry, I should have been clearer: I have no way of 
> knowing if there are other open connections to the file - there may be as 
> it's a web-application. So I'll assume there are connections.
> 
> At this point I'm starting to think that the best option is to create a new 
> database with the requisite structure and copy the data across via an ATTACH 
> (there are only two tables and one will almost always be empty at this point).
> 
> Any other thoughts welcome though!
> Cheers,
> Jonathan
> 
> On 2019-03-18 13:37, Simon Slavin wrote:
>> On 18 Mar 2019, at 1:10pm, Jonathan Moules  
>> wrote:
>> 
>>> I was wondering if there was a good way of backing up an SQLite database if 
>>> you do *not* have access to the SQLite command line tool (which I know has 
>>> .backup - https://stackoverflow.com/a/25684912). [snip]
>>> I've considered simply running "PRAGMA wal_checkpointer;" and then copying 
>>> the file immediately after that, but that still seems prone to error.
>> Ideally, rather than force a WAL checkpoint, close the file, make the copy, 
>> then open it again.  This does not take significantly more time, and it 
>> ensures that you will copy the right thing no matter what caching and 
>> optimization your tools are trying to do.
>> 
>> In more general terms ...
>> 
>> Are you trying to backup while the database is being modified using SQLite 
>> function calls ?
>> 
>> If not, then the data is just a single file.  Assuming all programs using 
>> SQLite calls closed their connections properly, just copy the file using any 
>> file copy commands, or file copy primatives in your favourite programming 
>> language.  In PHP I'd use the built-in copy command:
>> 
>> 
>> 
>> There may be a journal file there and you can copy that too, but just the 
>> database file is enough for a backup for emergency purposes.
>> 
>> If you're trying to copy a file while connections still have it open then 
>> you should use SQLite API calls to do it.  The obvious ones are in the 
>> SQLite Online Backup API, which is the set of calls underlying the '.backup' 
>> command you mentioned.  You can find documentation for this here:
>> 
>> 
>> 
>> Unfortunately I don't think the PHP sqlite3 tools give access to this API.
>> 
>> Hope that helps.  Don't hesitate to get back to us if we can help.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Backing up a SQLite database without the CLI

[Simon Slavin]

On 18 Mar 2019, at 3:21pm, Jonathan Moules  wrote:

> At this point I'm starting to think that the best option is to create a new 
> database with the requisite structure and copy the data across via an ATTACH 
> (there are only two tables and one will almost always be empty at this point).

That could work well.  Create the new database and set

PRAGMA journal_mode = OFF

then close it, attach it to your main database and use the 

INSERT INTO table SELECT ...

syntax.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Backing up a SQLite database without the CLI

[Shawn Wagner]

If the php sqlite bindings are incomplete and don't support the backup
functions, write a small program in C that uses them to copy a database,
and execute that from the php code?

On Mon, Mar 18, 2019, 8:24 AM Jonathan Moules 
wrote:

> Hi Simon,
>
> Thanks for your thoughts. Sorry, I should have been clearer: I have no
> way of knowing if there are other open connections to the file - there
> may be as it's a web-application. So I'll assume there are connections.
>
> At this point I'm starting to think that the best option is to create a
> new database with the requisite structure and copy the data across via
> an ATTACH (there are only two tables and one will almost always be empty
> at this point).
>
> Any other thoughts welcome though!
> Cheers,
> Jonathan
>
> On 2019-03-18 13:37, Simon Slavin wrote:
> > On 18 Mar 2019, at 1:10pm, Jonathan Moules 
> wrote:
> >
> >> I was wondering if there was a good way of backing up an SQLite
> database if you do *not* have access to the SQLite command line tool (which
> I know has .backup - https://stackoverflow.com/a/25684912). [snip]
> >> I've considered simply running "PRAGMA wal_checkpointer;" and then
> copying the file immediately after that, but that still seems prone to
> error.
> > Ideally, rather than force a WAL checkpoint, close the file, make the
> copy, then open it again.  This does not take significantly more time, and
> it ensures that you will copy the right thing no matter what caching and
> optimization your tools are trying to do.
> >
> > In more general terms ...
> >
> > Are you trying to backup while the database is being modified using
> SQLite function calls ?
> >
> > If not, then the data is just a single file.  Assuming all programs
> using SQLite calls closed their connections properly, just copy the file
> using any file copy commands, or file copy primatives in your favourite
> programming language.  In PHP I'd use the built-in copy command:
> >
> > 
> >
> > There may be a journal file there and you can copy that too, but just
> the database file is enough for a backup for emergency purposes.
> >
> > If you're trying to copy a file while connections still have it open
> then you should use SQLite API calls to do it.  The obvious ones are in the
> SQLite Online Backup API, which is the set of calls underlying the
> '.backup' command you mentioned.  You can find documentation for this here:
> >
> > 
> >
> > Unfortunately I don't think the PHP sqlite3 tools give access to this
> API.
> >
> > Hope that helps.  Don't hesitate to get back to us if we can help.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Backing up a SQLite database without the CLI

[Jonathan Moules]

Hi Simon,

Thanks for your thoughts. Sorry, I should have been clearer: I have no 
way of knowing if there are other open connections to the file - there 
may be as it's a web-application. So I'll assume there are connections.


At this point I'm starting to think that the best option is to create a 
new database with the requisite structure and copy the data across via 
an ATTACH (there are only two tables and one will almost always be empty 
at this point).


Any other thoughts welcome though!
Cheers,
Jonathan

On 2019-03-18 13:37, Simon Slavin wrote:

On 18 Mar 2019, at 1:10pm, Jonathan Moules  wrote:


I was wondering if there was a good way of backing up an SQLite database if you 
do *not* have access to the SQLite command line tool (which I know has .backup 
- https://stackoverflow.com/a/25684912). [snip]
I've considered simply running "PRAGMA wal_checkpointer;" and then copying the 
file immediately after that, but that still seems prone to error.

Ideally, rather than force a WAL checkpoint, close the file, make the copy, 
then open it again.  This does not take significantly more time, and it ensures 
that you will copy the right thing no matter what caching and optimization your 
tools are trying to do.

In more general terms ...

Are you trying to backup while the database is being modified using SQLite 
function calls ?

If not, then the data is just a single file.  Assuming all programs using 
SQLite calls closed their connections properly, just copy the file using any 
file copy commands, or file copy primatives in your favourite programming 
language.  In PHP I'd use the built-in copy command:



There may be a journal file there and you can copy that too, but just the 
database file is enough for a backup for emergency purposes.

If you're trying to copy a file while connections still have it open then you 
should use SQLite API calls to do it.  The obvious ones are in the SQLite 
Online Backup API, which is the set of calls underlying the '.backup' command 
you mentioned.  You can find documentation for this here:



Unfortunately I don't think the PHP sqlite3 tools give access to this API.

Hope that helps.  Don't hesitate to get back to us if we can help.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Backing up a SQLite database without the CLI

[Simon Slavin]

On 18 Mar 2019, at 1:10pm, Jonathan Moules  wrote:

> I was wondering if there was a good way of backing up an SQLite database if 
> you do *not* have access to the SQLite command line tool (which I know has 
> .backup - https://stackoverflow.com/a/25684912). [snip]

> I've considered simply running "PRAGMA wal_checkpointer;" and then copying 
> the file immediately after that, but that still seems prone to error.

Ideally, rather than force a WAL checkpoint, close the file, make the copy, 
then open it again.  This does not take significantly more time, and it ensures 
that you will copy the right thing no matter what caching and optimization your 
tools are trying to do.

In more general terms ...

Are you trying to backup while the database is being modified using SQLite 
function calls ?

If not, then the data is just a single file.  Assuming all programs using 
SQLite calls closed their connections properly, just copy the file using any 
file copy commands, or file copy primatives in your favourite programming 
language.  In PHP I'd use the built-in copy command:



There may be a journal file there and you can copy that too, but just the 
database file is enough for a backup for emergency purposes.

If you're trying to copy a file while connections still have it open then you 
should use SQLite API calls to do it.  The obvious ones are in the SQLite 
Online Backup API, which is the set of calls underlying the '.backup' command 
you mentioned.  You can find documentation for this here:



Unfortunately I don't think the PHP sqlite3 tools give access to this API.

Hope that helps.  Don't hesitate to get back to us if we can help.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

RE: [sqlite] Backing up a SQlite database

[Brandon, Nicholas \(UK\)]

>Derrell,

>Just to clarify, you don't need to use an exclusive transaction. That
will acquire a write lock and unnecessarily block 
>all other readers as well. You only need to hold a read lock to prevent
any other process from acquiring a write lock.

>Dennis Cote

I asked a similar question last year...
http://article.gmane.org/gmane.comp.db.sqlite.general/17946




This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Backing up a SQlite database

[Dennis Cote]

[EMAIL PROTECTED] wrote:



You should either have your backup application open the database and do a
BEGIN EXCLUSIVE; statement to ensure that no other processes can write to it
while you're backing it up

  

Derrell,

Just to clarify, you don't need to use an exclusive transaction. That 
will acquire a write lock and unnecessarily block all other readers as 
well. You only need to hold a read lock to prevent any other process 
from acquiring a write lock.


Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Backing up a SQlite database

[Dennis Cote]

[EMAIL PROTECTED] wrote:


You should either have your backup application open the database and do a
BEGIN EXCLUSIVE; statement to ensure that no other processes can write to it
while you're backing it up, or if you don't want to do that, you can use the
command line shell and do:

  sqlite3 .dump database.db > database.sql

and then back up database.sql.  In this latter case, the shell ensures that
the database is unchanged during the entire dump.  To restore, you do
something like this on Windows (I'm not a Windows expert so the command may
need some fixing):

  del database.db
  type database.sql | sqlite3 database.db


  

Derrell,

I believe that should be:

 sqlite3 database.db .dump > database.sql

 sqlite3 database.db < database.sql

But I also wanted to point out that this does not create an exact copy 
of your database. In particular it does not include the pragams that may 
have been set in the original database. Settings like default_page_size 
and auto_vacuum are not dumped.


To create a complete backup you need to use your first approach to 
create a utility does a normal file copy in code inside a transaction 
(which prevents other processes from doing any writes).


Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Backing up a SQlite database

[Rich Shepard]

On Fri, 9 Feb 2007, Christian Smith wrote:


No, no, no! Copying the file is not atomic, and a live database may be
updated part way through the copy.


  Mea culpa! My response was based on my own use of sqlite, which is
embedded in models. Therefore, when I do any copying of the database file
it's when the model is not running. I've not used sqlite in a continuously
live environment.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Backing up a SQlite database

[Derrell . Lipman]

Rich Shepard <[EMAIL PROTECTED]> writes:

> On Fri, 9 Feb 2007, Mikey C wrote:
>
>> This might be a dumb question, but is taking a backup of a live database
>> simply a matter of copying the file to a backup device/drive?
>
>   Yes. It's a regular file to your OS. As a matter of fact, you can copy the
> file to another name and open that other name to see the same tables and
> data as with the original. That's how I keep a backup of the database I'm
> developing.
>
>> And restoring it a matter of copying it back?
>
>   Yes.

That is a dangerous way to back up a live database.  If another process
decides to write to the database while you're backing it up, you'll be backing
up partially modified data, and you're also ignoring any possible journal
file.  Whey you restore, you'll find corrupt data in this case.

You should either have your backup application open the database and do a
BEGIN EXCLUSIVE; statement to ensure that no other processes can write to it
while you're backing it up, or if you don't want to do that, you can use the
command line shell and do:

  sqlite3 .dump database.db > database.sql

and then back up database.sql.  In this latter case, the shell ensures that
the database is unchanged during the entire dump.  To restore, you do
something like this on Windows (I'm not a Windows expert so the command may
need some fixing):

  del database.db
  type database.sql | sqlite3 database.db

Derrell

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Backing up a SQlite database

[Christian Smith]

Rich Shepard uttered:


On Fri, 9 Feb 2007, Mikey C wrote:


This might be a dumb question, but is taking a backup of a live database
simply a matter of copying the file to a backup device/drive?


 Yes. It's a regular file to your OS. As a matter of fact, you can copy the
file to another name and open that other name to see the same tables and
data as with the original. That's how I keep a backup of the database I'm
developing.



No, no, no! Copying the file is not atomic, and a live database may be 
updated part way through the copy.


Use the sqlite shell .dump command, which will implement the necessary 
locking:

$ sqlite3 db.file .dump  > backup.sql

The backed up file is a SQL script that will restore the database.


Christian

--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Backing up a SQlite database

[Will Leshner]

On 2/9/07, Rich Shepard <[EMAIL PROTECTED]> wrote:

On Fri, 9 Feb 2007, Mikey C wrote:

> This might be a dumb question, but is taking a backup of a live database
> simply a matter of copying the file to a backup device/drive?

   Yes. It's a regular file to your OS. As a matter of fact, you can copy the
file to another name and open that other name to see the same tables and
data as with the original. That's how I keep a backup of the database I'm
developing.


You do, however, want to be sure there isn't a hot journal lying
around when you do the copy. Otherwise you could end up copying the
database file and not the journal, which effectively corrupts the
database.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Backing up a SQlite database

[Rich Shepard]

On Fri, 9 Feb 2007, Mikey C wrote:


This might be a dumb question, but is taking a backup of a live database
simply a matter of copying the file to a backup device/drive?


  Yes. It's a regular file to your OS. As a matter of fact, you can copy the
file to another name and open that other name to see the same tables and
data as with the original. That's how I keep a backup of the database I'm
developing.


And restoring it a matter of copying it back?


  Yes.


I am using Windows with NTFS drives.


  Works there, too.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

-
To unsubscribe, send email to [EMAIL PROTECTED]
-