.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Backup Database task in SSIS and Send Mail - SQL Server Integration Services (SSIS) - Part 13

Posted By:Karthikeyan Anbarasan       Posted Date: February 27, 2011    Points: 100    Category: DataBase    URL: http://www.dotnetspark.com  

In this article we are going to see on how to use a Backup Database task to create an execution plan in SSIS to take a database backup in SQL Server Integration Services (SSIS)
 

Introduction

In this article we are going to see on 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 a Full, Differential backups based on our requirement we can go ahead and use the respective option. Let's jump into the steps on how to create and use the backup database task 


To follow my series of articles on SSIS packages refer to the below URL's

Basics of SSIS and Creating Package - SQL Server Integration Services (SSIS) - Part 1

Transforming SQL Data to Excel Sheet - SQL Server Integration Services (SSIS) - Part 2

Export Data using Wizard - SQL Server Integration Services (SSIS) - Part 3

Import Data using Wizard - SQL Server Integration Services (SSIS) - Part 4

Building and Executing a Package - SQL Server Integration Services (SSIS) - Part 5

Options to execute a package in SSIS - SQL Server Integration Services (SSIS) - Part 6 

Options to Deploy a package in SSIS - SQL Server Integration Services (SSIS) - Part 7

Scripting in SSIS Packages - SQL Server Integration Services (SSIS) - Part 8

Breakpoints in SSIS Packages - SQL Server Integration Services (SSIS) - Part 9

Check Points in SSIS Packages - SQL Server Integration Services (SSIS) - Part 10

Send Mail in SSIS Packages - SQL Server Integration Services (SSIS) - Part 11

For Loop task in SSIS Packages - SQL Server Integration Services (SSIS) - Part 12

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 on how to use Backup Database task options available with SSIS.

Once we are into the project drag and drop the Backup Database Task from the tool box as shown in the below figure



Now drag and drop the Send Mail task, so here our task is to do a backup and send a mail to the DBA that the backup has be done successfully. Once you drag and drop the Send mail task then you 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 below screen. We need to do the configuration as shown below. It's self-explanatory on the options available



Clicking on the View T-SQL button will open a popup as shown in the below screen



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.



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 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.

 Subscribe to Articles

     

Further Readings:

Responses
Author: Amit Mehra         Company URL: http://www.dotnetspark.com
Posted Date: March 03, 2011

Great series Karthik..Keep writing...

Regards
Amit

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend