This article explains how to restore an SQLite database from within the SQLite command line interface.

There are a few ways to restore a database from the SQLite CLI.

One way to do it is to simply attach a new database using the backup file (or a copy of it). Another way to restore a database is to use the .restore dot command to restore the database file to your chosen database within SQLite CLI.

The .restore Command

The .restore command was designed specifically to restore a database from a file. It’s pretty straightforward to use.

Here’s an example:

ATTACH DATABASE 'pets2.db' AS Pets2;
.restore Pets2 bak/pets_backup.db

In this case I used the ATTACH DATABASE statement to create a blank database file and attach it under the name Pets2.

I then used the .restore command to restore an existing backup file to that database.

In this case the backup file was in a subdirectory called bak. If your backup file is in a different directory, you’ll need to use the applicable file path.

Attach the Database

Another way to restore a backup file is to simply attach it directly.

So, instead of running the .restore command in the previous example, I could have simply done the following:

ATTACH DATABASE 'bak/pets_backup.db' AS Pets2;

However, before you do this, you should ensure that you have a copy of the backup file somewhere else. Probably best to rename the file you’re going to attach too.

In this example, I would have been better off taking a copy of bak/pets_backup.db and placing the copy into my data directory or current directory, then renamed that file to Pets2.db or whatever is more suitable.

If I’d done that, I would have attached it like this:

ATTACH DATABASE 'pets2.db' AS Pets2;

The .open Command

You can also use the .open command to close the existing database and open the file you want to restore.

.open pets2.db

Open the Backup File when Connecting to SQLite

And we could have also opened that file directly when connecting to SQLite:

sqlite3 Pets2.db