Help Needed: How to Extract List of Media Files from DB

[Mike_Hayes]

The drive where my media files are located died. While I wait for the painfully slow block-level recovery process to complete, I need to generate a list of all files and their sizes so I can rename them properly.

I made a copy of the entire Plex Media Server folder and registry from the server since that PC is quite old, and I plan to re-install it on a new PC.

I found a sample query in this post: I need to export a list of files to my desktop - #18 by hthighway. I’ve run some queries on the backup copy of the db. However, it only lists the files from one of my libraries, Movies. I also need the info from three other libraries: Home Movies, Lectures, and TV Shows.

Any suggestions or a better approach are welcomed.

Here’s a small sample of what I’ve tried.
SELECT file, size FROM media_parts WHERE file is not null;

Filename| Size
E:\Movies\Title1.mkv| 1,060,159,845
E:\Movies\Another Title.mkv| 962,753,802
E:\Movies\Title3.mkv| 1,529,794,025
E:\Movies\A 4th title.mkv| 1,167,791,464

“SELECT S.id, name, root_path, agent, scanner FROM section_locations L INNER JOIN library_sections S ON L.library_section_id=S.id ORDER BY name;”

id,name,root_path,agent,scanner
3,“Home Movies”,E:\HomeMovies,com.plexapp.agents.none,“Plex Video Files Scanner”
5,Lectures,E:\Lectures,tv.plex.agents.series,“Plex TV Series”
1,Movies,E:\Movies,tv.plex.agents.movie,“Plex Movie”
1,Movies,E:\Test,tv.plex.agents.movie,“Plex Movie”
2,“TV Shows”,E:\Shows,tv.plex.agents.series,“Plex TV Series”

[DTR]

@Mike_Hayes

Here’s a small sample of what I’ve tried.
SELECT file, size FROM media_parts WHERE file is not null;

That should capture everything, including TV episodes, music tracks, and “other video” files. If it doesn’t show any other files, it sounds like those entries got removed from the database (possibly due to "Empty trash automatically after every scan being enabled). Do you have any older DB backups you can try running the query on? The following would show you how many files Plex has associated with each library, for sanity testing:

SELECT s.name, count(1) AS item_count FROM media_parts p
  INNER JOIN directories d ON d.id=p.directory_id
  INNER JOIN library_sections s ON s.id=d.library_section_id
GROUP BY s.name
ORDER BY item_count DESC;

[FordGuy61]

Use the Export Tools component of WebTools-NG. It is mentioned at the top of the thread you linked.

It will export a list in a CSV file.

[dbirch]

For anyone telling you to use database esquire directly, don’t. Plex’s database uses a custom SQLLite format. its far easier to just use Releases · WebTools-NG/WebTools-NG · GitHub

[Mike_Hayes]

@DTR

SELECT s.name, count(1) AS item_count FROM media_parts p
  INNER JOIN directories d ON d.id=p.directory_id
  INNER JOIN library_sections s ON s.id=d.library_section_id
GROUP BY s.name
ORDER BY item_count DESC;

Thanks DTR. The backups I have on the server are all missing the additional libraries. Unfortunately, the drive died when I was away so things got cleaned up a bit too much. I’ll try looking at some system level backups to see if I can find any older dbs.

[Mike_Hayes]

FordGuy61 and dbirch, Thanks for the reply. My understanding is that Export Tools requires a running server, and I’m unable to do that.

[FordGuy61]

@Mike_Hayes

My understanding is that Export Tools requires a running server,

Sort of…

You need Plex Media Server with the desired database files. It does not have to be your “real” server or have access to the media files.

Example using another system:

1. Install Plex Media Server on another system (laptop, old PC, etc.).
2. Run & claim the server. Skip setting up any libraries.
3. Go to Settings → Library and disable the first five option (scan automatically through empty trash after every scan).
4. Stop Plex Media Server.
5. Copy over the desired database files from the “real” server.
6. Start Plex Media Server.
7. Use WebTools-NG to query the database for desired info.

[Mike_Hayes]

@FordGuy61

You need Plex Media Server with the desired database files. It does not have to be your “real” server or have access to the media files.

Example using another system:

1. Install Plex Media Server on another system (laptop, old PC, etc.).
2. Run & claim the server. Skip setting up any libraries.
3. Go to Settings → Library and disable the first five option (scan automatically through empty trash after every scan).
4. Stop Plex Media Server.
5. Copy over the desired database files from the “real” server.
6. Start Plex Media Server.
7. Use WebTools-NG to query the database for desired info.

Good to know FordGuy61, thanks.
I will definitely use that approach when testing the new server.