One of the recommendations for SQLServer is that you run a DBCC CHECKDB regularly on your system. Those individuals that have worked with SQL Server for some time and worked on learning more about the system know to schedule this check, and even ensure that you restore backups to check them on a regular basis. Note, running these checks on secondary systems may or may not be valid. You might want to read Paul Randal's post on this.

Experienced DBAs know that when corruption strikes, you can find yourself in a very problematic situation. Corruption can be captured in backups, which means that if it appears, it's entirely possible that if this has been in your system for some time, all the backup files you have contain the same corruption. If this is inside a table, you might end up losing data, which is never what any of us want.

Those that might not be familiar with SQL Server, or have never learned about regular maintenance might not realize that DBCC is needed. In fact, they might not know if CHECKDB has ever been run on their system. It used to be hard to find this, but things became easier over the years. When checkdb runs, it does write a note in the error log, but that's not a great way to track this information for administrators, especially if the instance has been restarted. Without a set of tools in place, this becomes a project for anyone that starts working with a new system to track down.

That changes in SQL Server 2016 SP2. There's a new property for your database, a parameter for DatabasePropertyEx(). The value is LastGoodCheckDbTime, which in this case, you can get the last datetime when a checkdb was run. To me, this should be some sort of alert that your monitoring system has in place that lets you know if this value is too old.

The problem is that for many of us, we may run DBCC CHECKDB on another machine, perhaps on a restored copy of production, so how can we track this? Is there a way that would make sense? I'd like to think that perhaps any script testing a backup should connect back to the primary database and update this value. There isn't a specific place for this, but I certainly could see using extended properties for this. At least then we could more easily determine if we haven't been regularly checking a particular database for corruption. If you have other ideas, I'd be interested in how one might actually track this.

For now, use this property to ensure you're checking those databases where you do execute CHECKDB, and if you aren't sure if you should do this, you should. Go learn about what CHECKDB does and why it's important for your production systems.

Join the debate, and respond to today's editorial on the forums

While you can set the database to offline and copy thefiles, this is not your only option. You can use the CONTINUE_AFTER_ERROR option.

Ref: BACKUP - click here

» Discuss this question and answer on the forums