SQLite partial backup - how to copy db schema

[Iczer]

I need to make partial backup of sqlite db, but to do it db/table schema is needed. To create universal function, I cannot reuse db creation query. So how I can get it from main db?

$sQuery = "CREATE TABLE IF NOT EXISTS SRDB " & $sSqlBuild_Table & ";"

Or maybe there a another method to do partial backup?

[dmob]

This may/may not be what you want

[jchd]

select * from sqlite_master returns all the DDL statements you need.

You may need to first extract the rows having type = 'table', then 'index' then 'trigger' then 'view', then ...
After that you may have to reorder CREATE TABLE statements so that foreign keys work in the correct order.

[Skysnake]

Why would you want to make a partial backup?

And of WHAT exactly would this be a partial backup? Of all the data in certain tables, or of only data inserted during the current session?

Alternatively to the backup, make a full copy and delete what you don't need?

... ? 

[jchd]

Making first a backup of a 7.8Tb DB then dropping 7.725Tb of unneeded data in the copy, then performing a vacuum is pretty inefficient.

It's possible to get and filter DDL statements from the source DB as shown above and create only the tables/indices/triggers/views needed in the copy, then copy corresponding rows. In this case, one can use regular open of source and ATTACH the target, name it tgt for instance. Then insert into tgt.table_A select * from table_A; will do. Repeat and rince for all wanted tables.

Otherwise several third-party SQLite DB managers allow copying tables from one DB to another one. I personnally use SQLite Expert Pro (payware) whenever I need to perform such task. All required accompanying DDL statements (tables, indices and triggers) are automagically managed.

[Skysnake]

Hmm, thanks @jchd. Point taken.

 

 

[Iczer]

16 hours ago, jchd said:

select * from sqlite_master

Thanks! It exactly what i need!

[cristiscu]

<snip>

We do not permit ads for paid solutions - even if they are of your own making. Please do not do it again.

M23

Edited January 8, 2020 by Melba23