In this tutorial we will learn how to create a simple SQL Server backup solution with The Dummy Programmer Robot (or, in short, TDP Robot).

We have one goal: perform a daily backup of the databases contained in a SQL Server instance.

As always, to create the job we need to open the TDP Robot Job Editor. Remember that you can learn the basics of job editor consulting the following page.

Let’s start!

Configuring the container folder

Before starting to drag the event and tasks into the workspace area, let’s create a new folder in which to place the new objects. Here is the workspace area after the creation of the new folder:

The Job Editor workspace area with the new "SQL Server backup" folder.

For this new job we will add the following objects from the toolbox:

The Date / Time event object in the toolbox
The SQL Server backup task in the toolbox

Next connect the two objects and rename them:

The workspace area with the two new objects

Configuring the Date / Time event

Double click on the event “Every day at 21:00” to show the event configuration window, which I configured as follow:

Date / time event configuration window

Note that the date / time event will always start at the date / time specified in the “At” field:

The "At" field in the date / time event configuration window

but if you select “One time” the event will be triggered just one time at the date / time specified. Otherwise if you select one of the options below, it will be triggered starting from the date time specified with the periodicity configured. In our example, we want to trigger the event at 21:00 every day, so the configuration above is ok for our needs.

Configuring the SQL Server backup task

If you double click the object “Backup databases” you will see the following empty configuration window:

SQL Server backup task empty configuration window

In the “Connection” frame, insert the parameters needed to connect to your SQL Server instance and press the button “Test / Refresh” to test the connection toward the database server and refresh the database list.

This is my configuration window after entering the parameters for the connection:

SQL Server backup task empty configuration window after entered the parameters for the SQL Server connection

As you can see, the list at the bottom of the window as been loaded with the databases contained in your SQL Server instance.

In the “Backup type” field you can choose one of the following options:

  • Full backup: to make a full backup of the database data file
  • Transaction log: to make a backup of database transaction log

We will choose the option “Full backup”.

Note also the options below the field “Backup type”. You have three options:

  • All databases: select this option to back up all databases in the instance (system databases like “master” and all the users databases)
  • All user databases: select this option to backup only user databases (therefore all the databases except the system ones)
  • The following databases: select this option to specifically indicate the databases to back up; in this case you have to select each database in the list using the checkbox near the database name

To make things simple, we will choose the first one: “All databases”.

Now it’s time to go to the tab “Options”, which I configured as follow:

SQL Server backup configuration window - the tab "Options"

This is the simplest possible configuration, the one containing only the destination folder. We will use this configuration for our example, but remember that you can you use dynamic data to make the “Destination folder” and “File name template” fields dynamic.

Now we are ready… always remember to save your work using the menu in the Job Editor main window:

The "Save" menu item

and to restart the Windows service “The Dummy Programmer Robot – Job Engine Service” in order for the configuration to be reloaded.

How it works

The date / time event object “Every day at 21” will trigger the task “Backup databases” every day at the time specified. The backups will be saved in the path “D:\DBBackup”. If a backup file already exists, will be overwritten.

Right now it’s not much more than what you could do with a simple SQL Server maintenance plan, but in the next tutorial we will see how to modify this job to add some new useful features.