Back Up Database Task

By using the Backup Database task, a package can back up a single database or multiple databases. If the task backs up only a single database, you can choose the backup component: the database, or its files and file groups.

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.

1. Create a new SSIS project and solution.
• Open SQL Server Business Intelligence Studio (BIDS).
• Select "New Project"
• Select 'Integration Services' template.
• Name of "SSIS" (this will be the name of the overall solution).
• Location of existing folder "C:\SSIS".
• Check the 'Create Directory for Solution' box.
• Click 'Ok' and the Solution will be created.
• In the Solution Explorer window Right click the default package name of 'Package.dtsx' and select rename.
• Use name "SampleSSISl.dtsx" and press. Select yes to renaming the pack object as well.


2. From the Toolbox, Double-click or drag the 'Back Up Database Task' to the Control Flow tab.
3. Double click on the task, create a connection and provide all credentials to access database for backup


4. Select the type of the database backup


Full backup
A full backup contains all the data in a specific database or set of file groups or files, and also enough log to allow for recovering that data. It is the base of both differential backup and transaction log backup.

Differential backup
A differential backup is not independent and it must be based on the latest full backup of the data. That means there should have a full back up as a base. A differential backup contains only the data that has changed since the differential base. Typically, differential backups are smaller and faster to create than the base of a full backup and also require less disk space to store backup images.

Transaction Log Backups
The transaction log is a serial record of all the transactions that have been performed against the database since the transaction log was last backed up

5. Select the databases that you want to back up (typically all user databases).


6. Select your backup media (typically Disk).
7. Specify a location (either Default or as assigned by you) for your backup files.
8. Back up databases across one or more files
• Click Add to open the Select Backup Destination dialog box, and provide one or more a disk location, or tape device.
• If backup files exist
• Select Append to add this backup to the end of the file. Select Overwrite, to remove any old backup in the file and replace them with this new backup.
9. Create a backup file for every database
• Create a backup file in the location specified in the folder box. One file will be created for each database selected.

10. Select the Verify backup integrity check box.

11. Now Hit F5 will run the package to make a Full database backup. Once everything is ready and executed your screen will look like below.


12. After the execution of this package twice, we got the full backup file in the folder named Test (name of the database).



No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: