Today, I was researching information on DBCC and database backups. I was trying to ensure that I have good data in my backup, as you know… garbage in leads to garbage out. Basically, your data is only good as the data you pull from
Talking with Robert Davis (Blog | Twitter) he provided me with some good advice: “You should be running DBCC CheckDB regularly. Daily if your system can handle it, but at least weekly. You don’t need to check for the last good DBCC CheckDB if you are running regular CheckDB. For backups, you should be using the CHECKSUM option of the backup command.” So with that being said, I went and tested some of my scripts and then modified them to ensure they are doing a CHECKSUM. Then my next goal was to verify that my databases had recent DBCC checks. (DBCC CheckDB)
One of the articles I came across was a great one from MSDN SQL Server Premier Field Engineer Blog. Not only did it let me know the last known good DBCC check, but it also provided a nice chart listed below to allow you to compare that information against when you had a backup.
That made it all too easy for me to see if I had a good backup that actually had valid data in it! This script made me feel better about my backups not having any garbage data. I hope this information helps you as well.
I also wanted to add that you should add this TSQL code (CHECKSUM) in your backup as well to verify your data.