SSIS package for automated database backups

Common task for database administrator is to maintain backups of databases.
I created SSIS package which can be used as a basement for daily automated backups. This package can be easily modified to any database and added as step in agent job.

This package was created on MS SQL server 2005 SP3 developer (Visual studio is included in installation)
Please note that advanced backup management is available via 3rd party products (native compression introduced in SQL Server 2008 Enterprise). If you have large production databases I would recommend using tape library or other backup scenarios.


As you can see the package creates local database dump instead of network location. Microsoft does not recommend network backups and I don’t have personally good experience with that. Every small network hick-up can stop backup procedure and you can spend more time troubleshooting theses issues than is necessary. Instead of backing up to network this package makes local backup and copies it onto NAS (share).

Example for master database. Pleas avoid temptation to use all system or all user databases.

Deleting local backup location
To delete old backups we will use backup filename mask using “Foreach loop container”.
Drag “Foreach loop container” onto you control flow view and click edit.


Onto collection tab make sure you have “Foreach File Enumerator”. In “Folder” select location of your automated backups and in “Files” proper filename mask. Make sure you have “Fully qualified” option selected (to give full name including path for delete task)


On “Variable mappings” tab create new user variable “FileToDelete”. Save changes.


Drag onto Control Flow “File System task” and make sure its inside the “Foreach loop container” (variable stays only inside it)

On General tab select “delete file” operation. Select “IsSourcePathVariable = True” and “SourceVariable User::FileToDelete”. Save changes

Open Variables toolbox (right click onto control flow and select “variables”) and put into Value same file mask you used in previous steps.

Database backup
Drag onto control flow “Back Up Database Task” and configure it according to your needs. Make sure you have same path, filemask and extension we used before. We use Full backup here.

Deleting remote location
You can skip this step if you want to keep all the backups.

Drop another “Foreach Loop Container” onto Control Flow. And configure it with filename mask we are using and network share you want. On “Variable Mappings” don’t forget to map “User::FileToDelete” variable.
Drop File System Task into container. The settings are the same like for local delete operation.

Moving Backup
We will use again new Froeach Loop container but with new variable. As you know backup files are using date format by default (master_backup_201002092000.bak). One variable includes actual filename and the second filename from last date. If you use just one variable you will receive “file not found” errors. Just create and map new variable on “Variable Mappings” tab and name it “FileName”.

Drop into container new File system task. Set DestinationConnection to your shared folder

“Operation = Move” File and “SourceVariable = User::FileName”. Save changes and close.

Open variables  toolbox ( right click onto control flow and select “variables” ) and modify value of “FileName” to your filename mask.

Save package
Connect each task with arrows in correct order. Now if you have access to SSIS package storage you can save a copy of the package.

Name it as you need.

As you can see you created versatile backup package for any database you want. All you need is to change file masks, variables, back up database according to you needs. Then save a copy under another name.

Making a Agent Job
Open Sql Management studio, connect to your database and create new agent job. On Steps tab create new step and name it as you wish. Next select “SQL Server Integration Service Package” type, SSIS Package store as Package source, your server and browse for your package.

On Advanced tab set “on success and failure action” as you need. Its useful to tick check-box Include step output in history ( you are creating more steps – every step for every backup package)

Repeat this for all your packages ( backup tasks ), create schedules, notifications or whatever you want.

Cheers and say hello to your db users 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *