Introduction
In this article we are going to see how to use a Backup Database task to create an execution plan in SSIS to take a database backup. This task will be very helpful for the DBA's to maintain the database automatically by creating the package and using it across the servers. This task has many options to do Full or Differential backups based on requirements; we can use the respective option. Let's jump into the steps of how to create and use the backup database task.
Steps
Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on integration services project. Once the project is created, we will see how to use Backup Database task options available with SSIS.
Once we are in the project drag and drop the Backup Database Task from the tool box as shown in the figure below.
![]()
Now drag and drop the Send Mail task, so our task is to do a backup and send a mail to the DBA that the backup has been done successfully. Once you drag and drop the Send mail task then your project will look like below:
![]()
Now let's step into the configuration section of the Backup Database task and do the configuration for a Full backup database. Just double click on the backup database task; it will open a window as shown in the screen below. We need to do the configuration as shown below. It's self-explanatory from the options available.
![]()
Clicking on the View T-SQL button will open a popup as shown in the screen below.
![]()
Now configure the Send mail task (Check my previous link on how to configure Send Mail task in SSIS). Now press F5 to execute the package to make a Full database backup. Once everything is ready and executed your screen will look like below.
![]()
The Send Mail task shows red color which means there is an error in the task. It's a known error, since it's executed in my local system SMTP which is not configured.
Conclusion
So in this article we have seen how to back up a database using SSIS package and send a mail to the DBA on success.