Duplicati BackupExplorer

vbs · September 8, 2024, 8:04pm

Hi guys,

some weeks ago, I was interested in learning more about how the data deduplication actually works for my backups:

For educational purposes (and also for my personal entertainment), I made a little desktop tool I called “Duplicati BackupExplorer”:

It can browse the Duplicati sqlite database and display its contents and also analyze how much data is being reused between different backups. More information here:

In case someone wants to give it a try, binaries for Linux and Windows are available here:

As I am not very artsy, so I used the original Duplicati icon for the application icon. Are you guys ok with that? As far as I know, it is ok regarding the Duplicati license, but of course I am fine to change it if you want.

Jojo-1000 · September 9, 2024, 12:33pm

This looks really good, and it certainly must have required quite a deep understanding of the inner workings of duplicati.

I am not sure whether you see this as completed or not, but what I would also find interesting is an overview of the currently wasted space (from leftover blocks that would be removed by a compact). This is obviously just a suggestion in case you want to keep working on the tool.

drwtsn32 · September 9, 2024, 1:19pm

Neat tool! Thanks for sharing it with the forum.

starob2 · September 9, 2024, 1:48pm

Does this already support Duplicati version 2.0.9? I tried and I’m getting a SQLite Error 1: 'no such table: FileLookup'.

vbs · September 9, 2024, 3:22pm

That is surely a nice idea and it would be an interesting addition. Not sure how easy it is to “simulate” the compacting process and to evaluate how much space it would free. Let me see if I can get a good enough understanding of it.

EDIT:
I am not sure if this information is even included in the database. Possibly only by accessing the actual files this information can be retrieved?

vbs · September 9, 2024, 3:25pm

Hm yes, I didn’t try with 2.0.9 yet as I am only using 2.0.8 so far, so it is well possible that it does not work with it. I would not like to switch my production setup to 2.0.9 currently, but if someone would be willing to provide an example database from 2.0.9 for me then I would be happy to look into it.

Jojo-1000 · September 9, 2024, 4:21pm

I think this is the relevant part that calculates the wasted space, which should only need the database:

github.com/duplicati/duplicati

Duplicati/Library/Main/Database/LocalDeleteDatabase.cs

