Home > Computers, Databases, HTPC, Media Center, SQL > MyMovies – Bulk Path Change for Online Titles

MyMovies – Bulk Path Change for Online Titles

NOTICE: See the comments section for MyMovies 5 info.

My Home Theater PC has a 2TB RAID array, on which I was storing movies, music and recorded TV. After upgrading from my analog tuners to a Ceton quad tuner board, which gave me access to more programming as well as HD programming, I found that my 2TB array was starting to get a little too close to full, a little too often.

(Skip the background and go straight to the instructions)

The solution I chose was to get a 3TB external drive, and move the movies over to it. I’m only using about 350GB of space for movies right now, but I’m adding to it all the time, and freeing up that space gives a little more breathing room on the RAID array for recorded TV. I left the music on the array for now – I may move it over as well, if I find that we’re running out of space for recorded TV again.

Moving the movies to a different drive introduced a change in the location of the movies on the system. They were all formerly stored in “R:\Movies\<title>\…”, and the new drive was I:. The rest of the path remained the same for each title – it was only the drive letter that changed. In the My Movies database, the old paths remained. I needed a way to update the database to reflect the new locations, without having to edit each title manually – I wasn’t about to edit over 300 titles, one by one.

The My Movies collection management program has a mechanism to do this, but it only found the new location for about 15 titles. It wasn’t going to work for me. I wanted a solution that would cleanly update the entire collection in one go. The best way that I know of to accomplish a task like this is to edit the database directly, so that’s what I set out to do.

Expecting that I wasn’t the first person to do this, and that the solution would already be out on the web somewhere, I searched for the quick and easy solution that had to exist. To my dismay, it didn’t. Not that I could find, anyway. I was pretty surprised, since the HTPC/Media Center crowd tends to lean towards the techie types. “Ok,” I thought, “how hard can it be?”

Here’s the problem – I’m not a Microsoft SQL Server guy. I’ve been using MySQL at a fairly basic level for a long time, and I could have easily done it in about two minutes if the My Movies database used MySQL as the engine. But MSSQL? Eeesh…

The first challenge was finding the proper tool to do the job. MySQL has the command line tool that I’m very familiar with, but as far as I could tell, there was no such tool for MSSQL, and the My Movies installation doesn’t include any tools to directly manipulate the database.

The version of My Movies that I’m using uses MSSQL 2005 as the database engine. Microsoft’s (free!) tool for this version is SQL Server Management Studio Express. I downloaded the 64 bit version and installed it on my Windows 7 Home Premium system without any trouble. Make sure you install and run it as a system administrator, or you may run into problems.

Once it was installed, I opened it up and easily navigated to the My Movies database and found the table/column where the locations of the online titles are stored.

Next, I just needed to know what the SQL query would be to update the locations and change all the instances of R:\ to I:\. Figuring that out took a while, as virtually all of the info I found on the REPLACE command used a SELECT statement as its example. Not being familiar with MSSQL, I thought it was odd – I would have expected an UPDATE statement – but hey, maybe Microsoft just does things different. It wouldn’t be the first time. (In fact, it’s usually the case with them…) Long story short, it turned out that an UPDATE command was what I wanted after all. The examples at the MS site and elsewhere just didn’t use it to demonstrate REPLACE.

I had the query, and I was good to go, right? Nope! I ran into a namespace issue with the database. I wasn’t having any troubles when running SELECT statements, but the UPDATE wasn’t working. I don’t have a good understanding of MSSQL namespaces, and I really don’t want to – I just wanted to get this one thing done. It’s unlikely that I’ll ever need to dig into a MSSQL database like this again, so spending the requisite time to get educated on it seemed wasteful. After many trials & errors, I finally figured it out.

Here’s the step-by-step:

0. BACKUP YOUR DATABASE. I’m not responsible for any damage you do to your system, even if my instructions are wrong. 🙂

1. Download, install (as an administrator) and run (as an administrator) the appropriate version of SQL Server Management Studio Express.

2. Select your My Movies database.

3. Click the “New Query” button.

4. Enter the following query, changing the drive letters as appropriate:

UPDATE [My Movies].[dbo].tblDiscs
SET nvcLocation = REPLACE (nvcLocation,'R:\','I:\')

5. Press the “Execute” button.

That’s it – your locations should be updated now. You can verify the new locations by entering the query:

SELECT nvcLocation FROM [My Movies].[dbo].tblDiscs

I hope that this post has saved you some time and trouble. What seemed like a fairly simple thing turned into a several hour project for me, and if I can save someone else the hassle, I’m happy to do so.