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

Automating repetitive tasksThis 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!

Doug Purnell

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

  1. Pingback: T-SQL Tuesday #051: Bets and Results | SQL RNNR

  2. Pingback: T-SQL Tuesday #051: Bets and Results - SQL Server - SQL Server - Toad World

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s