I needed to find a way to change the storage location for a large sub tree to "secure - file system". Here is a script that I ended up with.

Note that this will only work if you are running this from SQL Managment Studio and only then if the account that sql server service  is running under has write permission to the portals folder. If one would run this from host > sql one would have to add the database account that the website is runnig from is in the sysadmin role in sql server.

The reason for that is that the script makes use of xp_cmdshell that in the process will rename all files in the given tree to end on '.resources'

At the very beginning of the script you will have to set a few variables to match your environment.

-- this is the absolute path to your portal directory
DECLARE @RootPath nvarchar(255) = 'C:\inetpub\wwwRoot\Portals\0\'

-- the script will perform a like search on that expression. so if you put 'images' in here, the script will migrate all folders that have 'images' in ther folder path!
DECLARE @Identifier nvarchar(255) = 'put_the_root_foldername_here'

-- lookup your mapping id to use the correct folder provider in the foldermappings table
DECLARE @FolderMappingId int = 8
DECLARE @StorageLoactionId int = 1

DECLARE @FolderId int
DECLARE @FolderName nvarchar(255)

DECLARE @FileName nvarchar(255)
DECLARE @FilePath nvarchar(255)

DECLARE folder_cursor CURSOR FOR
    select FolderID, FolderPath from Folders where FolderPath Like '%' + @Identifier + '%'
    
OPEN folder_cursor;

-- Perform the first fetch.
FETCH NEXT FROM folder_cursor into @FolderId, @FolderName
WHILE @@FETCH_STATUS = 0
BEGIN
   
   PRINT 'Now securing ' + @FolderName
    
    DECLARE files_cursor CURSOR FOR Select [FileName], [Folder] From Files Where FolderId = @FolderId
    OPEN files_cursor
        FETCH NEXT FROM files_cursor into @FileName, @FilePath
        WHILE @@FETCH_STATUS = 0
            BEGIN
            Print '...' + @FilePath + @FileName
        
            DECLARE @OldPath nvarchar(255) = Replace(@RootPath + @FilePath + @FileName, '/', '\')
            DECLARE @NewFile nvarchar(255) = @FileName + '.resources'
            DECLARE @command nvarchar(500) = 'rename "' + @OldPath + '" "' + @NewFile + '"'
            DECLARE @result nvarchar(1000)
            EXEC @result = master.dbo.xp_cmdshell @command , NO_OUTPUT
        
            Print '...command execution returned ' + @result
        
            FETCH NEXT FROM files_cursor into @FileName, @FilePath
            END
        CLOSE files_cursor;
        DEALLOCATE files_cursor;

   UPDATE Folders set StorageLocation = @StorageLoactionId, FolderMappingID = @FolderMappingId where FolderID = @FolderId   
     
   PRINT @FolderName + ' is now secure'
   FETCH NEXT FROM folder_cursor into @FolderId, @FolderName
END

CLOSE folder_cursor;
DEALLOCATE folder_cursor;