In a previous blog post, I talked about the importance of using the CHECKSUM keyword in the BACKUP DATABASE command to make sure that you are not caught unaware of page corruption in your backups. I’ve also provided a very simple example of showing how this one keyword can influence whether or not you’ll have a good working backup. To make it even more simple, I’ve recorded a demo video showing the difference between using it and not using it.
As I’ve mentioned in both the previous blog post and the video, the CHECKSUM keyword is very important especially if you are using an enterprise-wide backup solution and you have no control over the parameters passed by the backup solution. You can set this for the entire instance so that everyone taking database backups will have the CHECKSUM keyword included.
I’ve also included a reference article from SQL Server MVP/Regional Director and SQLSkills CEO Paul Randal (blog | Twitter) about using the CHECKSUM option for databases that have been upgraded from previous versions of SQL Server.
One more thing, you might want to check if you are using the backup compression feature. Just saying 🙂