master


      
          /// and the number of bytes no longer needed in each volume.

          /// The sizes are the uncompressed values.

          /// </summary>

          /// <returns>A list of tuples with name, datasize, wastedbytes.</returns>

          private IEnumerable<VolumeUsage> GetWastedSpaceReport(IDbTransaction transaction)

          {

              var tmptablename = "UsageReport-" + Library.Utility.Utility.ByteArrayAsHexString(Guid.NewGuid().ToByteArray());

          

              var usedBlocks = @"SELECT SUM(""Block"".""Size"") AS ""ActiveSize"", ""Block"".""VolumeID"" AS ""VolumeID"" FROM ""Block"", ""Remotevolume"" WHERE ""Block"".""VolumeID"" = ""Remotevolume"".""ID"" AND ""Block"".""ID"" NOT IN (SELECT ""Block"".""ID"" FROM ""Block"",""DeletedBlock"" WHERE ""Block"".""Hash"" = ""DeletedBlock"".""Hash"" AND ""Block"".""Size"" = ""DeletedBlock"".""Size"" AND ""Block"".""VolumeID"" = ""DeletedBlock"".""VolumeID"") GROUP BY ""Block"".""VolumeID"" ";

              var lastmodifiedFile = @"SELECT ""Block"".""VolumeID"" AS ""VolumeID"", ""Fileset"".""Timestamp"" AS ""Sorttime"" FROM ""Fileset"", ""FilesetEntry"", ""FileLookup"", ""BlocksetEntry"", ""Block"" WHERE ""FilesetEntry"".""FileID"" = ""FileLookup"".""ID"" AND ""FileLookup"".""BlocksetID"" = ""BlocksetEntry"".""BlocksetID"" AND ""BlocksetEntry"".""BlockID"" = ""Block"".""ID"" AND ""Fileset"".""ID"" = ""FilesetEntry"".""FilesetID"" ";

              var lastmodifiedMetadata = @"SELECT ""Block"".""VolumeID"" AS ""VolumeID"", ""Fileset"".""Timestamp"" AS ""Sorttime"" FROM ""Fileset"", ""FilesetEntry"", ""FileLookup"", ""BlocksetEntry"", ""Block"", ""Metadataset"" WHERE ""FilesetEntry"".""FileID"" = ""FileLookup"".""ID"" AND ""FileLookup"".""MetadataID"" = ""Metadataset"".""ID"" AND ""Metadataset"".""BlocksetID"" = ""BlocksetEntry"".""BlocksetID"" AND ""BlocksetEntry"".""BlockID"" = ""Block"".""ID"" AND ""Fileset"".""ID"" = ""FilesetEntry"".""FilesetID"" ";

              var scantime = @"SELECT ""VolumeID"" AS ""VolumeID"", MIN(""Sorttime"") AS ""Sorttime"" FROM (" + lastmodifiedFile + @" UNION " + lastmodifiedMetadata + @") GROUP BY ""VolumeID"" ";

              var active = @"SELECT ""A"".""ActiveSize"" AS ""ActiveSize"",  0 AS ""InactiveSize"", ""A"".""VolumeID"" AS ""VolumeID"", CASE WHEN ""B"".""Sorttime"" IS NULL THEN 0 ELSE ""B"".""Sorttime"" END AS ""Sorttime"" FROM (" + usedBlocks + @") A LEFT OUTER JOIN (" + scantime + @") B ON ""B"".""VolumeID"" = ""A"".""VolumeID"" ";

          

              var inactive = @"SELECT 0 AS ""ActiveSize"", SUM(""Size"") AS ""InactiveSize"", ""VolumeID"" AS ""VolumeID"", 0 AS ""SortScantime"" FROM ""DeletedBlock"" GROUP BY ""VolumeID"" ";

              var empty = @"SELECT 0 AS ""ActiveSize"", 0 AS ""InactiveSize"", ""Remotevolume"".""ID"" AS ""VolumeID"", 0 AS ""SortScantime"" FROM ""Remotevolume"" WHERE ""Remotevolume"".""Type"" = ? AND ""Remotevolume"".""State"" IN (?, ?) AND ""Remotevolume"".""ID"" NOT IN (SELECT ""VolumeID"" FROM ""Block"") ";

          

              var combined = active + " UNION " + inactive + " UNION " + empty;

              var collected = @"SELECT ""VolumeID"" AS ""VolumeID"", SUM(""ActiveSize"") AS ""ActiveSize"", SUM(""InactiveSize"") AS ""InactiveSize"", MAX(""Sorttime"") AS ""Sorttime"" FROM (" + combined + @") GROUP BY ""VolumeID"" ";

              var createtable = FormatInvariant($"{@$"CREATE {TEMPORARY} TABLE ""{tmptablename}"" AS "}{collected}");

vbs · September 9, 2024, 6:40pm

That’s great, thank you. I will look into that.

kenkendk · September 10, 2024, 12:38pm

Looks super neat! I think this is a very useful utility!

It is @agrajaghh who made the icons originally, so I will defer that to him.

The format for the local database did not change between those versions. I think you are perhaps trying to open Duplicati-server.sqlite. This tool looks like it is meant to open the randomly named local databases (one for each backup). You can see the path to each database in the UI under “Database…”

starob · September 10, 2024, 1:09pm

I tried both. But opening a backup database also failed with this error:

An error occured when trying to load the Duplicati database file 'Avalonia.Platform.Storage.FileIO.BclStorageFile': The given key '724' was not present in the dictionary.

vbs · September 10, 2024, 4:32pm

I only tested with my own database, so it is well possible that other databases might have special contents that could be a problem. Would it be possible for you to let me have your database file so I could debug what the problem is?

Yes, you cannot open Duplicati-server.sqlite but only the “random” name database. Every one of those databases represents one backup job as far as I know.

starob · September 10, 2024, 4:57pm

I’ll send you an example database file as a message.

Thanks for looking at this.

Robert

agrajaghh · September 10, 2024, 9:21pm

Really cool tool!

Including the size into the file tree might be also a good feature for duplicati while restoring data:

sure, you can use the icon

vbs · September 11, 2024, 4:16pm

Thanks, thats nice!

vbs · September 14, 2024, 9:16am

I did a new version of the tool and added the information about the wasted space:

But, there is one drawback: as I understand it, there is only information in the database about the compressed size of the whole volume (ZIP file) but not about individual blocks (naturally). Knowing which blocks have been deleted only tells you the uncompressed space on the storage backend, which will be freed by COMPACT. But you cannot know how much actual disk space will be freed.

vbs · March 22, 2025, 7:39pm

A new version (0.8.0) here:

Needs less memory by not loading all backups into memory. Makes it possible to work with large databases. Thanks to @Jojo-1000

PS.
It is really not possible to edit posts in this forum, right? I wanted to update first post.

Jojo-1000 · March 22, 2025, 8:00pm

Looks great and thanks for improving upon my suggestions. I tried it with my main backup and it can handle it without any trouble. I think you can’t edit posts after a few days, but I can update the link to the base release page if you want so it should stay up to date.

vbs · March 22, 2025, 8:08pm

Yes, perfect, that would be great. Deleting the 0.5.0-link and replacing it with the base page would be great:

Thanks alot.