I have implemented a new backup strategy on a server using the SQL Server Maintenance Solution by Ola Hallengren. It is very flexible and very robust. There are lots of advantages to using it instead of the built-in maintenance plans.
Some of the reasons are:
- Easy Deployment to multiple servers using sql scripts.
- Have control of the name of the sql jobs.
- Wild card inclusion/exclusion of databases
- Handles a differential or log back up if full has not been executed as in when a database is created.
- Totally customizable
- Very robust options
- Smart index maintenance by only re-indexing or organizing indexes that need it.
- Better clean up of old files
- All details of every command executed is stored in a SQL table and text file system. Job history also has job level details.
- Easy to diagnose issues due to detailed logging of commands.
- If one thing in job fails, the other commands in the job are still executed. This means if one database can’t be backed up the rest still will be.
- Used by tons of corporations around the world.
- Works with multiple version of SQL Server
- Has received Gold Community Choice award and Silver Editor’s Best Choice award (both in 2011).
- Handle Large databases well.
The one that I like most is ease of deployment. Since it is based on stored procs and sql jobs all that needs to be done is run a sql file. The default configuration is nearly perfect. The jobs just need to be scheduled for the most part.
There is a good video (about an hour) that reviews it in detail.
It is an open source project that has a open ended license. The source and documentation can be found at: http://ola.hallengren.com