Implementing a backup strategy on MS SQL Server

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