In this previous tutorial we have seen how to create a simple SQL Server backup solution using TDP Robot. In this new tutorial we will go a step further: we will see how to zip and then transfer backup files to a remote FTP server.

Adding an FTP task to the job

Let’s start by opening the TDP Robot’s job editor and, specifically, the folder we created in the previous tutorial which we named “SQL Server backup”. This is the current situation:

The backup job as we created in the previous tutorial

As already said, we want to zip and transfer the backups created by the “Backup databases” task to a remote server.

To do this, we need to add the following tasks to the job:

Drag the tasks above in workspace area and connect them in the following way:

The backup job modified with two new tasks: a zip task and a FTP/SFTP task

Configure the zip task

Let’s start by configuring the zip task. Double click on the task “Zip backups” to show the task’s configuration window.

The following window will appear:

The empty zip task configuration window

Fill the “Source” field with the path where the task “Backup databases” stores the backup files. In my case the path is “D:\DBBackup”. Next, choose the path and file name where you want to save the zip and fill the field “Destination archive”. You can leave the other options as they are.

The configuration form will look like this:

The zip task configuration window completed

Configure the FTP / SFTP task

It’s time to configure the last task of our job: the “Transfer backups” task. Double click the task to show the configuration window:

The empty FTP/SFTP task configuration window

Here we have to specify the necessary info to make an FTP connection, filling the fields “Host”, “Username” and “Password”. Note that, as an alternative, you could use and SFTP connection changing the field “Protocol”.

In the section “Copy / Move” we can add the folder / files we want to transfer.

In the end, the configuration window will look like this:

The FTP/SFTP task configuration window completed

And this is the “Add / Edit copy item” window:

The "Add / Edit copy item" window configured

Note the use of a dynamic data in the field “Remote path”, whose content is as follows:

/my_ftp_path/Backup_{Object[15].ExecutionEndDateUnderscore}.zip

Using the dynamic data {Object[15].ExecutionEndDateUnderscore} allow us to create a unique file name each time we transfer a backup, keeping an history of the backups on the FTP server.

Conclusion

In this tutorial we have gone a step further than the previous one, adding the copy of the backups on a remote server. I hope you will find TDP Robot (and these tutorials) useful. Stay tuned because in short we will see other possibilities of TDP Robot! 🙂