Backup4All Version 9 SQL

README.md

Backup4All Version 9 SQL

Export the following map Zip -> Files. So this will not include rows for deleted files. Only when they were added/changed.

Run on latest SQLite Version

• I suggest to use the free SQLiteStudio GUI from https://sqlitestudio.pl/ (cross platform)

How to Export

1. Open the .bkc file. It is a SQLite file. Might be password protected with the user-defined backup password.
2. Create FullPath View with the SQL bellow
3. Run 2_ZipFiles.sql to get all files
   • add WHERE Storages.SpanIndex == 20 if you want zip part 20
     • real name based on config.. (C_020.zip? C.z19? so check around to see you get the one you need)

Note: If you backup with partial zips, you need to backup last file from index-1, which was possibly started before

How to zip yourself:

"C:\Program Files\7-Zip\7z.exe" a -tzip RESULT.zip -spf2 -bsp1 @FULLPATH_FILE_LIST.txt

• a adds
• -tzip save as zip
• -bsp1 show progress on stdout Source
• -spf2 will put folder structure in zip from level 2 (so no drive C:) Source
  • -spf will store C: also

Note: only the files in FULLPATH_FILE_LIST.txt must be full, you can run this command from any folder

How to extract yourself:

To use the folder structure, go to Drive root and tun the command there, for example to extract on drive C:

> cd C:
> C:
> "C:\Program Files\7-Zip\7z.exe" e -tzip PATH\TO\RESULT.zip -spf2 -bsp1

R_1_FullPath (VIEW).sql

SELECT FolderID, min(PathCode) as PathCode,
     group_concat(Name,'\') as FullName,
     group_concat(value,'\')==min(PathCode) as Sanity -- <key, value> of array is <0..N, arr[0...N]>
FROM (
    SELECT 
        *
    FROM Folders, json_each('[' || replace(PathCode,'\',',') || ']') -- Works well because int only, so no text json sanatize needed
    INNER JOIN FolderDict on FolderDict.DictID == value
    order by Folders.FolderID, key -- key will keep array sorted, which wont happen in a join (order not kept)
)
group by FolderID

R_2_ZipFiles.sql

SELECT 
--    FileVersions.FileVersionID, -- Leading ID
    ( Storages.StorageName || ' ( ' || Storages.SpanIndex || ')' ) as "ZipName",
--    FileVersions.BackupID as "BackupNumber",
--    FileVersions.FileID,
    CASE FileVersions.Status
       WHEN 1
           THEN 'ADDED' 
       WHEN 2
           THEN 'CHANGED-CONTENT' 
       WHEN 4
           THEN 'DELETED' 
       ELSE 'UNKNOWN' 
    END as "FileBackupReason",
    FileVersions.Size as "ByteSize",
--    Files.FileID, -- Not unique table
--    Files.DictID, -- pointer to unique names table id
--    Files.FolderID,
    (FullPath.FullName || '\' || FileDict.Name) as "FullFilePath",
    FullPath.Sanity
from 
   FileVersions 
INNER JOIN Storages on 
    Storages.BackupID == FileVersions.BackupID and
    Storages.SpanIndex == FileVersions.StartSpan -- Null for deleted, so they will not be included
INNER JOIN Files on FileVersions.FileID == Files.FileID
INNER JOIN FileDict on Files.DictID == FileDict.DictID
INNER JOIN FullPath on Files.FolderID == FullPath.FolderID -- My View

-- For Export:
-- WHERE Storages.SpanIndex == 20
     
order by FileVersions.FileVersionID asc