Home > SSIS > SQL Server Database Backup with SSIS

SQL Server Database Backup with SSIS


Dear SSIS/DBA Folks,

Backup is the most import activity of DBA in everyday task list. Often, DBA has to create T-SQL query to take a backup of a database or using manual method using SSMS.
Today, I am going to show how DBA’s can use SSIS to perform Backup activity in very simple way using SSIS.
Back up database task in SSIS is one of the maintenance plan task (MPT) in the tool box. The lists of MPT are as follows.
How configure back up database task?
The basic activity of this task is to take backup of the source database (s) to different storage (tape/backup file/overwrite the existing database backup).
Simple Demo:
Scenario:
I need take a database backup to keep each backup file in different folders with backup compress.
Implementation steps:
Step 1: Create a SSIS project and Drag and drop a Back UP database task from the maintenance pan task in the tool box.
Step 2:  Double click on the task, create a connection and provide all credentials to access database for backup
Step 3: select the type of the database backup and select the database(s) for backup.
Step 4: Now, configuring the backup folders by select the option shown in the below picture. You can choose the destination folder path during this setup.
Step 5: Setup database compress option as shown in the below picture
Final step, click ok and execute the package.
After the execution of this package twice, I got two different backup files in the folder named Test (name of the database).
Additional tips:
Automate this package with SQL Agent and monitor the activity instead doing it. 🙂
Question for the readers:
There is situation wherein you have to back up a database and keep it in two different locations. How do you do with SSIS?
Thanks for reading this article; your comments are more valuable.
Advertisements
  1. May 13, 2011 at 4:24 pm

    >A friend of mine commented on this post in a networking site. He said "Jack Whittaker:- Don't forget to tick the Check Integrity box. It runs "Restore with verify only" commands for every one of your backups."Check the integrity is to verify the backup is fully readable.if you want to learn more about this go to this web page http://msdn.microsoft.com/en-us/library/ms188902.asp

  2. August 31, 2011 at 8:43 am

    Thanks for the detailed post.
    Do you know how to set dynamic name for the each backup? I could sent dynamic folder path using expression but dont know how to set file name.

    Awaiting for your reply.
    Thanks.

    • September 1, 2011 at 8:48 am

      By Default backup file names are unique because it contains name, date and time with it. so, it will create backup file with different names.

  3. Ram
    February 16, 2012 at 11:28 pm

    SO is there any way to Dynamically change the backup file names? I tried using FTP task that can rename the files..but the file names are changing randomly so can’t use that…any idea about this subject would be helpful!! and also Can u throw some light how to restore the backup files using SSIS package.
    thanks

    • March 4, 2012 at 8:51 pm

      Good question.

      We can change file name using task propery with expressions.

      For database restoration. You can use script task and execute SQL statement programatically.

  4. shjnmnck
    September 28, 2012 at 1:19 am

    Is that backup compression option available in SSIS 2005 ?

  5. Krishna
    October 15, 2012 at 11:41 am

    Hi Ayyappan, thanks for posting this… Can we use SSIS package to restore databases as well?

    • October 15, 2012 at 12:39 pm

      We can use Execute SQL Task or Execute T-SQL task to restore database using TSQL statement.

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

      • Krishna
        October 15, 2012 at 1:03 pm

        I’m looking for a process of taking backup in one server and restoring in different server for multiple databases using SSIS package..I would be grateful if you can provide any procedure and example on this ..

        Thanks

  6. dev
    September 17, 2014 at 1:26 pm

    how do i change backup file name? i want just name not number.

    • October 15, 2014 at 11:54 am

      Hello Dev

      You can use
      DestinationCreationType and DestinationManualList properties in the expression to assign dynamic file name for the database backup file.

      Thanks
      Ayyappan

  7. bharathi
    November 4, 2014 at 12:31 am

    How to take backup in two different locations?One in local and other in server

    • November 5, 2014 at 12:23 pm

      You can use two backup task and point each one to different location.

  8. Ami
    February 27, 2015 at 9:47 pm

    Can I use this method to backup SQL Express on a separate server without creating all the proxy accounts?

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: