Using sqlite query to build script...

Shell Programming and Scripting
1

Okay, so this one is a bit above my knowledge level so I'm hoping for some pointers.

Here's the scenario:

I have a backup system on my network that makes single file images of the machines it's backing up and uses an sqlite database to keep track of everything. As is pretty typical with backup systems, it simply creates new backups and deletes the old. It also names these files with the date and time it ran as part of the file name. There is no way to alter this behavior.

I also have a cloud backup service that uses delta-blocking to only upload changed file blocks.

Now, because the local backup system is creating new files, if I simply point the cloud backups to those folders it's going to try and upload the whole dang thing every time since it sees it as a completely new file (which it technically is). However, the new file is the same format and everything as the old one, and after doing some test, if I can maintain a hardlink of a single name to the most current backup image, the cloud backup service sees it as the same file and uses the delta-blocking to only update data that is different in the new image from the previous.

I am decent with SQL queries so I've got as far as using the data in the DB and some fancy string manipulation to create the list of commands that would do this for me... i.e. query output is several lines similar to:

ln <path to file1> <hardlink1>
ln <path to file2> <hardlink2>
ln <path to file3> <hardlink3>

I'm stuck at creating a bash script or something that I can schedule to run that will rm the old links, run the query, and use the results to create the new links... unless you guys think there's a better or easier way. I'm just going this direction because SQL is what I know. I was actually going to try to create a bash script that basically creates another bash script by echoing a few lines of commands, appending the query output somehow, and then executes what it just created... that's when I decided to post here. :slight_smile:

Thanks everyone!

2

Question: doesn't the cloud storage have deduplication?
This should automatically find identical blocks on different files and avoid separate blocks on storage devices. Of course it must first upload the whole new file.
So, if the upload time/volume is the concern, your links can make sense.
On most platforms ln -f "overwrites" an existing link.

ln -f file.week1 file.upload

and then relink file.upload with

ln -f file.week2 file.upload

You can check with

ls -li

which two files are hard-linked.

3

I assume they do, but yes, the time it takes to upload the amount of data is the issue I'm trying to solve.

Awesome! That helps a lot. So now I've essentially got a query that creates the list of commands I need to run. Is this really as easy as making a 2 line bash script like so

#!/bin/bash
sqlite3 BackupSystem.db < UpdateLinksQuery.sql > UpdateLinks.sh
UpdateLinks.sh