Daniel Calbimonte

Creating Backups with SSIS

November 21, 2014 by

Introduction

This Article is for people with experience using T-SQL and SQL Server Management Studio, but without experience in SQL Server Integration Services (SSIS).

In this article, we will introduce you to the SSIS world and then we will show how to combine different SSIS task to create combined backup tasks.

SSIS is a visual tool to Integrate SQL Server with other programs and databases like Oracle, MySQL, Teradata, Excel, MS Access, Visual Basic, C#, Web Services, FTP Sites, etc.

You can combine different tasks to work on complex tasks.

SSIS - Control Flow
Figure 1a

SSIS is a very powerful tool because you can:

  • Work with parameters and variables.
  • Work with loops.
  • Execute tasks in parallel incrementing the speed to complete the tasks.

SSIS is commonly used to Migrate Data, but it rarely used to backup databases. This is a tutorial to start using SSIS for these administrative tasks.

Getting started

  1. In order to start, open the SQL Server Data Tools (SSDT)

    SQL Server Data Tools (SSDT) in the Start menu
    Figure 1

  2. Go to File>New Project

    Navigating to File, New project
    Figure 2

  3. Select the Integration Services Project

    Selecting the Integration Services Project
    Figure 3

  4. Double click on the Check Database Integrity task. This task verifies the integrity of the logical and physical Database objects. You can run this task before the backup.

    The Check Database Integrity task
    Figure 4

  5. Double click on the task and press the New button to create a New connection

    Check Database Integrity Task dialog
    Figure 5

  6. Specify any name for the connection, the name of the SQL Server and select an authentication method (Windows Authentication is strongly recommended for security reasons) and press OK.

    Connection properties dialog
    Figure 6

  7. Click in the Databases Combobox and select the Database(s) to backup. As you can see, you can check the integrity of several databases with one single task and backup system databases.

    Selecting databases to backup
    Figure 7

  8. If you press the View T-SQL, you will have the chance to see the T-SQL command equivalent to the Task action

    View T-SQL button
    Figure 8

  9. The tasks executes the DBCC CHECKDB statement

    Task is executing the DBCC CHECKDB statement
    Figure 9

  10. Double click the Execute T-SQL Statement Task and add the tasks with a constraint

    Double clicking the Execute T-SQL Statement Task
    Figure 10

  11. Double click the Execute T-SQL Statement Task and add the following T-SQL Statement We are just making sure that the Database Logs are in full mode to recover databases at any point in time. Ideally, it should be set before.

    The main purpose of this is to show that we can execute T-SQL tasks with SSIS and combine with other tasks

    Execute T-SQL Statement Task dialog
    Figure 11

  12. Double click the Backup Database Task and join it with the other tasks using a constraint

    Double clicking the Backup Database Task
    Figure 12

  13. Double click the Back Up Database Task use the current connection, select the Full Backup type and a Database to backup

    Selecting connection, backup type and database to backup
    Figure 13

  14. You can backup individual files and files groups, you can also use the copy only option which allows you to backup the database without changing the Database and without affecting regular backup procedures. We can also specify expiration dates for the backups and Append or Overwrite the backup if it already exists.

    Back Up Database Task - Additional options
    Figure 14

  15. You can also verify the backup integrity and compress the backup

    Options for verifying the backup integrity and compressing the backup
    Figure 15

  16. Finally, double click on the Maintenance Cleanup Task and you can clean all databases

    Double clicking on the Maintenance Cleanup Task
    Figure 16

  17. With the Maintenance Cleanup Task, we can delete older backups. It is a good practice if there are obsolete backup that need to be removed. You can remove Backup files and Maintenance Plan text reports. You can also specify the file name or search folders with a specific extension in a specific folder. You can also search backup files in subfolders and specify which folders to delete according to their age.

    Maintenance Cleanup Task dialog
    Figure 17

  18. You can optionally press the view T-SQL button and see the command line used

    See the command line used by pressing the View T-SQL button
    Figure 18

  19. Once done, Start the package and it will run all the tasks created. If everything is OK all the tasks will be on green status.

    Starting the package If ok - all tasks will have green status
    Figure 19

  20. You can verify if the backup was created to verify that everything worked fine

    Verifying if the backup was created
    Figure 20

Other Tasks

There are many tasks that you can use to improve your SSIS projects. For example, you can use the Web Service Task to call Web Services in SSIS projects. Another powerful task is the Script Task. You can program in Visual Basic or C# complex or customized tasks not allowed by other Tasks. The Data Flow is the most popular and frequently used task to import or Export data to different data sources like Oracle, MySQL, DB2, MS Excel, Posgresql and most of the databases with the help of the Database Drivers. There are also tasks to send emails, upload files to an FTP Server, automate tasks in SQL Server Analysis Services, copy files, run the command shell (CMD), Work with XML files, etc.

Free extra tasks

There are also additional free SSIS components that you can download from the codeplex site.

There are task to upload files to the Amazon S3, Connect to Microsoft CRM Dynamics, FTPS, SCP, SSH, SharePoint and more.

How to schedule SSIS packages

You can schedule your SSIS packages using the SQL Server Agent. That way you can run the packages regularly according to your needs.

For more information about scheduling SSIS packages, review this link:

http://msdn.microsoft.com/en-us/library/ms141701.aspx#packages

SSIS for developers

If you do not like visual tasks and you love .NET, you can also program SSIS using C#, visual basic or other languages supported by the .NET Framework.

For more information about the Integration Services Development, please refer to this link:

http://msdn.microsoft.com/en-us/library/ms136025.aspx

Create your own custom SSIS task

Sometimes you need to create your custom task and add it to the SSDT to use it frequently. It is possible to create your own task and add it to the ones included by default.

For more information about creating your own custom SSIS task, please refer to this link:

http://msdn.microsoft.com/en-us/library/ms135965.aspx

Conclusions

As you can see, SSIS is a very complete and powerful tool to help you to Administer and Maintain your Database.


Daniel Calbimonte

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.

He also helps with translating SQLShack articles to Spanish

View all posts by Daniel Calbimonte
Daniel Calbimonte

Latest posts by Daniel Calbimonte (see all)

17,121 Views