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:
For this new job we will add the following objects from the toolbox:
Next connect the two objects and rename them:
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:
Note that the date / time event will always start at the date / time specified in the “At” field:
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:
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:
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:
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:
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.