The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Backup of an SQLite database file

My app uses an SQLite database and I want to implement some database backup with a posilitity to send me a copy, so I can troubleshoot if needed.

The SQLite database is a single file, which is created from scratch on the client's PC when the app is run for the first time. Now the question is, do I need to set permissions of the file somehow prior to zipping and sending it by e-mail to me? Or do I just ZIP it and I will be able to open the database on my PC?

I am not very experienced in file permissions, so I am asking. If there will be any problems with moving the DB file to another PC, can I just allow everyone to change the file and that's it?

Thanks,
Petr
Petr Veit
Monday, May 12, 2008
 
 
As long as you can read it, you can send it.  The most important thing is to make sure you do your backup when no other transactions are running.  Hopefully there aren't any journal files laying around, but if there are, back those up and send them too since they're part of the database consistency guarantee.
Doug
Monday, May 12, 2008
 
 
Thanks Doug,

I understand that I can send the file, but will I be able to use it on another PC due to any permissions applied to the DB file when the DB was created on the client's PC?

I also thought that SQLite database is only one file. Are there any other files I should check?

Thanks,
Petr
Petr Veit
Monday, May 12, 2008
 
 
there are no permissions. sqlite doesn't have any permissions or authentication of its own. simply file-system permissions apply. once the file is give to you (zipped and emailed or whatever), you own the file. so, you shouldn't have any troubles.

-don
Don Dickinson Send private email
Monday, May 12, 2008
 
 
Don, thanks for clarification, that's what I neded to know.

Any comments about SQLite using more files, so I should include them into the backup as well?
Petr Veit
Monday, May 12, 2008
 
 
SQLite stores all of its data in one file.  But _during_ an update, it will create a roll back journal.  Assuming the transaction completes and the process doesn't crash, that roll back jounrnal gets deleted.  In addition, if you have multiple databases involved in a transaction, there can be a master-journal file which lists all the individual journal files.

You typically don't see those files because they are often created and deleted very fast.  But if you DO see them, that means your database is in an inconsistent state, and the primary database file plus the roll-back journal files are all needed to get back to a consistent state.  SQLite will get itself back to a consistent state automatically the next time it touches that database file.

You should guarantee no transactions are executing when you make your copy, and that the last user (process) that was touching those files closed the database properly and shutdown cleanly.  In that case, there won't be any other files to worry about.
Doug
Monday, May 12, 2008
 
 
I will be the only one who will use the DB, so I can connect to the DB to check if it is OK, then disconnect and make the backup. I hope this will work.
Petr Veit
Monday, May 12, 2008
 
 
"I can connect to the DB to check if it is OK, then disconnect and make the backup. I hope this will work."

That should unroll any open transaction logs which belong to non-existent connections, because the first thing a connection does is fix anything that needs fixing.

There is also a dump operation runnable from the commandline interface which gets you a traditional SQL dump.
Katie Lucas
Tuesday, May 13, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz