SQLite Database Backup

Introduction

In this lab, you will learn how to back up and restore an SQLite database. You'll learn to export a database to an SQL file using the .dump command, create a backup, and then restore the database from that backup. This is essential for protecting your data.

Create a Sample Database

In this step, you will create a sample SQLite database and populate it with some data. This will give you a database to practice backing up and restoring.

First, open the SQLite shell. This is where you'll interact with the database.

sqlite3 mydatabase.db

This command starts the SQLite shell and creates a new database file named mydatabase.db. If the file already exists, it will open that file instead.

Now, create a table named users with columns for id, name, and email:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
);

This SQL command creates a table named users. The id column is an integer and the primary key, which means it uniquely identifies each row in the table. The name and email columns are text fields.

Next, insert some sample data into the users table:

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');

These commands add two rows to the users table. The first row has the name 'Alice' and the email 'alice@example.com'. The second row has the name 'Bob' and the email 'bob@example.com'.

To verify that the data has been inserted correctly, run the following command:

SELECT * FROM users;

You should see output similar to this:

1|Alice|alice@example.com
2|Bob|bob@example.com

This output shows the contents of the users table, confirming that the data has been inserted successfully.

Export the Database to an SQL File

In this step, you will export the mydatabase.db database to an SQL file named mydatabase.sql. This SQL file will contain the commands needed to recreate the database and its data.

First, exit the SQLite shell:

.exit

This command closes the connection to the database and returns you to the Linux terminal.

Now, use the .dump command to export the database to an SQL file:

sqlite3 mydatabase.db .dump > mydatabase.sql

This command uses the sqlite3 command-line tool to connect to the mydatabase.db database. The .dump command tells SQLite to output the entire database as a series of SQL commands. The > symbol redirects the output of the .dump command to a file named mydatabase.sql.

To verify that the export was successful, you can view the contents of the mydatabase.sql file using the cat command:

cat mydatabase.sql

You should see output similar to this:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
);
INSERT INTO users VALUES(1,'Alice','alice@example.com');
INSERT INTO users VALUES(2,'Bob','bob@example.com');
COMMIT;

This output shows the SQL commands that create the users table and insert the data. This confirms that the database has been successfully exported to an SQL file.

Create a Backup of the Database

In this step, you will create a backup of the database using the same .dump command. This is essentially the same process as exporting to an SQL file, but we'll save the backup to a different file.

sqlite3 mydatabase.db .dump > mydatabase_backup.sql

This command creates a backup of the mydatabase.db database and saves it to a file named mydatabase_backup.sql.

To verify that the backup was created successfully, you can view the contents of the mydatabase_backup.sql file using the cat command:

cat mydatabase_backup.sql

You should see the same output as in the previous step, confirming that the backup file contains the SQL commands needed to recreate the database.

Restore the Database from the Backup

In this step, you will restore the database from the mydatabase_backup.sql file. This will recreate the database and its data from the backup.

First, let's simulate a scenario where the original database is lost. Delete the mydatabase.db file:

rm mydatabase.db

This command removes the mydatabase.db file from the file system.

Now, restore the database from the mydatabase_backup.sql file:

sqlite3 mydatabase.db < mydatabase_backup.sql

This command uses the sqlite3 command-line tool to create a new database file named mydatabase.db. The < symbol redirects the contents of the mydatabase_backup.sql file to the sqlite3 command, which executes the SQL commands in the file. This recreates the database and its data.

To verify that the restoration was successful, open the database in the SQLite shell:

sqlite3 mydatabase.db

And query the users table:

SELECT * FROM users;

You should see the data that was backed up:

1|Alice|alice@example.com
2|Bob|bob@example.com

This confirms that the database has been successfully restored from the backup file.

Exit the SQLite shell:

.exit

Summary

In this lab, you have learned how to back up and restore an SQLite database. You used the .dump command to export the database to an SQL file, and then you restored the database from that file. These skills are essential for protecting your data and ensuring that you can recover from data loss.