I'm trying to write an SQL query that will update /media folder filenames in the database. I have uploaded a number of PDFs to the media section and now need to change the filenames. Via RDP, I can change the PDF filenames manually but following, the media folder items will be broken.
I'l like to run an SQL query that will rename from the table cmsPropertyData *.pdf to *-public.pdf. The query would find all files with a PDF extension and rename as above. End result would be as follows:
Original:
somefile.pdf
anotherfile.pdf
After Query:
somefile-public.pdf
anotherfile-public.pdf
I can then go to content pages and update the links to the PDFs via the RTE unless a second query could be written to update those as well. This would save dozens of hours as there are thousands of PDFs. Possible?
Windows Server 2008 R2 running SQL Management Studio 2008 R2
besides updating the database you also have to rename the files themselves on disk.
If you upload a media file the mediafile is stored on disk. If you for example upload foobar.pdf, the file on disk is (obviously) foobar.pdf and this name is also stored in the database (on the umbracoFileName-property I guess).
If you only update the database the link will get corrupt because the url will look like foobar-public.pdf, but the file on disk is still called foobar.pdf.
So, it's possible, but you should also write an update for those filenames (probably in .NET).
I already have some software ready to take care of the on-disk renaming, that's the easy part. Now I need to find an SQL query that will handle renamed the PDF references in the database. As far as I know, the query will need to make updates to cmsPropertyData, cmsContent & cmsContentXml.
SQL query to update /media folder filenames
I'm trying to write an SQL query that will update /media folder filenames in the database. I have uploaded a number of PDFs to the media section and now need to change the filenames. Via RDP, I can change the PDF filenames manually but following, the media folder items will be broken.
I'l like to run an SQL query that will rename from the table cmsPropertyData *.pdf to *-public.pdf. The query would find all files with a PDF extension and rename as above. End result would be as follows:
Original: somefile.pdf anotherfile.pdf
After Query: somefile-public.pdf anotherfile-public.pdf
I can then go to content pages and update the links to the PDFs via the RTE unless a second query could be written to update those as well. This would save dozens of hours as there are thousands of PDFs. Possible?
Windows Server 2008 R2 running SQL Management Studio 2008 R2
Hi Robert,
besides updating the database you also have to rename the files themselves on disk.
If you upload a media file the mediafile is stored on disk. If you for example upload foobar.pdf, the file on disk is (obviously) foobar.pdf and this name is also stored in the database (on the umbracoFileName-property I guess).
If you only update the database the link will get corrupt because the url will look like foobar-public.pdf, but the file on disk is still called foobar.pdf.
So, it's possible, but you should also write an update for those filenames (probably in .NET).
Hope it helps,
Jeffrey
Jeffrey,
I already have some software ready to take care of the on-disk renaming, that's the easy part. Now I need to find an SQL query that will handle renamed the PDF references in the database. As far as I know, the query will need to make updates to cmsPropertyData, cmsContent & cmsContentXml.
Hi Robert,
that's probably true. Maybe you could do easier by using the MediaService that takes care of all these updates.
You should loop through your mediaitems and then
Hope it helps, Jeffrey
Anyone have a thought on how this can be done via SQL query?
is working on a reply...