Menu
I have sqlite database on my local host (Win NT) and want to back it up. I want to use shell command '.backup' to accomplish this task. But it seems I can insert a new rows into database during backup process.
Does '.backup' shell command starts new exclusive transaction on my database?
I thought when I execute '.backup' shell command it locks my database.
951
You can also backup a SQLite database using the SQLite command. This output or backup file in this way will contain all the necessary SQL codes to reconstruct the database. Run the following command to backup the test.db database file to backup.sql SQL file:
The BEGIN command is used to start or open a transaction. Once an explicit transaction has been opened, it will remain open until it is committed or rolled back. Following is the syntax of the SQLite BEGIN command. We can start the transaction using BEGIN or BEGIN TRANSACTION commands. Here, the keyword TRANSACTION is optional.
Start the sqlite3program by typing "sqlite3" at the command prompt, optionally followed by the name the file that holds the SQLite database (or ZIP archive). If the named file does not exist, a new database file with the given name will be created automatically.
In case if any error occurred while executing these SQLite statements then the complete transaction will be rollbacked. Generally the SQLite is in auto-commit mode that means SQLite automatically starts a transaction for each command, process and commit the transaction changes automatically to database.
The sqlite3 backup method does not lock the database. I would suggest to use the following workaround if you would like to lock the database:
INSERT statement, the database gets a reserved lock. However, this INSERT statement can be empty.ROLLBACK or COMMIT.Code:
BEGIN;
INSERT INTO <anytable> SELECT * FROM <anytable> WHERE 1=0;
.backup <database> <file>
ROLLBACK;
A less hacky way would be if you are using a table named 'backup' and you are inserting a row (date,..) for each copy (if this information is relevant for you).
189
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
