My Plex server decided to go through my database and screw with all the posters and art, so I made the below script which can be used to recover them. You should only use this if doing a full revert to backup is not a viable option. Do also note that if you have refreshed the metadata on any tv series since the backup, it could cause issues with the episode posters.

#Run the following commands as root
sudo su

#User variables
SERVICE_NAME='plexmediaserver'
BACKUP_LOCATION='/ROOT_BACKUP_LOCATION/'
PLEX_USER='plex'

#Derived variables (Slashes at end of directory paths are required)
SQLITE3_PATH="/usr/lib/$SERVICE_NAME/Plex SQLite"
DB_PATH="/var/lib/$SERVICE_NAME/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db"
IMG_PATH="/var/lib/$SERVICE_NAME/Library/Application Support/Plex Media Server/Metadata/"
CACHE_PATH="/var/lib/$SERVICE_NAME/Library/Application Support/Plex Media Server/Cache/PhotoTranscoder/"

#Stop the plex service until we have updated everything
echo "Stopping Plex"
systemctl stop "$SERVICE_NAME"

#Set the (user_thumb_url, user_art_url, and updated_at) from the old database in the new database.
#The updated_at is required to match the cached images
echo "Updating database"
sudo -u "$PLEX_USER" "$SQLITE3_PATH" "$DB_PATH" -cmd "ATTACH DATABASE '$BACKUP_LOCATION$DB_PATH' as OLDDB" "
UPDATE
  metadata_items AS NMDI
SET
  user_thumb_url=OMDI.user_thumb_url,
  user_art_url=OMDI.user_art_url,
  updated_at=OMDI.updated_at
FROM
	OLDDB.metadata_items AS OMDI
WHERE
	NMDI.id=OMDI.id
"

#Copy the posters and art from the old directory to the new directory
echo "Copying posters"
cd "$BACKUP_LOCATION$IMG_PATH"
find -type d \( -name "art" -o -name "posters" \) | xargs -n100 bash -c 'rsync -a --relative "$@" "'"$IMG_PATH"'"' _

#Copy the cached images over
echo "Copying cache"
rsync -a "$BACKUP_LOCATION$CACHE_PATH" "$CACHE_PATH"

#Restart plex
echo "Starting Plex"
systemctl start "$SERVICE_NAME"

Yesterday I moved my Plex installation from a Windows machine to a Linux machine. The primary data folders that needed to be copied over were Media, Metadata, Plug-ins, and Plug-in Support. It doesn't hurt to copy over some of the folders in Cache too. It's possible there may be some more data that needs to be moved over, but I don't have documented what.

After moving all the data, I updated the paths in the database for the new machine. Doing this allowed me to keep everything as it was and no new refreshes/scans needed to be done.

The location of the Plex modified SQLite for me was at /usr/lib/plexmediaserver/Plex SQLite. So the following is the bash commands to stop the plex server and open SQLite editor on Linux Mint 21.3.

service plexmediaserver stop
/usr/lib/plexmediaserver/Plex\ SQLite '/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db'

And the following is the SQL I used to replace a D: drive with a path of /home/plex/drives/d/. You can replace these strings in the below code with your custom drives and paths.

#Replace backslashes with forward slash in paths
UPDATE media_parts SET file=REPLACE(file, '\', '/');
UPDATE section_locations SET root_path=REPLACE(root_path, '\', '/');
UPDATE media_streams SET url=REPLACE(url, '\', '/') WHERE url LIKE 'file://%';

#Replace root paths
UPDATE media_parts SET file=REPLACE(file, 'D:/', '/home/plex/drives/d/');
UPDATE section_locations SET root_path=REPLACE(root_path, 'D:/', '/home/plex/drives/d/');
UPDATE media_streams SET url=REPLACE(url, 'file://D:/', 'file:///home/plex/drives/d/') WHERE url LIKE 'file://%';
UPDATE media_streams SET url=REPLACE(url, 'file:///D:/', 'file:///home/plex/drives/d/') WHERE url LIKE 'file://%';
UPDATE metadata_items SET guid=REPLACE(guid, 'file:///D:/', 'file:///home/plex/drives/d/');

Since version 3 of Firefox, the browser has moved over from using flat files for keeping track of browsing history (history.dat) and bookmarks (bookmarks.html) to using SQLite databases (places.sqlite). This change over was required because the old flat file formats were badly implemented, clunky, and not able to handle the new demands of the location bar and browser history. Using a SQL database was the perfect solution for the complexity brought in with the new location bar and its dynamic searching of previous URLS, as SQL is easy to implement, is mostly compatible against multiple SQL application implementations (removing dependency on a single product), and powerful for cross referencing lookups. As a matter of fact, most of the data Firefox keeps now is stored in SQLite databases.

SQLite was also a good choice for the SQL solution because it can be implemented minimally straight into a product without needing a large install and a lot of bloat. While I like SQLite for this purpose and its ease of implementation, it lacks a lot of base SQL functionality that would be nice, like TABLE JOINS inside of DELETE statements, among many other language abilities. I wouldn’t suggest using it for large database driven products that require high optimization, which I believe it can’t handle. It’s meant as a simpler SQL implementation.

Anyways, I was very happy to see that when you delete URLs from the history in the newest version of Firefox that it actually deletes them out of the database as opposed to just hiding them, like it used to. The history manager actual seems to do its job quite well now, but I noticed one big problem. After attempting to delete all the URLs from a specific site out of the Firefox history manager, I noticed there were still some entries from that site in the SQLite database, which is a privacy problem.

After some digging, I realized that there are “hidden” entries inside of the history manager. A hidden entry is created when a URL is loaded in a frame or IFrame that you do not directly navigate too. These entries cannot be viewed through the history manager, and because of this, cannot be easily deleted outside of the history database without wiping the whole history.

At this point, I decided to go ahead and look at all the table structures for the history manager and figure out how they interact. Hidden entries are marked in places.sqlite::moz_places.history with the value “1”. According to a Firefox wiki “A hidden URL is one that the user did not specifically navigate to. These are commonly embedded pages, i-frames, RSS bookmarks and javascript calls.” So after figuring all of this out, I came up with some SQL commands to delete all hidden entries, which don’t really do anything anyways inside the database. Do note that Firefox has to be closed to work on the database so it is not locked.

sqlite3 places.sqlite
DELETE FROM moz_annos WHERE place_id IN (SELECT ID FROM moz_places WHERE hidden=1);
DELETE FROM moz_inputhistory WHERE place_id IN (SELECT ID FROM moz_places WHERE hidden=1);
DELETE FROM moz_historyvisits WHERE place_id IN (SELECT ID FROM moz_places WHERE hidden=1);
DELETE FROM moz_places WHERE hidden=1;
.exit

This could all be done in 1 SQL statement in MySQL, but again, SQLite is not as robust :-\. There is also a “Favorite’s Icon” table in the database that might keep an icon stored as long as a hidden entry for the domain still exists, but I didn’t really look into it.