A CompleteFTP user recently asked us about an easy way to change lots of folder paths. They are moving all their folders in CompleteFTP to a different location. They have far too many folders to update the paths one by one in the manager application.

Fortunately, there is an easier way.

All of CompleteFTP’s configuration data is stored in a SQL database called config.sdf. It can be found in the C:\ProgramData\Enterprise Distributed Technologies\Complete FTP folder. You can extract the data as SQL statements, modify them appropriately, and then update the database with the modified SQL.

To extract the data, you need the right tool. As we use Microsoft’s SQL Compact database, you need to use a tool that works with this – Compact View (CV). Once this is installed, make a copy of config.sdf, and open it with CV. Select the Node table, and from the menu select “Export …”. The various fields will be displayed – you only need the primary key, NodeID, Name and Configuration. Select “Only data” and then choose Export. You’ll end up with a file containing a series of SQL INSERT statements. Each line represents one folder.

Delete all the lines that you aren’t interested in changing, and modify the Configuration field appropriately of those you want to change. Now you need to convert he SQL INSERT statements into SQL UPDATE statements, so you end up updating the Configuration field of the Nodes you changed.  You want to end up with a series of statements like this:

UPDATE [Node] SET Configuration = 'New folder path' WHERE NodeID = '68b3264c-3357-49bb-9da3-1427fbd30ea5';

Each original SQL INSERT statement that you have modified must be converted into an UPDATE. The NodeID for each line must come from that line’s NodeID – they can’t be mixed up.

Once you’ve finished updating your file with SQL UPDATE statements (remember, only for the ones you want to change – the remainder should be deleted from your file), you are ready to import the file. In CV, use the Import menu item to do so. You should have no errors, and now all your folders should be appropriately updated.

The final step is to replace the config.sdf file in C:\ProgramData\Enterprise Distributed Technologies\Complete FTP with your modified config.sdf file. Then restart the server, and start the manager application to verify that everything looks as it should. You can always roll back to your original config.sdf by replacing it and restarting the server.