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.

Clipboard001.jpg

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.

Clipboard02.jpg

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)

Clipboard03.jpg

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

Clipboard04.jpg

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

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

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

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.
Clipboard09.jpg

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.
Clipboard10.jpg
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”.
Clipboard11.jpg

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

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

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

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.
Clipboard006.jpg

Name it as you need.
Clipboard16.jpg

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.
Clipboard18.jpg

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)
Clipboard19.jpg

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 *