SQLite Forum

how best to completely replicate a db file?
Login

how best to completely replicate a db file?

(1) By tom (tleylan) on 2021-05-19 15:41:36 [link] [source]

I think I could manually perform all the steps but I was curious to know if there is a semi-automated way to regenerate a database from a schema and a set of exported data files.

I have a case where I can't physically access the db file easily but can programmatically. For security I can download the file but I can't upload a replacement. So I'd like a scripted solution that I could use to regenerate a db file from scratch and then populate the tables with data that I know is accurate.

If there is a handy utility that takes care of the grunt work or a site that describes the steps I'd appreciate any pointers.

Thanks.

(2) By Larry Brasfield (larrybr) on 2021-05-19 15:47:05 in reply to 1 [source]

You might consider the CLI shell's .dump command to be that handy utility.

(3) By tom (tleylan) on 2021-05-19 17:22:39 in reply to 2 [link] [source]

Wow thanks. I saw a .dump file mentioned in a message but didn't know what created it. Given the description of the utility I assume it can handle the orderly creation of indexes and such (I don't have any on this file) if they exist?

I believe you have solved my issue!

(4) By anonymous on 2021-05-20 09:23:00 in reply to 3 [link] [source]

I don't know about modern state of things, but around 2018 I switched my application from .dump to SQLite Online Backup API because .dump apparently didn't preserve PRAGMA user_version.

(5) By BohwaZ (bohwaz) on 2021-05-20 13:00:58 in reply to 4 [link] [source]

You can also use VACUUM INTO 'file.db'; to create a backup since 3.27. That's what I use. If not available, I use the Online Backup API. Before that I had to resort to making a file copy after starting an exclusive transaction.