Every so often you may put yourself in a situation where you’re forced to reinstall Plex. Regrettably, Plex has no built-in functionality for database back-up and migration. This may seem logical, as it is typically unimportant to backup and restore posters and folder matches. However, there is one piece of database information that is worth restoring from previous servers: the view history. This information tells the Plex Media Server instance what has been marked as Played/Unplayed. It also contains some extra information such as user ratings.
But the issue arises when merging into a server that isn’t a clean install (absolutely no watch history). In this scenario, you may be faced with errors such as
Error: near line 2319: UNIQUE constraint failed: metadata_item_settings.id, and you may see negative unplayed counts within Plex.
Migrating watch history while avoiding these issues can be done in six easy steps.
Part One: Merging Databases
1) Locate the databases directory for the Plex install that contains the view history you wish to save. This will be found in
./Plex Media Server/Plug-in Support/Databases/
The default location of this Plex Media Server folder will vary depending on operating system. For more directories see Where is the Plex Media Server data directory located? Here’s the location for a few common operating systems.
Unraid Docker (Binhex):
When you locate this directory, navigate to it via a terminal. This can be done with cd on Linux and dir on Windows.
2) Ensure that sqlite3 is installed on the host operating system by typing
sqlite3 -version into your terminal. If an error shows indicating that it is not installed, install SQLite3 through your package manager or through this link.
3) Put the view history into a file called viewhistory.sql. This can be done via typing the following command while in the Databases directory:
echo ".dump metadata_item_settings" | sqlite3 com.plexapp.plugins.library.db | grep -v TABLE | grep -v INDEX > viewhistory.sql
4) Move viewhistory.sql to the databases directory for the Plex install that you’re migrating data into.
5) Merge view history data from the old server into the new server. This can by done by running the following:
cat viewhistory.sql | sqlite3 com.plexapp.plugins.library.db
Do not be alarmed if you see a
UNIQUE constraint failed error. This will be fixed in the next step.
Part Two: Removing the Duplicates
6) Remove duplicate database entries that cause negative a view count. Please note, it is highly recommended to create a backup of
com.plexapp.plugins.library.db before attempting to modify it.
If the server you’re merging view states to is not perfectly clean (a server with no watch history), or if you received
UNIQUE constraint failed in the previous steps then you must run the following command in the current directory:
sqlite3 com.plexapp.plugins.library.db "DELETE FROM metadata_item_settings WHERE id in (SELECT MIN(id) FROM metadata_item_settings WHERE account_id = 1 GROUP BY guid HAVING COUNT(guid) > 1);"