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.
SQL Server Rider 
>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
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.
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.
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
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.
Is that backup compression option available in SSIS 2005 ?
It was not there in SQL 2005.
Hi Ayyappan, thanks for posting this… Can we use SSIS package to restore databases as well?
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
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
how do i change backup file name? i want just name not number.
Hello Dev
You can use
DestinationCreationType and DestinationManualList properties in the expression to assign dynamic file name for the database backup file.
Thanks
Ayyappan
How to take backup in two different locations?One in local and other in server
You can use two backup task and point each one to different location.
Can I use this method to backup SQL Express on a separate server without creating all the proxy accounts?
I think, we can do.
can anyone explain about backup types in backup database types??