Server Version#: latest
Player Version#: latest
(Apologies for not recording versions, my Plex server is offline at this time.)
I’m hoping someone can help me with getting a listing of my content (movies, TV, etc.)? I know this sounds funny, but please hear me out…
All of my Plex content was on 2 hard drives and 1 failed before I could back it up. (18TB, ugh!). My 2nd drive is backed up, but I cannot afford the cost to recover data (if any) from the failed drive. I’m ok losing the content (sort of, as it just tears me up), but I can reproduce 95% of it. My problem is not knowing exactly what content was on that drive. I expect that data’s contained in the Plex database, so I’m wondering if anyone can assist with querying my database to retrieve my title listings? All I need is just a list of the files, I can do the rest.
I’m a DBE/DBA so have no problem with SQL databases, but I cannot find a schema listing of the Plex database, so I have no idea what content’s in which tables. There are a LOT of tables and it’d take an immense amount of time trial-n-erroring queries to reverse engineer the database enough to get what I need.
So perhaps there’s somebody who could invest a little time to save me tons of time, please?
Thx in advance!
PS I don’t need help with the SQL, just table names.
I don’t know how to extract your series list from the Plex database, but this is how you can backup your current database before Plex goes through and removes the missing content.
Also, didn’t forget/ignore backing up the PMS registry settings as well. This is imperative to rebuilding your Plex server account if any other problem occurs.
I’ve been where you are and it’s frightening and liberating at the same time! – although I lost a HDD with of movies, not TV series.
FYI, I backup my entire Plex data folder several times a week to a 2nd drive (actually 2 2TB SSDs using DrivePool) several times a week. This keeps not only my database backed up, but also all my thumbnails, artwork, credits scan, etc intact).
My folders are backed up to a dedicated NAS as well as my Windows “desktop” daily driver - as opposed to my Windows ‘server’ dedicated to PMS and transcoding.
It took me a while to be in the position to have additional storage for backups of my media, but it’s definitely worth it in the long run.
Otherwise you will have to manually query the primary SQLite database file. To get comprehensive details, you will have to JOIN at least 2 tables.
But if you only need a list of file names and folder locations, the column files from the table media_parts is probably sufficient.
Worth a shot if you have a tiny bit experience with SQL.
If the command line ain’t your thing, try https://sqlitebrowser.org/
It also allows you to export the table to CSV, which you then can sort and filter in a regular spreadsheet app.
Great advice in general and I’m following it now. I’ve got everything backed up onto a new drive (replacing the failed one, plus a new _Backup folder) as I’ll be relocating my PMS to a new dedicated PC. (I used to have such a standalone box, finally got to replace it, hehe.)
My current plan is to move my 2 drives to the new PC, install PMS, migrate the backups to the new PMS installation (copy-replace, etc.), then restart and see what PMS finds vs. losing track of. With backed up database files, I plan to use SQL queries to try locating my old content listings. I’ve done some SQL exploration of the PMS database before, is definitely not a schema I’d have designed, but it obviously works, so I’ll just have to adapt to it. But exploring someone else’s undocumented database is like exploring a new city w/o any maps or map apps. Having someone suggest tables is like someone suggesting places to go in the new city, imho.
Thx again, great general advice, most of which I’d already found, but some not, so I definitely appreciate the assist.
/cheer
Good idea, no telling how much has changed over the years. I’ll give this a try before whacking away at the database with a SQL query app. And wouldn’t it be cool if the WebTools app could give me insights into the PMS database itself? o.O
On a side note, I once wrote a Windows GUI app that let me do some basic operations on my PMS installation, but I didn’t yet pursue doing much with the database as I had no database schema to assist me. With a likely pending job layoff, I might have to resume working on that app. I still use it for things like finding particular content (folders with missing files, files with particular metadata tags, etc.). I could even clone some functionality I created for my Windows File Wrangler GUI app.
And with backups kept on my main PC, I can dink around with the db files all I want w/o compromising my new PMS installation on my new dedicated PC. Well, so goes the plan, at least.
Super, many thanks! I used SQLiteStudio previously, I just didn’t spend enough time exploring the tables. Given enough time, I can figure it out, have done so many times at work with SQL Server and Oracle databases, SQLiteStudio is just more primitive than the tools I’m used to…kinda like writing web apps using Notepad instead of Visual Studio.
Thx for the media_parts tip, though…it’ll let me hit the ground running, at least.
Oh yeah, that’s exactly what happened to me: 15TB of movies and TV series, both. I was backing up my 2 drives (the 18TB drive is about 10TB full atm), but something happened to one, then the main drive died suddenly, so I lost both. I still have the 18TB drive OK, plus its backup, I just can’t afford the $5K to have someone retrieve the data off the dead drive, even if they can (i.e. no guarantees).
And yeah, I exported the registry node as well, just something I do automatically with anything, lol. Somebody below just added a link to some SQL queries he posted, so I’m giving that a try tomorrow.
I was going to look at these tomorrow or Friday, but when I saw the quality of your SQL, I’m going to take a shot at this after dinner tonight, instead. Man, I can’t thank you enough because this is EXACTLY what I was looking for. This is such a HUGE jump-start, I can’t thank you enough.
/cheer
Having someone suggest tables is like someone suggesting places to go in the new city, imho.
I’ll give this a try before whacking away at the database with a SQL query app. And wouldn’t it be cool if the WebTools app could give me insights into the PMS database itself? o.O
