T-SQL Tuesday #51 – Don’t bet against CHECKSUM when performing backups

This month’s T-SQL Tuesday is being hosted by Jason Brimhall (blog | Twitter).  Jason’s theme is about gambling with SQL Server.  What areas have you gambled with?  Are there tasks that you should be doing that you are betting won’t be needed?  One came to mind for me, CHECKSUM.  I’ve always been a fan of Maintenance Plans.  They are an easy way to get basic maintenance tasks established on an instance of SQL Server.  The major down side of the built-in maintenance plans is no support for the CHECKSUM media option for BACKUP DATABASE.  If you are performing backups without this option enabled, you run the risk of the pages in the backup media not matching the pages stored in the database.  This is because without the CHECKSUM option, SQL Server doesn’t know if the page written to the backup media was written out correctly.  The CHECKSUM option ensures that every page written out to your backup media has a checksum that matches the checksum of the page in the database.  The CHECKSUM option also goes as far as doing a checksum on the entire backup that the RESTORE statement can take advantage of.  There is a performance impact for using the CHECKSUM option but as everyone says, your backup is only as good as your restore.  If you bet against your restore, your odds of a resume updating event just increased.

Now that you understand the benefits of CHECKSUM for your backups, how do you integrate it into your backup process?  It’s time to graduate from the built-in Maintenance Plans and move to Ola Hallengren’s SQL Server Maintenance Solution.  The is by far the best maintenance scripts available within the SQL Server community.  Do yourself a favor and pull the scripts down to a test instance and give it a try.  I made the move a few months ago and have never looked back.

Don’t bet against CHECKSUM when performing your backups!