Friday, July 13, 2012

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


No comments: