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.
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.
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”.
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.
Repeat this for all your packages ( backup tasks ), create schedules, notifications or whatever you want.
Cheers and say hello to your db users 🙂